Grouper Users - Open Discussion List

[Baron Fujimoto <>]

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 
>[mailto:]
> 
>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: 
>>
>> 
>>[mailto:]
>> 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, 
>>edu.internet2.middleware.grouper.hibernate.ByObject@6ede46f6
>>...
>>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: 
>>grpr_admin@jdbc:mysql://dbfoo.hawaii.edu:3306/grprfoo?CharSet=utf8&useUnicode=true&characterEncoding=utf8
>>sources.xml read from:        
>>/home/grouper/grouper/grouper.apiBinary-2.2.2/conf/sources.xml
>>sources.xml ldap source id:   UH test LDAP: 
>>cn=*****@ldaps://ldapfoo.hawaii.edu:636
>>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)
>>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)
>>    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.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, 
>>edu.internet2.middleware.grouper.hibernate.ByObject@6ede46f6
>>    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),
>>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.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, 
>>edu.internet2.middleware.grouper.hibernate.ByObject@6ede46f6
>>    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
>>
>>-- 
>>Baron Fujimoto 
>><>
>> :: UH Information Technology Services
>>minutas cantorum, minutas balorum, minutas carboratum desendus pantorum
>
>-- 
>Baron Fujimoto 
><>
> :: UH Information Technology Services
>minutas cantorum, minutas balorum, minutas carboratum desendus pantorum

-- 
Baron Fujimoto 
<>
 :: UH Information Technology Services
minutas cantorum, minutas balorum, minutas carboratum desendus pantorum