Grouper Users - Open Discussion List

["Rahman, Mahbub" <>]

Hi Shilen,

 

Thanks for looking into it.

 

Here is the stack trace:

 

2020-01-07 01:07:54,939: [DefaultQuartzScheduler_Worker-9] ERROR FindBadMembershipsDaemon.execute(131) -  - Error running job

edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (170d040e): new, readonly, READONLY_NEW, notActiveTransaction, session (63731726),

Exception in list: (class [Ljava.lang.Object;), ByHqlStatic, query: 'select distinct c.factorOwnerUuid, c.uuid, m.uuid from MembershipEntry ms, Member m, Composite c where c.typeDb = 'intersection' and c.leftFactorUuid = ms.ownerGroupId and ms.fieldId = :fieldId and ms.enabledDb = 'T' and ms.memberUuid = m.uuid and NOT m.subjectSourceIdDb = 'g:gsa' and 1 in     (select 1 from MembershipEntry ms2      where ms2.ownerGroupId = c.rightFactorUuid      and ms2.memberUuid = m.uuid      and ms2.fieldId = :fieldId      and ms2.enabledDb = 'T') and not 1 in     (select 1 from ImmediateMembershipEntry ms3      where ms3.ownerGroupId = c.factorOwnerUuid      and ms3.memberUuid = m.uuid      and ms3.fieldId = :fieldId      and ms3.type = 'composite'      and ms3.enabledDb = 'T') ', cacheable: false, cacheRegion: null, tx type: null, tx type: nullBind var[0]: 'Param (class java.lang.String): 'fieldId'->'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ',

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

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

                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.findMissingIntersectionMemberships(Hib3MembershipDAO.java:3482)

                at edu.internet2.middleware.grouper.misc.FindBadMemberships.checkComposites(FindBadMemberships.java:412)

                at edu.internet2.middleware.grouper.misc.FindBadMemberships.checkAll(FindBadMemberships.java:193)

                at edu.internet2.middleware.grouper.misc.FindBadMembershipsDaemon.execute(FindBadMembershipsDaemon.java:81)

                at edu.internet2.middleware.grouper.app.loader.GrouperDaemonJob.execute(GrouperDaemonJob.java:57)

                at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

                at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

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

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

                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:703)

                ... 9 more

Caused by: java.sql.SQLNonTransientConnectionException: (conn=366996) Communications link failure with primary host xxx.xxx.xxx.harvard.edu:3306. Connection timed out

                at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:240)

                at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171)

                at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)

                at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:230)

                at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157)

                at org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:172)

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

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

                ... 26 more

Caused by: java.sql.SQLNonTransientConnectionException: Communications link failure with primary host xxx.xxx.xxx.harvard.edu:3306. Connection timed out

on HostAddress{host='xxx.xxx.xxx.harvard.edu', port=3306},master=true.

                at org.mariadb.jdbc.internal.failover.AbstractMastersListener.throwFailoverMessage(AbstractMastersListener.java:534)

                at org.mariadb.jdbc.internal.failover.FailoverProxy.handleFailOver(FailoverProxy.java:364)

                at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:300)

                at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:270)

                at com.sun.proxy.$Proxy2.executeQuery(Unknown Source)

                at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221)

                ... 30 more

Caused by: java.sql.SQLNonTransientConnectionException: Read timed out

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:1894)

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1437)

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415)

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:288)

                at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:498)

                at org.mariadb.jdbc.internal.failover.impl.MastersSlavesListener.invoke(MastersSlavesListener.java:226)

                at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:278)

                ... 33 more

Caused by: java.net.SocketTimeoutException: Read timed out

                at java.net.SocketInputStream.socketRead0(Native Method)

                at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)

                at java.net.SocketInputStream.read(SocketInputStream.java:171)

                at java.net.SocketInputStream.read(SocketInputStream.java:141)

                at java.io.FilterInputStream.read(FilterInputStream.java:133)

                at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.fillBuffer(ReadAheadBufferedStream.java:130)

                at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.read(ReadAheadBufferedStream.java:103)

                at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:244)

                at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:215)

                at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1435)

                ... 40 more

 

I ran the missing intersection memberships query (given below) manually. I did not get any response back in ~15/20 minutes and canceled the query. Please note that the query with EXISTS clause returns result in couple of minutes in our same environment.

 

select distinct composite2_.owner as col_0_0_,

                composite2_.id as col_1_0_, member1_.id as col_2_0_ from grouper_memberships_all_v membership0_

                cross join grouper_members member1_ cross join grouper_composites composite2_

                where composite2_.type='intersection' and composite2_.left_factor=membership0_.owner_group_id

                and membership0_.field_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx '

                and membership0_.immediate_mship_enabled='T' and membership0_.member_id=member1_.id

                and member1_.subject_source<>'g:gsa'

                and (1 in (select 1 from grouper_memberships_all_v membership3_

                where membership3_.owner_group_id=composite2_.right_factor

                and membership3_.member_id=member1_.id

                and membership3_.field_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx '

                and membership3_.immediate_mship_enabled='T'))

                and (1 not in  (select 1 from grouper_memberships immediatem4_

                where immediatem4_.owner_group_id=composite2_.owner

                and immediatem4_.member_id=member1_.id and immediatem4_.field_id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

                and immediatem4_.mship_type='composite' and immediatem4_.enabled='T'))

 

Some additional information:

We are using MariaDB Connector/J driver to support failover for AWS aurora mysql (obviously the error is not error is not related to the driver).

Number of records:

    grouper_memberships_all_v ~14 millions

     grouper_members ~770,000

    grouper_composites ~ 563

    grouper_memberships ~ 6 millions

 

Thanks,

Mahbub

 

From: <> on behalf of Shilen Patel <>
Reply-To: Shilen Patel <>
Date: Tuesday, January 7, 2020 at 7:20 AM
To: "Rahman, Mahbub" <>
Cc: "" <>
Subject: Re: [grouper-users] findBadMemberships job failure after taking latest patches

 

Hi Mahbub,

 

Do you have a stack trace?  If you run the problematic query manually, does it run but take a long time? 

 

Thanks!

 

- Shilen

 

From: <> on behalf of "Rahman, Mahbub" <>
Reply-To: "Rahman, Mahbub" <>
Date: Monday, January 6, 2020 at 3:30 PM
To: "" <>
Subject: [grouper-users] findBadMemberships job failure after taking latest patches

 

Hello,

 

We are using clustered AWS aurora mysql RDS (engine version: mysql 5.7.mysql_aurora.2.04).

After taking the latest v2.4.0 patches, findBadMemberships job is failing for me with connection timed out error. It looks like the query changes applied as part of GRP-2080 and GRP-2117 is causing this error in our environment (from EXISTS to IN clause for sub queries).

I have the indices applied for grouper_composites.type grouper_attribute_assign.attribute_assign_type and the tables are analyzed.

We would appreciate your insight and recommendations to resolve this issue.

 

Thanks,

Mahbub