grouper-users - RE: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

Subject: Grouper Users - Open Discussion List

List archive

RE: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

From: "Black, Carey M." <>
To: "Crawford, Jeffrey" <>
Cc: " Mailing List" <>
Subject: RE: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4
Date: Fri, 23 Nov 2018 12:11:38 +0000
Accept-language: en-US
Authentication-results-original: spf=none (sender IP is ) ;
Ironport-phdr: 9a23:pHsKlhWdRYVlz2hcAuBcct4ZtBnV8LGtZVwlr6E/grcLSJyIuqrYZRSDu6dThVPEFb/W9+hDw7KP9fy4CSpYud6oizMrSNR0TRgLiMEbzUQLIfWuLgnFFsPsdDEwB89YVVVorDmROElRH9viNRWJ+iXhpTEdFQ/iOgVrO+/7BpDdj9it1+C15pbffxhEiCCybL9uLxi6txndutULioZ+N6g9zQfErGFVcOpM32NoIlyTnxf45siu+ZNo7jpdtfE8+cNeSKv2Z6s3Q6BWAzQgKGA1+dbktQLfQguV53sTSXsZnxxVCAXY9h76X5Pxsizntuph3SSRIMP7QawoVTmk8qxmUwHjhjsZODEl8WHXks1wg7xdoBK9vBx03orYbJiIOPZiYq/ReNUXTndDUMlMTSxMGoOyYZUSAeodM+hWrIf9qFkPrRSiCgahH/ngxiNUinLswaE2z+YsHAfb1wIgBdIOt3HUoc37OKoPVeC61rXHzTbbY/hLxzry8pLIfQ4hoPqRWrx8a8TdwlQoGgzeilWfs5DqPzCP2ugQrWSW4fFtVeW0i246sQ1+vCWgxto1h4TPm4kbxFfE9SBjz4Y0I921UE57YcK4EJtIrCGWLoV7Tdk6TG5woik6yacJtYS+fCgR0JsnyB7fZOKGc4WG4x/vTP6RIS1/hHJidrK/nxGy8Ummyu3gTMW7zFFKri9Dn9LRtX4NzwTe5tadRfdh40us3CuD2x3O5uxBL0A5mrbXJ4Ikz74+iJUevkfOEyrzlUj0kqOaaF0o9van5uj9fLnqupyRO5Vqhg3gPKkigNGzDOY7PwQWUWiU5+ex2KHm8ED8XrlGk+A5n6vcvZ3UO8gWprS2DgpV34sh6xuzEzKr3dsCknQDNl1Ifg+MgZLzNFHUOv/4CO+yg1SynzdvwPDLJqXvD4nKIHTfnrrtZKhw5VdBxAYqytBQ/IxbBqsGIPLuRk/+r9vYDgI/MwOp2ernEM991oQCWW2RHqCZLKLSsViO5u41JOmMeZMVuDL6K/gi5P7il2M2mVgYfaWx3JsXbm63Huh+LkmHe3XgnsoNHGIXsgYjUeDnjVOCXDFPa3quWq886C80BY2lAIrGQ42ggqKN0DmjEp1TfG9GC1SMEXnyd4WDXvcBcDmdIst7nTMYVritUZIt2Q+guQLhzLpnNffU9jcGup3+ydh5//fTmg0q9TxoE8Sd1HmAT25unmMPWj82xL5woVZkxleeyqh3neZYFcdI6vNNUwc6LoLcz/dkB9zoWwLBeMuJR0i8QtWgHz4xUsw9z8UQbElgBtXxxizEimCBGbYKmbuRCNh80K/S0WL8Now1n3TNzqA+gV48TuNOKSu7jap2sQXfGtiavV+ekvPgX6AV1y2JvEyK12ef9GQeGkYkW6HMVnNZPxGNhdPi+wXPQ6L4WudvCRdI1cPXcvgCUdbul1gTAa67YNk=
Spamdiagnosticmetadata: NSPM
Spamdiagnosticoutput: 1:99

Jeffrey,

Quote Shilen:

“

Or do a “gsh -registry -deep” to get a new sql file with the missing ddl.

“

That operation should be a “look but don’t touch” process that should validate the state of the DB and produce any ddl ( db structure changes ) sql that needs executed to “fix it”.

As the process output says…*read the output ddl before you just run it*. Make sure you know what the script will do. Then choose how to proceed.

