grouper-users - RE: [grouper-users] problem running grouper on copy of its database

Subject: Grouper Users - Open Discussion List

List archive

RE: [grouper-users] problem running grouper on copy of its database

From: "Hyzer, Chris" <>
To: Baron Fujimoto <>
Cc: Grouper Users <>
Subject: RE: [grouper-users] problem running grouper on copy of its database
Date: Mon, 22 May 2017 00:54:38 +0000
Accept-language: en-US
Authentication-results: hawaii.edu; dkim=none (message not signed) header.d=none;hawaii.edu; dmarc=none action=none header.from=isc.upenn.edu;
Ironport-phdr: 9a23:xJ0j7Bw+/P1B97TXCy+O+j09IxM/srCxBDY+r6Qd2u8RIJqq85mqBkHD//Il1AaPBtSEragZwLCO6ujJYi8p2d65qncMcZhBBVcuqP49uEgeOvODElDxN/XwbiY3T4xoXV5h+GynYwAOQJ6tL1LdrWev4jEMBx7xKRR6JvjvGo7Vks+7y/2+94fdbghMhjexe61+IRW5oQnPqsUanJZpJ7osxBfOvnZGYfldy3lyJVKUkRb858Ow84Bm/i9Npf8v9NNOXLvjcaggQrNWEDopM2Yu5M32rhbDVheA5mEdUmoNjBVFBRXO4QzgUZfwtiv6sfd92DWfMMbrQ704RSiu4qF2QxLzliwJKyA2/33WisxojaJUvhShpwBkw4XJZI2ZLedycr/Bcd8fQ2dKQ8RfWDFbAo6kb4UBEfcPPfpWoYf+qVsBrxq+ChWjC+700DBEmmP60Lcm3+g9Dw3L2hErEdIUsHTTqdX4LKkeX+K1zajJ0zrDdeta0irj5YjIaBAhoOqMUbxtesfWzEkgCwPFj1WRqIP7JTOYzeUNs3OH4OZ6SOKvk3Aoqwd3ojS12Mgjl5TJi5sTx1vZ+yt5x4M1Kse5SE59edOkEZ1Qtz2EOItsRMMtXX1otDggxrIYpJG7YS4Hw4kkyR7Hc/GLbZKH7g7+WOuUPDt0mW9pdbexhxu97USs1ujxW8uq31tFsiZIl9zBum4R2xDP8sSLV/Vw8lu71TuLygzf8P9ILV0wmKbBNpIswL89m5wOukrZBCD2gl/5jKqOe0Uk5Oeo7+Pnb63+qJKAMIF4lhzyPr0zlMKnHOg0Kw8OUHOF9uim073j4FH5T65Njv0rlKnWrYrWJdwBpq6+Hw9azJos6wq+Dzeh1tQUh34HLE9ZeBKDiIjpPFLOLOrkAve4hlSgiDZrx/bYMb39GpjBMGLMnKv8cbt49kJQ1Rc/wNVR559bFr0NPPf+V03vu9HXEhM0Ngm5z/j7BNlhzo8eXHiAAq6dMKPcq1+I4ecvLvGJZIAJuDb8K/kk6OD0jXIimF8dZrWm0YEKaHCgAPtmJUOZbWDyjdgcDGcGphA+Q/DyiF2eTT5TYG6/X60m5jE8FYKmFZnMRpq0jLycxye0AIdWZntdB1CIEHfobJmEW+wSZC6II89hlCAEWqa7S48nyx6uqBH2x6B5IeXJ5y1L/a7kgfJ8/erV3T8v9z1xCNiamzWHRntzmEsITiA/3aE5rEBgnASty6991rZ4BM5e/bcBeQcgNIWWh7h/ANDjSA/bVtaSQxC7WtigB3c8Qs9nkIxGWFp0B9j31kOL5CGtGbJA0uXTXJE=
Spamdiagnosticmetadata: NSPM
Spamdiagnosticoutput: 1:99

I exported and imported the demo server to a local mysql, I put the steps here:

https://spaces.internet2.edu/display/Grouper/Migrating+Grouper+from+one+MySQL+to+another

1. Create a schema

a. utf8 charset

b. utf8_bin collation

2. Create user, and grant all to that user on that schema

3. Exporting MYSQL structure and data did not work. So startup grouper against the new schema, and create tables with

a. gsh -registry -runscript

