Grouper Users - Open Discussion List

[Colin Hudler <>]

Thanks for trying to duplicate.  While you were doing that, I ran

gsh.sh -registry -check

against a mysql database with no tables.  It created the complete DDL, 
so I could run it manually (contrast with following the wiki upgrade 
directions, for example).  There's two places that the script fails:

CREATE TABLE grouper_attributes
(
    id VARCHAR(40) NOT NULL,
    group_id VARCHAR(40) NOT NULL,
    field_id VARCHAR(40) NOT NULL,
    value VARCHAR(1024) NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attribute_field_value_idx ON grouper_attributes (field_id, 
value);
# FAIL! the field_id and value combined
# is too large for poor old mysql

The way I fixed it is to manually modify that line to

CREATE INDEX attribute_field_value_idx ON grouper_attributes (field_id, 
value(960));

In case you're interested, its RHEL5.4
Mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64)

The table has ENGINE=MyISAM DEFAULT CHARSET=latin1

Chris Hyzer wrote:
> Ive got mysql for windows 5.1.41.
> I create a new db and user, and grant all privs for that db to that user.
> I download the 1.5.3 Grouper api binary.
> Unzip it.
> Edit the grouper.hibernate.properties
>
> hibernate.dialect               = org.hibernate.dialect.MySQL5Dialect
> hibernate.connection.driver_class = com.mysql.jdbc.Driver
> hibernate.connection.url = jdbc:mysql://localhost:3306/grouper_test
> hibernate.connection.username         = grouper_test
> hibernate.connection.password         = **********
>
> Run the gsh command (output below).  It worked fine.  Attached is the DDL.  I 
> think what is happening is you have a multichar (UTF?) database where each 
> char in your database is two chars of index space.  So indexes that work for 
> me don't work for you.  So yes, wherever it fails, edit the DDL or the 
> database to get it to work.  With MYSQL you can have indexes on parts of 
> columns, so you should be able to get it to work.
>
> Let me know if you need more help.
>
> Thanks,
> Chris
>
> ###############################
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin>gsh -registry -runscript
> Using GROUPER_HOME:           
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin\..
> Using GROUPER_CONF:           
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin\../conf
> Using JAVA:                   "c:\dev_inst\java/bin/java"
> using MEMORY:                 64m-512m
> Grouper starting up: version: 1.5.3, build date: 2010/03/31 23:37:26, env: <no 
> label configured>
> grouper.properties read from: 
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\conf\grouper.properties
> Grouper current directory is: 
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin
> log4j.properties read from:   
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\conf\log4j.properties
> Grouper is logging to file:   
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin\..\logs\grouper_error.log,
>  at min le
> vel WARN for package: edu.internet2.middleware.grouper, based on 
> log4j.properties
> grouper.hibernate.properties: 
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\conf\grouper.hibernate.properties
> grouper.hibernate.properties: 
> grouper_test@jdbc:mysql://localhost:3306/grouper_test
> sources.xml read from:        
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\conf\sources.xml
> sources.xml groupersource id: g:gsa
> sources.xml jdbc source id:   jdbc: GrouperJdbcConnectionProvider
> (note, might need to type in your response multiple times (Java stdin is 
> flaky))
> (note, you can whitelist or blacklist db urls and users in the 
> grouper.properties)
> Are you sure you want to schemaexport all tables 
> (dropThenCreate=F,writeAndRunScript=T) in db user 'grouper_test', db ur
> l 'jdbc:mysql://localhost:3306/grouper_test'? (y|n):
> y
> Continuing...
> Grouper ddl object type 'Grouper' has dbVersion: 0 and java version: 22
> Grouper ddl object type 'Subject' has dbVersion: 0 and java version: 1
> Grouper database schema DDL requires updates
> (should run script manually and carefully, in sections, verify data before 
> drop statements, backup/export important data
>  before starting, follow change log on confluence, dont run exact same script 
> in multiple envs - generate a new one for
> each env),
> script file is:
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\ddlScripts\grouperDdl_20100406_13_53_28_299.sql
> Script was executed successfully
>
>
> C:\software\grouper\1.5.3\grouper.apiBinary-1.5.3\bin>
> ##################################
>
>
>
>
>
> -----Original Message-----
> From: Colin Hudler [mailto:] 
> Sent: Tuesday, April 06, 2010 12:10 PM
> To: 
>
> Subject: [grouper-users] Grouper DDL mysql max key length 1000 issue?
>
> hi,
>
> Following instructions from
> <https://spaces.internet2.edu/display/GrouperWG/v1.5.0+Upgrade+Instructions+from+v1.4.2>
> Method 1 again.  First, starting from a totally empty database, running
>
> ./bin/gsh.sh -registry -runscript
>
> I get the massive stack trace, which is attached to this email (trace1). 
>   It seems to hinge on "Specified key was too long; max key length is 1000 bytes".  This might be related to <https://bugs.internet2.edu/jira/browse/GRP-197>.  This defect appears to be fixed, which is my question.  Should I pursue a fix in the database or alter the DDL?
>
> If I run the command against a db with ddlVersion: 13, but all data truncated (including 
> grouper_ddl), per instructions "Drop all of the Grouper objects from your database", it 
> fails differently (trace2).  I now realize this is probably not what is intended by the 
> "drop all grouper objects" statement, perhaps it means to literally drop the 
> tables/structures (as I first tried to do)?
>
> Finally, if I truncate all tables EXCEPT ddlVersion, I get no error. 
> But should I trust it?  FWIW, in this case, I would prefer it create a clean new database to insert the exported xml into.