Grouper Users - Open Discussion List

[Jeffrey T Eaton <>]

What collation would be the recommended one for a MySQL database?  

I just tried an experiment of setting up a brand new Grouper 2.2.1 with MySQL 
installation, on Ubuntu 14.04.1 LTS, and it defaulted to using InnoDB tables 
with latin1_sweedish_ci collation, which is of course case-insensitive.  If 
that's not advisable, then perhaps the Grouper installer should set an 
explicit collation (and probably explicit table type, since using MyISAM is a 
bad idea as well) when creating the tables?  Or at least, the documentation 
could better advise which default collation to use for a Grouper MySQL 
database?

There's still a fundamental question of whether or not group names should be 
treated as case-insensitive.  I would argue that given that LDAP and AD both 
treat DNs as case-insensitive, then Grouper should as well (or at least, have 
an option to configure it to do so).  What happens now if two groups are 
created in Grouper, differing only in case, and PSP tries to provision them 
to LDAP?  

-jeaton

> On Jan 28, 2015, at 1:23 PM, Chris Hyzer 
> <>
>  wrote:
> 
> Grouper should not be used with a case insensitive database.  I dont think 
> you should run a service on a case insensitive database that doesnt support 
> that (and grouper has never claimed to support that).  You will have 
> problems if someone creates a group with the same name as another group 
> with different case (or other issues, that is just an example).
> 
> Thanks,
> Chris
> 
> -----Original Message-----
> From: Rahul Doshi 
> [mailto:]
>  
> Sent: Wednesday, January 28, 2015 1:05 PM
> To: Waldbieser, Carl; Dorsey Dick
> Cc: 
> ;
>  Chris Hyzer
> Subject: Re: [grouper-users] RE: Web services case sensitive with 2.2.1
> 
> We are using case insensitive MySQL DB on test and production.  Only 
> difference is, we are running 2.1.4 on production and 2.2.1 on test.  I 
> debugged the code and found in the method retrieveGroupIfNeeded in 
> WsGroupLookup.java, there is case sensitive if statement in 2.2.1 branch 
> but not in 2.1.4 branch. The error ³GROUP_UUID_DOESNT_MATCH_NAME² the code 
> returns if conditional statement is true matches what rest method is 
> returning when doing all lowercase group name searches.  I am also 
> including complete method for easy reference.  Can this be considered as 
> bug in 2.2.1?
> 
> if (hasUuid && !StringUtils.equals(this.uuid, theGroup.getUuid())) {Š}
> (2.2.1)
> 
> if ((hasUuid && !StringUtils.equals(this.uuid, theGroup.getUuid())) || 
> (hasName && !StringUtils.equals(this.groupName, theGroup.getName())) || 
> (hasIdIndex && !GrouperUtil.equals(GrouperUtil.longValue(this.idIndex),
> theGroup.getIdIndex()))) {Š} (2.1.4)
> 
> 
> 2.1.4 Method
> public Group retrieveGroupIfNeeded(GrouperSession grouperSession,
>      String invalidQueryReason) throws WsInvalidQueryException {
> 
>    //see if we already retrieved
>    if (this.groupFindResult != null) {
>      return this.group;
>    }
> 
>    //assume success (set otherwise if there is a problem)
>    this.groupFindResult = GroupFindResult.SUCCESS;
> 
>    try {
>      boolean hasUuid = !StringUtils.isBlank(this.uuid);
> 
>      boolean hasName = !StringUtils.isBlank(this.groupName);
> 
>      //must have a name or uuid
>      if (!hasUuid && !hasName) {
>        this.groupFindResult = GroupFindResult.INVALID_QUERY;
>        if (!StringUtils.isEmpty(invalidQueryReason)) {
>          throw new WsInvalidQueryException("Invalid group query for '"
>              + invalidQueryReason + "', " + this);
>        }
>        String logMessage = "Invalid query: " + this;
>        LOG.warn(logMessage);
>      }
> 
>      if (hasName) {
> 
>        //TODO make this more efficient
>        Group theGroup = GroupFinder.findByName(grouperSession,
> this.groupName, 
>            true, new QueryOptions().secondLevelCache(false));
> 
>        //make sure uuid matches
>        if (hasUuid && !StringUtils.equals(this.uuid, theGroup.getUuid())) {
>          this.groupFindResult =
> GroupFindResult.GROUP_UUID_DOESNT_MATCH_NAME;
>          String error = "Group name '" + this.groupName + "' and uuid '"
> + this.uuid
>              + "' do not match";
>          if (!StringUtils.isEmpty(invalidQueryReason)) {
>            throw new WsInvalidQueryException(error + " for '" + 
> invalidQueryReason
>                + "', " + this);
>          }
>          String logMessage = "Invalid query: " + this;
>          LOG.warn(logMessage);
>        }
> 
>        //success
>        this.group = theGroup;
> 
>      } else if (hasUuid) {
>        this.group = GroupFinder.findByUuid(grouperSession, this.uuid, true, 
> new QueryOptions().secondLevelCache(false));
>      }
> 
>    } catch (GroupNotFoundException gnf) {
>      this.groupFindResult = GroupFindResult.GROUP_NOT_FOUND;
>      if (!StringUtils.isBlank(invalidQueryReason)) {
>        throw new WsInvalidQueryException("Invalid group for '" + 
> invalidQueryReason
>            + "', " + this, gnf);
>      }
>    }
>    return this.group;
>  }
> 
> 2.2.1 Method
> public Group retrieveGroupIfNeeded(GrouperSession grouperSession,
>      String invalidQueryReason) throws WsInvalidQueryException {
> 
>    //see if we already retrieved
>    if (this.groupFindResult != null) {
>      return this.group;
>    }
> 
>    //assume success (set otherwise if there is a problem)
>    this.groupFindResult = GroupFindResult.SUCCESS;
> 
>    try {
>      boolean hasUuid = !StringUtils.isBlank(this.uuid);
> 
>      boolean hasName = !StringUtils.isBlank(this.groupName);
> 
>      boolean hasIdIndex = !StringUtils.isBlank(this.idIndex);
> 
>      //must have a name or uuid
>      if (!hasUuid && !hasName && !hasIdIndex) {
>        this.groupFindResult = GroupFindResult.INVALID_QUERY;
>        if (!StringUtils.isEmpty(invalidQueryReason)) {
>          throw new WsInvalidQueryException("Invalid group query for '"
>              + invalidQueryReason + "', " + this);
>        }
>        String logMessage = "Invalid query: " + this;
>        LOG.warn(logMessage);
>      }
> 
>      Group theGroup = null;
> 
>      if (hasName) {
> 
>        theGroup = GroupFinder.findByName(grouperSession, this.groupName,
>            true, new QueryOptions().secondLevelCache(false));
> 
>      } else if (hasUuid) {
>        theGroup = GroupFinder.findByUuid(grouperSession, this.uuid, true, 
> new QueryOptions().secondLevelCache(false));
>      } else if (hasIdIndex) {
>        theGroup = GroupFinder.findByIdIndexSecure(
>            GrouperUtil.longValue(this.idIndex), true, new 
> QueryOptions().secondLevelCache(false));
>      }
> 
>      //make sure matches
>      if ((hasUuid && !StringUtils.equals(this.uuid, theGroup.getUuid()))
>          || (hasName && !StringUtils.equals(this.groupName,
> theGroup.getName()))
>          || (hasIdIndex &&
> !GrouperUtil.equals(GrouperUtil.longValue(this.idIndex),
> theGroup.getIdIndex()))){
>        this.groupFindResult =
> GroupFindResult.GROUP_UUID_DOESNT_MATCH_NAME;
>        String error = "Group name '" + this.groupName + "', uuid '" + 
> this.uuid
>            + "', idIndex: " + this.idIndex + " do not match";
>        if (!StringUtils.isEmpty(invalidQueryReason)) {
>          throw new WsInvalidQueryException(error + " for '" + 
> invalidQueryReason
>              + "', " + this);
>        }
>        String logMessage = "Invalid query: " + this;
>        LOG.warn(logMessage);
>      }
> 
>      this.group = theGroup;
> 
>    } catch (GroupNotFoundException gnf) {
>      this.groupFindResult = GroupFindResult.GROUP_NOT_FOUND;
>      if (!StringUtils.isBlank(invalidQueryReason)) {
>        throw new WsInvalidQueryException("Invalid group for '" + 
> invalidQueryReason
>            + "', " + this, gnf);
>      }
>    }
>    return this.group;
>  }
> 
> 
> Thanks,
> Rahul
> 
> On 1/27/15, 3:35 PM, "Waldbieser, Carl" 
> <>
>  wrote:
> 
>> 
>> If you are using MySQL, check your collation settings:
>> 
>> 
>> http://stackoverflow.com/questions/341273/what-does-character-set-and-c
>> oll
>> ation-mean-exactly
>> 
>> Thanks,
>> Carl
>> 
>> ----- Original Message -----
>> From: "Chris Hyzer" 
>> <>
>> To: "Dorsey Dick" 
>> <>,
>>  
>> 
>> Sent: Tuesday, January 27, 2015 3:29:56 PM
>> Subject: [grouper-users] RE: Web services case sensitive with 2.2.1
>> 
>> uh...  I dont know how Grouper could ever be case insensitive... are 
>> you using the same database and settings?  Are there databases that do 
>> case insensitive like strings if you dont do upper() or lower()?
>> 
>> We have 2.1 in prod at Penn and I tried it and it is case sensitive.
>> Also, you can create two groups with different case so Grouper must be 
>> case sensitive searching for groups by name...
>> 
>> /**********************************************************************
>> ***
>> ******
>> * Copyright 2012 Internet2
>> *
>> * Licensed under the Apache License, Version 2.0 (the "License");
>> * you may not use this file except in compliance with the License.
>> * You may obtain a copy of the License at
>> *
>> *   http://www.apache.org/licenses/LICENSE-2.0
>> *
>> * Unless required by applicable law or agreed to in writing, software
>> * distributed under the License is distributed on an "AS IS" BASIS,
>> * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>> * See the License for the specific language governing permissions and
>> * limitations under the License.
>> ***********************************************************************
>> ***
>> ****/
>> /**
>> * @author mchyzer
>> * $Id$
>> */
>> package edu.internet2.middleware.grouperClient.poc;
>> 
>> import edu.internet2.middleware.grouper.util.GrouperUtil;
>> import edu.internet2.middleware.grouper.ws.GrouperWsConfig;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .Cr
>> edentials;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .Ht
>> tpClient;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .Ht
>> tpMethod;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .Us
>> ernamePasswordCredentials;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .au
>> th.AuthScope;
>> import
>> edu.internet2.middleware.grouperClientExt.org.apache.commons.httpclient
>> .me
>> thods.GetMethod;
>> 
>> 
>> /**
>> * run a manual web service
>> */
>> public class WsParamsRestPoc {
>> 
>> /**
>>  * @param args
>>  * @throws Exception
>>  */
>> public static void main(String[] args) throws Exception {
>> 
>>   String user =
>> GrouperWsConfig.retrieveConfig().propertyValueString("testUser");
>>   String pass =
>> GrouperWsConfig.retrieveConfig().propertyValueString("testPass");
>> 
>>   HttpClient httpClient = new HttpClient();
>>   HttpMethod httpMethod = new
>> GetMethod("https://our.grouper.url.upenn.edu/grouperWs/servicesRest/jso
>> n/v 
>> 2_1_001/groups/test%3AtestGroup/members?wsLiteObjectType=WsRestGetMembe
>> rsL iteRequest&subjectAttributeNames=PENNNAME");
>>   Credentials defaultcreds = new UsernamePasswordCredentials(user, 
>> pass);
>> 
>>   httpClient.getState()
>>     .setCredentials(new AuthScope("grouperws.apps.upenn.edu", 443), 
>> defaultcreds);
>>   httpClient.getParams().setAuthenticationPreemptive(true);
>>   httpClient.executeMethod(httpMethod);
>>   String result = httpMethod.getResponseBodyAsString();
>>   System.out.println(GrouperUtil.indent(result, false));
>> }
>> 
>> }
>> 
>> 
>> this is with the right name:
>> 
>> 
>> {
>> "WsGetMembersLiteResult":{
>>   "responseMetadata":{
>>     "millis":"182",
>>     "serverVersion":"2.1.1"
>>   },
>>   "resultMetadata":{
>>     "resultCode":"SUCCESS",
>>     "resultMessage":"Success for: clientVersion: 2.1.1, wsGroupLookups:
>> Array size: 1: [0]:
>> WsGroupLookup[pitGroups=[],groupName=test:testGroup]\n\n, memberFilter:
>> All, includeSubjectDetail: false, actAsSubject: null, fieldName: null,
>> subjectAttributeNames: Array size: 1: [0]: PENNNAME\n\n, paramNames: 
>> \n,
>> params: null\n, sourceIds: null\n, pointInTimeFrom: null, pointInTimeTo:
>> null",
>>     "success":"T"
>>   },
>>   "subjectAttributeNames":[
>>     "PENNNAME"
>>   ]
>>   ,
>>   "wsGroup":{
>>     "description":"testGroup",
>>     "displayExtension":"testGroup",
>>     "displayName":"test:testGroup",
>>     "extension":"testGroup",
>>     "name":"test:testGroup",
>>     "typeOfGroup":"group",
>>     "uuid":"dbfa18c3-a025-47b6-a9a0-be5ac02e8270"
>>   },
>>   "wsSubjects":[
>>     {
>>       "attributeValues":[
>>         "mchyzer"
>>       ]
>>       ,
>>       "id":"10021368",
>>       "name":"Chris Hyzer",
>>       "resultCode":"SUCCESS",
>>       "sourceId":"pennperson",
>>       "success":"T"
>>     }
>>   ]
>> }
>> }
>> 
>> 
>> this is with
>> 
>> 
>>   HttpMethod httpMethod = new
>> GetMethod("https://our.grouper.url.upenn.edu/grouperWs/servicesRest/jso
>> n/v 
>> 2_1_001/groups/Test%3AtestGroup/members?wsLiteObjectType=WsRestGetMembe
>> rsL iteRequest&subjectAttributeNames=PENNNAME");
>> 
>> 
>> 
>> {
>> "WsGetMembersLiteResult":{
>>   "responseMetadata":{
>>     "millis":"128",
>>     "serverVersion":"2.1.1"
>>   },
>>   "resultMetadata":{
>>     "resultCode":"GROUP_NOT_FOUND",
>>     "resultMessage":"There were 0 successes and 1 failures of getting 
>> members for groups.Invalid group for 'wsGroupLookup', 
>> WsGroupLookup[pitGroups=[],groupName=Test:testGroup,\n
>> groupFindResult=GROUP_NOT_FOUND],
>> WsGroupLookup[pitGroups=[],groupName=Test:testGroup,\n
>> groupFindResult=GROUP_NOT_FOUND], null",
>>     "success":"F"
>>   },
>>   "subjectAttributeNames":[
>>     "PENNNAME"
>>   ]
>> }
>> }
>> 
>> 
>> this is with:
>> 
>>   HttpMethod httpMethod = new
>> GetMethod("https://our.grouper.url.upenn.edu/grouperWs/servicesRest/jso
>> n/v 
>> 2_1_001/groups/test%3ATestGroup/members?wsLiteObjectType=WsRestGetMembe
>> rsL iteRequest&subjectAttributeNames=PENNNAME");
>> 
>> 
>> {
>> "WsGetMembersLiteResult":{
>>   "responseMetadata":{
>>     "millis":"106",
>>     "serverVersion":"2.1.1"
>>   },
>>   "resultMetadata":{
>>     "resultCode":"GROUP_NOT_FOUND",
>>     "resultMessage":"There were 0 successes and 1 failures of getting 
>> members for groups.Invalid group for 'wsGroupLookup', 
>> WsGroupLookup[pitGroups=[],groupName=test:TestGroup,\n
>> groupFindResult=GROUP_NOT_FOUND],
>> WsGroupLookup[pitGroups=[],groupName=test:TestGroup,\n
>> groupFindResult=GROUP_NOT_FOUND], null",
>>     "success":"F"
>>   },
>>   "subjectAttributeNames":[
>>     "PENNNAME"
>>   ]
>> }
>> }
>> 
>> 
>> 
>> 
>> From: Dorsey Dick 
>> [mailto:]
>> Sent: Tuesday, January 27, 2015 11:16 AM
>> To: Chris Hyzer; 
>> 
>> Subject: Re: Web services case sensitive with 2.2.1
>> 
>> Our production systems runs 2.1.4, there I'm able to search for groups 
>> and the group I put in the URL is case insensitive, I'm able to switch 
>> any character to any case and still get results back. When I try to do 
>> the same in our test system, which runs 2.2.1, I receive an error 
>> GROUP_UUID_DOESNT_MATCH_NAME.
>> 
>> Test will only work when fetching the below URL:
>> /grouper-ws/servicesRest/v2_1_005/groups/Apps%3AProvisioning%3Aauto%3AS
>> usp
>> ended-Expires-DeleteDate/members
>> 
>> While production will work with any of the below URL's:
>> grouper-ws/servicesRest/v2_1_005/groups/APPS%3Arovisioning%3Aauto%3Asus
>> pen
>> ded-expires-deletedate/members
>> grouper-ws/servicesRest/v2_1_005/groups/apps%3Aprovisioning%3Aauto%3Asu
>> spe
>> nded-expires-deletedate/members
>> grouper-ws/servicesRest/v2_1_005/groups/APPS%3Aprovisioning%3AAUTO%3Asu
>> spe
>> nded-expires-deletedate/members
>> 
>> 
>> 
>> From: Chris Hyzer 
>> <<mailto:>>
>> Date: Tuesday, January 27, 2015 at 10:45 AM
>> To: Dorsey Dick 
>> <<mailto:>>,
>> "<mailto:>"
>> <<mailto:>>
>> Subject: RE: Web services case sensitive with 2.2.1
>> 
>> They were always case sensitive...  can you give more info about how 
>> things are different?
>> 
>> Thanks,
>> Chris
>> 
>> From: 
>> <mailto:grouper-users-request@intern
>> et2 .edu> 
>> [mailto:]
>>  On Behalf Of 
>> Dorsey Dick
>> Sent: Tuesday, January 27, 2015 10:43 AM
>> To: 
>> <mailto:>
>> Subject: [grouper-users] Web services case sensitive with 2.2.1
>> 
>> Hello,
>> 
>> We recently upgraded to grouper 2.2.1 in our test environment. Looking 
>> at the web services it seems the group names are now case sensitive 
>> when using then in a URL when they were not before. Is there anyway to 
>> change the behavior to have them be case insensitive? As we are 
>> currently trying to meet a deadline the favor of a quick reply is greatly 
>> appreciated.
>> 
>> Thanks,
>> Dorsey
>