b. truncate the database, so there is no data

4. Export data to SQL from existing server, note, include option to disable and re-enable foreign keys

5. Import into new database

6. Turn on Grouper

Does it work?

Thanks

Chris

-----Original Message-----
From: Baron Fujimoto [mailto:]
Sent: Friday, May 19, 2017 5:10 PM
To: Hyzer, Chris <>
Cc: Grouper Users <>
Subject: Re: [grouper-users] problem running grouper on copy of its database

Following up on this once again. We've tried a few more things, but we've

made no progress or have any good ideas on how to resolve this.

Full stack trace is included in the original post below following "Full

logs".

The exports/dumps and imports/restores were performed using Oracle's MySQL

Workbench. I've exported to both a self contained .sql file file as well

as to a directory with each table as its own .sql file.

When tried re-exporting the DB imported from the export of the original

and comparing the resulting exports, there are no differences in the SQL

files - with the exception that when I export the original DB, you get a

db_routines.sql file that you don't get when you re-export it again after

importing it into a new db. I dump them with the following MySQL Workbench

export: options Stored Procedures and Functions, Events, Triggers.

Working from the lowest level error in the stanc trace that looks useful:

Duplicate entry '56a628646e5949bf89868455caa72adb-39e8f222fa1d434f87d40cc4f063371' for key 'membership_uniq_idx'

The value the error in the stack trace identified as a duplicate entry

does not occur anywhere in the exported SQL files

('56a628646e5949bf89868455caa72adb-39e8f222fa1d434f87d40cc4f063371')

The index 'membership_uniq_idx' only appears to be associated with the

table grouper_memberships.

CREATE TABLE `grouper_memberships` (

`id` varchar(40) COLLATE utf8_bin NOT NULL,

`member_id` varchar(40) COLLATE utf8_bin NOT NULL,

`owner_id` varchar(40) COLLATE utf8_bin NOT NULL,

`field_id` varchar(40) COLLATE utf8_bin NOT NULL,

...

UNIQUE KEY `membership_uniq_idx` (`owner_id`,`member_id`,`field_id`),

...

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I'm not sure This is the best way to actually determine this, but we can't

find any duplicate entries int the imported grouper_memberships table with

the following sql command:

select owner_id,member_id,field_id,count(*) from grouper_memberships group by owner_id,member_id,field_id order by count(*);

There are no counts > 1.

Does this mean there may be a problem or corruption with the index? I don't

know how I have any control over that.

The original db is on MySQL version 5.5.54. I've tried importing it into

another 5.5.54 instance as well as a newer 5.6.35 instance with the same

results.

If anyone has successfully done this with MySQL, could you share the exact

commands used for export/import?

All feedback we've received is that this dump/restore process should work,

but we can't get past this. Any ideas or suggestions welcomed.

Aloha,

-baron

>-----Original Message-----

>From: Baron Fujimoto []

>Sent: Friday, April 28, 2017 3:27 PM

>To: Hyzer, Chris <>

>Cc: Grouper Users <>

>Subject: Re: [grouper-users] problem running grouper on copy of its database

>AFAIK, there *shouldn't* be any differences. Our DBA has told us that the

>cloning was done via MySQL WorkBench's Data Export (self-contained file,

>single transaction option). And imported via the Data Import option.

>Export and import DBs are on different hosts (but should likewise be

>prettymuch the same).

>I will try repeating the process myself with MySQL Workbench. Maybe also

>try re-exporting the resulting copy and compare the single-file .sql

>export for differences.

>-baron

>On Fri, Apr 28, 2017 at 07:16:57PM +0000, Hyzer, Chris wrote:

>>It should work.

>>Are there any settings about case or character encoding or something that is different? I do the same thing with oracle all the time. And I did it with mysql recently when migrating demo servers.

>>How did they clone it? Export to SQL scripts?

>>Thanks

>>Chris

>>-----Original Message-----

>>From: [] On Behalf Of Baron Fujimoto

>>Sent: Thursday, April 27, 2017 5:02 PM

>>To: Grouper Users <>

>>Subject: [grouper-users] problem running grouper on copy of its database

>>We had this problem about a year ago, and unfortunately we never did

>>manage to figure it out. We're working on our infrastructure again now,

>>and so we find ourselves confronted with it again.

>>We're running Grouper 2.2.2. I believe it's fully patched