Carey Matthew

From: <> On Behalf Of Crawford, Jeffrey
Sent: Wednesday, November 21, 2018 4:21 PM
To: Shilen Patel <>; Redman, Chad <>; Hyzer, Chris <>
Cc: Mailing List <>
Subject: Re: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

Humm, I’m wondering if the same problem might be present in our production side. I’m not usually in the database (Other than the audit table) Is there an easy way to check for the issue? That way I can run the same query in production.

Thanks

Jeffrey C.

From: Shilen Patel <>
Date: Wednesday, November 21, 2018 at 2:52 AM
To: "Crawford, Jeffrey" <>, "Redman, Chad" <>, "Hyzer, Chris" <>
Cc: "" <>
Subject: Re: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

Not necessarily. The upgrade sql script basically has the following steps:

Drop all views
Drop all foreign constraints
Upgrade the ddl
Set the new ddl version in the database
Add the foreign constraints
Add the views

Your original error (ORA-02298: cannot validate (IAMUCLA_GROUPER_D.FK_PIT_GS_OWNER_STEM_ID) - parent keys not found) probably happened during step 5 above.

You can now either run all the sql statements in the original sql script that didn’t run. Or do a “gsh -registry -deep” to get a new sql file with the missing ddl.

But before you do that, the original issue needs to be resolved. It seems like your point in time data is corrupt. If this is a dev environment and you don’t necessarily care about it, then you can truncate those tables. Otherwise, you’ll need to see why you have rows in grouper_pit_group_set with an owner_stem_id that’s not in grouper_pit_stems.

Alternatively, you could add the views back (from the sql script) which may be enough for you to start gsh at least. Then you can also run the pit sync script.

Thanks!

- Shilen

From: "Crawford, Jeffrey" <>
Date: Tuesday, November 20, 2018 at 5:44 PM
To: "Redman, Chad" <>, "Hyzer, Chris" <>, "" <>, Shilen Patel <>
Subject: Re: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

But wouldn't running gsh -registry -check correct that?

Get Outlook for Android

From: Shilen Patel <>
Sent: Tuesday, November 20, 2018 2:18:47 PM
To: Redman, Chad; Crawford, Jeffrey; Hyzer, Chris; Mailing List
Subject: Re: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

I'm guessing since the upgrade failed (the first error that was given), the views weren't added back (which usually happens at the end of the upgrade). So all these other errors may be a side effect.

- Shilen

Sent from my Verizon, Samsung Galaxy smartphone

-------- Original message --------

From: "Redman, Chad" <>

Date: 11/20/18 5:13 PM (GMT-05:00)

To: "Crawford, Jeffrey" <>, "Hyzer, Chris" <>, "" <>

Subject: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

Syntax was almost right. Try this without the "as".

select ms.*, m.* from grouper_memberships_all_v ms, grouper_members m

where ms.owner_attr_def_id = '80075426c19b41758c65e6a6e74c6a9a'

and ms.member_id = '496458b5c1ca4757ab70541f15d3ec34'

and ms.member_id = m.id and ms.immediate_mship_enabled = 'T';

From: [] On Behalf Of Redman, Chad
Sent: Tuesday, November 20, 2018 5:09 PM
To: Crawford, Jeffrey <>; Hyzer, Chris <>; Mailing List <>
Subject: [grouper-users] RE: Trouble upgrading Grouper DB from 2.1 to 2.4

If it helps, this is the query the error translates to. Does this work from SQL? Logged in as iamucla_grouper_d?

/* edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO#findAllByGroupOwnerAndMember */

select ms.*, m.* from grouper_memberships_all_v as ms, grouper_members as m

where ms.owner_attr_def_id = '80075426c19b41758c65e6a6e74c6a9a'

and ms.member_id = '496458b5c1ca4757ab70541f15d3ec34'

and ms.member_id = m.id and ms.immediate_mship_enabled = 'T';

From: [] On Behalf Of Crawford, Jeffrey
Sent: Tuesday, November 20, 2018 2:41 PM
To: Hyzer, Chris <>; Mailing List <>
Subject: [grouper-users] Re: Trouble upgrading Grouper DB from 2.1 to 2.4

Maybe it’s just easier to post the errors I got:

./bin/gsh.sh

Using GROUPER_HOME: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF

Using GROUPER_CONF: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/classes

