Grouper Users - Open Discussion List

[Chris Hyzer <>]

Good news: Penn deployed 2.0.2 to prod (well, a majority of our testing was 
on snapshots, though this wasn't the problem)

Bad news: There was a serious problem with it in prodEnv but not testEnv 
(though they are very similar)...  so fyi you might not want to go to prod 
with 2.0.2 unpatched...  the fix is in the jira, Penn patched test/prod, and 
we are good to go.

https://bugs.internet2.edu/jira/browse/GRP-723

We will get back to you about this, the plans for 2.0.3?  etc...

Thanks,
Chris

Jira details:

Description: missing parens in SQL statement in large deployment cause severe 
performance problem, there is also probably a logic problem.

The problem here is that the problem query looks like this:

SELECT DISTINCT group0_.id AS id21_,
                group0_.hibernate_version_number AS hibernate2_21_,
                group0_.last_membership_change AS last3_21_,
                group0_.last_imm_membership_change AS last4_21_,
                group0_.parent_stem AS parent5_21_,
                group0_.creator_id AS creator6_21_,
                group0_.create_time AS create7_21_,
                group0_.modifier_id AS modifier8_21_,
                group0_.modify_time AS modify9_21_,
                group0_.name AS name21_,
                group0_.display_name AS display11_21_,
                group0_.extension AS extension21_,
                group0_.display_extension AS display13_21_,
                group0_.description AS descrip14_21_,
                group0_.context_id AS context15_21_,
                group0_.alternate_name AS alternate16_21_,
                group0_.type_of_group AS type17_21_
  FROM grouper_groups group0_, grouper_memberships_all_v membership1_
 WHERE ( membership1_.owner_group_id = group0_.id
       AND (membership1_.field_id IN
               ('0b195b7c-e526-4d40-9098-510e021093c1',
                'd68dad3b-be79-40ce-8128-86503ce21a37',
                '8f59f9fb-0caf-47f9-8fdf-428531f03514',
                '655aea9c-a8c1-429e-be4b-3b22605cf58f',
                'd647453b-db49-474f-834a-52db840377ea',
                '7b83eb56-eea1-4d73-b1b2-5134a799218f'))
       AND (membership1_.member_id IN
               ('theuserid',
                'GrouperAllId'))
       AND membership1_.immediate_mship_enabled = 'T'
       AND (group0_.name IN ('somestring2'))
       OR group0_.alternate_name IN ('somestring2') )

Note the lack of parens on the OR statement.

Here is what it should look like:

SELECT DISTINCT group0_.id AS id21_,
                group0_.hibernate_version_number AS hibernate2_21_,
                group0_.last_membership_change AS last3_21_,
                group0_.last_imm_membership_change AS last4_21_,
                group0_.parent_stem AS parent5_21_,
                group0_.creator_id AS creator6_21_,
                group0_.create_time AS create7_21_,
                group0_.modifier_id AS modifier8_21_,
                group0_.modify_time AS modify9_21_,
                group0_.name AS name21_,
                group0_.display_name AS display11_21_,
                group0_.extension AS extension21_,
                group0_.display_extension AS display13_21_,
                group0_.description AS descrip14_21_,
                group0_.context_id AS context15_21_,
                group0_.alternate_name AS alternate16_21_,
                group0_.type_of_group AS type17_21_
  FROM grouper_groups group0_, grouper_memberships_all_v membership1_
 WHERE ( membership1_.owner_group_id = group0_.id
       AND (membership1_.field_id IN
               ('0b195b7c-e526-4d40-9098-510e021093c1',
                'd68dad3b-be79-40ce-8128-86503ce21a37',
                '8f59f9fb-0caf-47f9-8fdf-428531f03514',
                '655aea9c-a8c1-429e-be4b-3b22605cf58f',
                'd647453b-db49-474f-834a-52db840377ea',
                '7b83eb56-eea1-4d73-b1b2-5134a799218f'))
       AND (membership1_.member_id IN
               ('theuserid',
                'GrouperAllId'))
       AND membership1_.immediate_mship_enabled = 'T'
       AND ( (group0_.name IN ('somestring2'))
       OR group0_.alternate_name IN ('somestring2') ) )

The bad query has an explain plan of: 1.2 trillion bytes, 7.5 million cost
The good query has an explain plan of: 706 bytes, 161 cost

The issue is this query is used when doing a subject search by identifier 
against the g:gsa source,
and when the WS authenticates, it does a subject lookup for the 
authenticating user by identifier.
In Penn's environment it was a 60 second penalty.
Then (ever so slightly good news), once Oracle does the query, its 
significantly improved the next time (if soon after), down to 15 seconds

Penn just put the new classfiles in the 
WEB-INF/classes/edu/internet2/middleware/grouper/internal/dao/hib3 dir and we 
are all set...

#################

The lack of parens kills the explain plan in oracle and the performance of 
large deployments (and is probably wrong logic). Here is the patch (note, the 
jira has a compiled patch too... unzip in the appropriate classes/ subdir):

Index: Hib3GroupDAO.java
===================================================================
--- Hib3GroupDAO.java (revision 7744)
+++ Hib3GroupDAO.java (working copy)
@@ -1074,7 +1074,7 @@
       hql.append(" where ");
     }
     
- hql.append(" theGroup.nameDb = :value or theGroup.alternateNameDb = :value 
");
+ hql.append(" ( theGroup.nameDb = :value or theGroup.alternateNameDb = 
:value ) ");
     
     byHqlStatic.createQuery(hql.toString())
       .setCacheable(true).setCacheRegion(KLASS + 
".FindByNameSecure").options(queryOptions);
@@ -2360,13 +2360,13 @@
       } else {
         sql.append(" where ");
       }
- sql.append(" theGroup.nameDb in ( ");
+ sql.append(" ( theGroup.nameDb in ( ");
       
       sql.append(HibUtils.convertToInClause(namesBatch, 
byHqlStatic)).append(" ) ");
 
       sql.append(" or theGroup.alternateNameDb in ( ");
       
- sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) 
");
+ sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) 
)");
 
       byHqlStatic
         .createQuery(sql.toString())


-----Original Message-----
From: 

 
[mailto:]
 On Behalf Of Tom Barton
Sent: Wednesday, January 04, 2012 8:09 PM
To: 
;
 Grouper Dev
Subject: [grouper-users] Grouper v2.0.2 release

The Internet2 Middleware Initiative is pleased to announce the release
of Grouper version 2.0.2.  This is a minor release that fixes some
issues with subject searches and the UI.

Here is a list of the 22 issues addressed in this release:

https://bugs.internet2.edu/jira/secure/IssueNavigator.jspa?reset=true&jqlQuery=project+%3D+GRP+AND+fixVersion+%3D+%222.0.2%22+AND+status+%3D+Resolved

For further information about this new release, to download the software
and release notes, for upgrade instructions, and a link to an online
Grouper demo, please visit:

http://www.internet2.edu/grouper/software.html
https://spaces.internet2.edu/display/Grouper/v2.0+Release+Notes

Apologies for cross-posting.

Thanks,
Tom
on behalf of the Grouper Project team