>>(grouper_v2_2_2_api_patch_11). Our DBA had cloned (exported/imported) the

>>DBs from a source MySQL host to a new MySQL host. However, when we

>>configure Grouper to point ot the cloned DBs, we get the following errors

>>(for example when trying to run gsh):

>>=====

>>Couldnt startup grouper: Problem in HibernateSession: HibernateSession (5c77ba8f): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (66273da0),

>>Exception in save: edu.internet2.middleware.grouper.Membership, ByObjectStatic, query: ', cacheable: null, cacheRegion: null, entityName: ImmediateMembershipEntry, tx type: null,

>>Problem in HibernateSession: HibernateSession (30364216): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (66273da0)

>>edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (5c77ba8f): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (66273da0),

>>Exception in save: edu.internet2.middleware.grouper.Membership, ByObjectStatic, query: ', cacheable: null, cacheRegion: null, entityName: ImmediateMembershipEntry, tx type: null,

>>Problem in HibernateSession: HibernateSession (30364216): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (66273da0)

>>...

>>Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update,

>>Exception in save: edu.internet2.middleware.grouper.Membership,

>>...

>>Caused by: java.sql.BatchUpdateException: Duplicate entry '56a628646e5949bf89868455caa72adb-39e8f222fa1d434f87d40cc4f063371' for key 'membership_uniq_idx'

>>=====

>>I'm told that when the DBs are cloned it includes all

>>tables/views/constraints/foreignkeys.

>>I didn't find any actual duplicates in either the original or copy of the

>>DBs using the command,

>>"select owner_id, member_id, field_id from grouper_memberships group by owner_id, member_id, field_id having count(*) > 1"

>>Nor are there nulls as reported by,

>>"select owner_id, member_id, field_id from grouper_memberships where owner_id = null or member_id = null or field_id = null;"

>>If I search with the the value of the entry reported in the error msg, e.g.

>>"SELECT * FROM grouper_memberships where owner_id like '56a628646e5949bf89868455caa72adb';"

>>I get the same results for both the original and the copy of the DB.

>>id,member_id,owner_id,field_id,owner_group_id,owner_stem_id,owner_attr_def_id,via_composite_id,enabled,enabled_timestamp,disabled_timestamp,mship_type,creator_id,create_time,hibernate_version_number,context_id

>>7e137eca43ac4a65a2b83b7e857f29b8,39e8f222fa1d434f87d40cc4f0633717,56a628646e5949bf89868455caa72adb,5d5dce817f7e411799fc5f5e6328a28c,NULL,NULL,56a628646e5949bf89868455caa72adb,NULL,T,NULL,NULL,immediate,a166b283e3124910a9f0661eae5b2b8e,1460748581132,0,NULL

>>968d28bd7585499f97bc0b291e47dc87,39e8f222fa1d434f87d40cc4f0633717,56a628646e5949bf89868455caa72adb,cda43f2141844e7aaaa79dfc0c119757,NULL,NULL,56a628646e5949bf89868455caa72adb,NULL,T,NULL,NULL,immediate,a166b283e3124910a9f0661eae5b2b8e,1460748581148,0,NULL

>>28c6bdfa8132434baf008d51b22b46dd,a166b283e3124910a9f0661eae5b2b8e,56a628646e5949bf89868455caa72adb,0239be70f97243f690e1372cecd634dd,NULL,NULL,56a628646e5949bf89868455caa72adb,NULL,T,NULL,NULL,immediate,a166b283e3124910a9f0661eae5b2b8e,1460748581112,0,79c0d83dbb71409e88330a971899016c

>>We see this problem with every Grouper DB we've copied, so I assume it

>>must be something systemic. Any suggestions would be greatly appreciated.

>>I'm at a loss at this point.

>>Full logs:

>>=====

>>$ ./gsh

>>Using GROUPER_HOME: /home/grouper/grouper/grouper.apiBinary-2.2.2/bin/..

>>Using GROUPER_CONF: /home/grouper/grouper/grouper.apiBinary-2.2.2/bin/../conf

>>Using JAVA: /usr/java/latest/bin/java

>>using MEMORY: 64m-750m

>>Grouper starting up: version: 2.2.2, build date: null, env: grouper-test

>>grouper.properties read from: /home/grouper/grouper/grouper.apiBinary-2.2.2/conf/grouper.properties