Using JAVA: /usr/java/jdk1.8.0_191-amd64/bin/java

using MEMORY: 64m-750m

log4j:WARN No such property [maxBackupIndex] in org.apache.log4j.DailyRollingFileAppender.

Grouper starting up: version: 2.4.0, build date: 2018/08/23 07:48:38, env: <no label configured>

grouperPatchStatus read from: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/grouperPatchStatus.properties

api patches installed: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9

ui patches installed: 0, 1, 2, 3, 4

grouper.properties read from: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/classes/grouper.properties

Grouper current directory is: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF

log4j.properties read from: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/classes/log4j.properties

Grouper is logging to file: /opt/grouper-2.4.0/apache-tomcat-8.5.12/logs/grouperUi/grouper_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties

grouper.hibernate.properties: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/classes/grouper.hibernate.properties

grouper.hibernate.properties:

subject.properties read from: /opt/grouper-2.4.0/grouper.ui-2.4.0/dist/grouper/WEB-INF/classes/subject.properties

sources configured in: subject.properties

subject.properties ldap source id: ldap: personLdap

subject.properties groupersource id: g:gsa

subject.properties groupersource id: grouperEntities

Grouper warning: cannot find group from config: deprovisioning admin group can deprovision from all groups/objects in Grouper even if the user is not a Grouper overall SysAdmin: etc:deprovisioning:deprovisioningAdmins

Grouper error: etc:deprovisioning:deprovisioningAdmins, edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (741741d0): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (45a1d057),

Exception in list: (class [Ljava.lang.Object;), ByHqlStatic, query: 'select ms, m from MembershipEntry as ms, Member as m where ms.ownerGroupId = :owner and ms.memberUuid = :member and ms.memberUuid = m.uuid and ms.enabledDb = 'T'', cacheable: false, cacheRegion: edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO, tx type: null, tx type: nullBind var[0]: 'Param (class java.lang.String): 'owner'->'8a101d8652034ff886576fd141f0cb70'Bind var[1]: 'Param (class java.lang.String): 'member'->'496458b5c1ca4757ab70541f15d3ec34', ,

Problem in HibernateSession: HibernateSession (1d98daa0): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (45a1d057),

Problem saving group: etc:deprovisioning:deprovisioningAdmins, thread: 2d6e8792

at edu.internet2.middleware.grouper.hibernate.HibernateSession._internal_hibernateSessionCatch(HibernateSession.java:589)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:711)

at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.list(ByHqlStatic.java:407)

at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.listSet(ByHqlStatic.java:458)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO.findAllByGroupOwnerAndMember(Hib3MembershipDAO.java:1805)

at edu.internet2.middleware.grouper.privs.GrouperNonDbAccessAdapter.getPrivs(GrouperNonDbAccessAdapter.java:248)

at edu.internet2.middleware.grouper.privs.AccessWrapper.getPrivileges(AccessWrapper.java:139)

at edu.internet2.middleware.grouper.privs.GrouperAllAccessResolver.getPrivileges(GrouperAllAccessResolver.java:127)

at edu.internet2.middleware.grouper.privs.GrouperSystemAccessResolver.getPrivileges(GrouperSystemAccessResolver.java:109)

at edu.internet2.middleware.grouper.privs.CachingAccessResolver.getPrivileges(CachingAccessResolver.java:101)

at edu.internet2.middleware.grouper.privs.CachingAccessResolver.hasPrivilege(CachingAccessResolver.java:160)

at edu.internet2.middleware.grouper.privs.WheelAccessResolver.hasPrivilege(WheelAccessResolver.java:345)

at edu.internet2.middleware.grouper.privs.ValidatingAccessResolver.hasPrivilege(ValidatingAccessResolver.java:150)

at edu.internet2.middleware.grouper.Group.hasRead(Group.java:4525)

at edu.internet2.middleware.grouper.GroupSave$1$1.callback(GroupSave.java:642)

at edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:974)

at edu.internet2.middleware.grouper.GroupSave$1.callback(GroupSave.java:444)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO$1.callback(Hib3TransactionDAO.java:66)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:701)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO.transactionCallback(Hib3TransactionDAO.java:56)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:87)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:106)

at edu.internet2.middleware.grouper.GroupSave.save(GroupSave.java:435)

at edu.internet2.middleware.grouper.Group.saveGroup(Group.java:366)

at edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkGroup(GrouperCheckConfig.java:221)

at edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkGroups(GrouperCheckConfig.java:853)

at edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:499)

at edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:315)

at edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:165)

at edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)

at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)

at org.hibernate.loader.Loader.doQuery(Loader.java:919)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)

at org.hibernate.loader.Loader.doList(Loader.java:2617)

at org.hibernate.loader.Loader.doList(Loader.java:2600)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)

at org.hibernate.loader.Loader.list(Loader.java:2424)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)

at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)

at edu.internet2.middleware.grouper.hibernate.ByHql.list(ByHql.java:380)

at edu.internet2.middleware.grouper.hibernate.ByHqlStatic$2.callback(ByHqlStatic.java:417)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:701)

... 28 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:459)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:999)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:539)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:235)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:239)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1246)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1500)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1717)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4401)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4482)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6272)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:353)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)

... 45 more

Couldnt startup grouper: Problem in HibernateSession: HibernateSession (7ce29a2d): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (243bf087),

Exception in list: (class [Ljava.lang.Object;), ByHqlStatic, query: 'select ms, m from MembershipEntry as ms, Member as m where ms.ownerAttrDefId = :owner and ms.memberUuid = :member and ms.memberUuid = m.uuid and ms.enabledDb = 'T'', cacheable: false, cacheRegion: edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO, tx type: null, tx type: nullBind var[0]: 'Param (class java.lang.String): 'owner'->'80075426c19b41758c65e6a6e74c6a9a'Bind var[1]: 'Param (class java.lang.String): 'member'->'496458b5c1ca4757ab70541f15d3ec34', ,

Problem in HibernateSession: HibernateSession (634ca3e7): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (243bf087)

edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (7ce29a2d): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (243bf087),

Problem in HibernateSession: HibernateSession (634ca3e7): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (243bf087)

at edu.internet2.middleware.grouper.hibernate.HibernateSession._internal_hibernateSessionCatch(HibernateSession.java:589)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:711)

at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.list(ByHqlStatic.java:407)

at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.listSet(ByHqlStatic.java:458)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO.findAllByAttrDefOwnerAndMember(Hib3MembershipDAO.java:2542)

at edu.internet2.middleware.grouper.privs.GrouperNonDbAttrDefAdapter.getPrivs(GrouperNonDbAttrDefAdapter.java:194)

at edu.internet2.middleware.grouper.privs.AttributeDefWrapper.getPrivileges(AttributeDefWrapper.java:122)

at edu.internet2.middleware.grouper.privs.GrouperAllAttrDefResolver.getPrivileges(GrouperAllAttrDefResolver.java:99)

at edu.internet2.middleware.grouper.privs.GrouperSystemAttrDefResolver.getPrivileges(GrouperSystemAttrDefResolver.java:101)

at edu.internet2.middleware.grouper.privs.CachingAttrDefResolver.getPrivileges(CachingAttrDefResolver.java:133)

at edu.internet2.middleware.grouper.privs.CachingAttrDefResolver.hasPrivilege(CachingAttrDefResolver.java:201)

at edu.internet2.middleware.grouper.privs.WheelAttrDefResolver.hasPrivilege(WheelAttrDefResolver.java:341)

at edu.internet2.middleware.grouper.privs.ValidatingAttrDefResolver.hasPrivilege(ValidatingAttrDefResolver.java:127)

at edu.internet2.middleware.grouper.attr.AttributeDefPrivilegeDelegate.hasAttrAdmin(AttributeDefPrivilegeDelegate.java:87)

at edu.internet2.middleware.grouper.attr.AttributeDefSave$1$1.callback(AttributeDefSave.java:738)

at edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:974)

at edu.internet2.middleware.grouper.attr.AttributeDefSave$1.callback(AttributeDefSave.java:495)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO$1.callback(Hib3TransactionDAO.java:66)

at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:701)

at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3TransactionDAO.transactionCallback(Hib3TransactionDAO.java:56)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:87)

at edu.internet2.middleware.grouper.hibernate.GrouperTransaction.callbackGrouperTransaction(GrouperTransaction.java:106)

at edu.internet2.middleware.grouper.attr.AttributeDefSave.save(AttributeDefSave.java:488)

at edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkAttributes(GrouperCheckConfig.java:2080)

at edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:501)