>>Grouper current directory is: /home/grouper/grouper/grouper.apiBinary-2.2.2/bin

>>log4j.properties read from: /home/grouper/grouper/grouper.apiBinary-2.2.2/conf/log4j.properties

>>Grouper is logging to file: /home/grouper/grouper/logs/grouper-api_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties

>>grouper.hibernate.properties: /home/grouper/grouper/grouper.apiBinary-2.2.2/conf/grouper.hibernate.properties

>>grouper.hibernate.properties:

>>sources.xml read from: /home/grouper/grouper/grouper.apiBinary-2.2.2/conf/sources.xml

>>sources.xml ldap source id: UH test LDAP:

>>sources.xml groupersource id: g:gsa

>>sources.xml groupersource id: grouperEntities

>>Couldnt startup grouper: Problem in HibernateSession: HibernateSession (5c77ba8f): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (66273da0),

>>Exception in save: edu.internet2.middleware.grouper.Membership, ByObjectStatic, query: ', cacheable: null, cacheRegion: null, entityName: ImmediateMembershipEntry, tx type: null,

>>Problem in HibernateSession: HibernateSession (30364216): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (66273da0)

>>Exception in save: edu.internet2.middleware.grouper.Membership, ByObjectStatic, query: ', cacheable: null, cacheRegion: null, entityName: ImmediateMembershipEntry, tx type: null,

>>Problem in HibernateSession: HibernateSession (30364216): new, notReadonly, READ_WRITE_NEW, notActiveTransaction, session (66273da0)

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

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

>> at edu.internet2.middleware.grouper.hibernate.ByObjectStatic.save(ByObjectStatic.java:468)

>> at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3MembershipDAO.save(Hib3MembershipDAO.java:2053)

>> at edu.internet2.middleware.grouper.Membership.internal_addImmediateMembership(Membership.java:2748)

>> at edu.internet2.middleware.grouper.privs.GrouperNonDbAttrDefAdapter$1.callback(GrouperNonDbAttrDefAdapter.java:243)

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

>> at edu.internet2.middleware.grouper.privs.GrouperNonDbAttrDefAdapter.grantPriv(GrouperNonDbAttrDefAdapter.java:228)

>> at edu.internet2.middleware.grouper.privs.AttributeDefWrapper.grantPrivilege(AttributeDefWrapper.java:147)

>> at edu.internet2.middleware.grouper.privs.AttributeDefResolverDecorator.grantPrivilege(AttributeDefResolverDecorator.java:144)

>> at edu.internet2.middleware.grouper.privs.CachingAttrDefResolver.grantPrivilege(CachingAttrDefResolver.java:179)

>> at edu.internet2.middleware.grouper.privs.AttributeDefResolverDecorator.grantPrivilege(AttributeDefResolverDecorator.java:144)

>> at edu.internet2.middleware.grouper.privs.ValidatingAttrDefResolver.grantPrivilege(ValidatingAttrDefResolver.java:116)

>> at edu.internet2.middleware.grouper.attr.AttributeDefPrivilegeDelegate$2.callback(AttributeDefPrivilegeDelegate.java:453)

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

>> at edu.internet2.middleware.grouper.attr.AttributeDefPrivilegeDelegate.internal_grantPriv(AttributeDefPrivilegeDelegate.java:441)

>> at edu.internet2.middleware.grouper.attr.AttributeDefPrivilegeDelegate.grantPriv(AttributeDefPrivilegeDelegate.java:295)

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

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

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

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

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

>>Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update,

>>Exception in save: edu.internet2.middleware.grouper.Membership,

>> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)

>> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

>> at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)

>> at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)

>> at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)

>> at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)

>> at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)

>> at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)

>> at edu.internet2.middleware.grouper.hibernate.ByObject.save(ByObject.java:214)

>> at edu.internet2.middleware.grouper.hibernate.ByObjectStatic$7.callback(ByObjectStatic.java:481)

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

>> ... 21 more

>>Caused by: java.sql.BatchUpdateException: Duplicate entry '56a628646e5949bf89868455caa72adb-39e8f222fa1d434f87d40cc4f063371' for key 'membership_uniq_idx'

>> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)

>> at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)

>> at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)

>> at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

>> at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)

>> ... 29 more

>>Exception in thread "main" edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (5c77ba8f): notNew, notReadonly, READ_WRITE_NEW, activeTransaction, session (66273da0),