Setting up the database
Mysql advice
https://lists.internet2.edu/sympa/arc/grouper-users/2019-02/msg00019.html
I would recommand the use of the option of one file per table, it saved us when a log table took to much space on hard drive, a truncate recreate the file and so reduced the used space, without that it saves all in one file and a truncate doesn't free space, so you will need to stop your service for maintenance, dump the database and import it (without logs)
Configuring Grouper to connect to the database server and installing the schema
With an empty database created, go to GROUPER_HOME on the machine on which you are going to be running the Grouper API and daemon. Within GROUPER_HOME open the conf directory. This contains the configuration files that tell Grouper how to connect to it's registry database (the database we just created), and to a subject data source.
grouper.hibernate.properties contains configuration parameters which connect Grouper to the registry database. A sample file in included in the distribution, the full text of which is:
# # Grouper Hibernate Configuration # $Id: grouper.hibernate.example.properties,v 1.9 2009-08-11 20:18:09 mchyzer Exp $ # # # Hibernate3 # e.g. org.hibernate.dialect.Oracle10gDialect, org.hibernate.dialect.HSQLDialect # e.g. org.hibernate.dialect.PostgreSQLDialect ,org.hibernate.dialect.MySQL5Dialect # e.g. org.hibernate.dialect.SQLServerDialect hibernate.dialect = org.hibernate.dialect.HSQLDialect hibernate.cache.provider_class = org.hibernate.cache.EhCacheProvider # # Generic Hibernate Configuration # hibernate.cache.use_query_cache = true # e.g. mysql: com.mysql.jdbc.Driver # e.g. p6spy (log sql): com.p6spy.engine.spy.P6SpyDriver # for p6spy, put the underlying driver in spy.properties # e.g. oracle: oracle.jdbc.driver.OracleDriver # e.g. hsqldb: org.hsqldb.jdbcDriver # e.g. postgres: org.postgresql.Driver # e.g. mssql: com.microsoft.sqlserver.jdbc.SQLServerDriver hibernate.connection.driver_class = org.hsqldb.jdbcDriver # e.g. mysql: jdbc:mysql://localhost:3306/grouper?useSSL=false # e.g. p6spy (log sql): [use the URL that your DB requires] # e.g. oracle: jdbc:oracle:thin:@server.school.edu:1521:sid # e.g. hsqldb (a): jdbc:hsqldb:dist/run/grouper;create=true # e.g. hsqldb (b): jdbc:hsqldb:hsql://localhost:9001/grouper # e.g. postgres: jdbc:postgresql:grouper # e.g. mssql: jdbc:sqlserver://localhost:3280 hibernate.connection.url = jdbc:hsqldb:hsql://localhost/grouper hibernate.connection.username = sa # If you are using an empty password, depending upon your version of # Java and Ant you may need to specify a password of "". # Note: you can keep passwords external and encrypted: https://bugs.internet2.edu/jira/browse/GRP-122 hibernate.connection.password = hibernate.connection.autocommit = false # Use c3p0 connection pooling (since dbcp not supported in hibernate anymore) # http://www.hibernate.org/214.html, http://www.hibernate.org/hib_docs/reference/en/html/session-configuration.html hibernate.c3p0.max_size 16 hibernate.c3p0.min_size 0 #seconds hibernate.c3p0.timeout 100 hibernate.c3p0.max_statements 0 hibernate.c3p0.idle_test_period 100 hibernate.c3p0.acquire_increment 1 hibernate.c3p0.validate false #What to do if there are connection problems - see http://www.mchange.com/projects/c3p0/index.html#configuration_properties #These settings are designed to cause exceptions sooner rather than later so end users are not left with a hanging UI. Once the database #is available again a connection will be made witout further intervention #If tou get intermittent connection problems in the UI even though the database is OK try increasing the max_size setting above and #tune the settings below hibernate.c3p0.acquireRetryAttempts=2 hibernate.c3p0.acquireRetryDelay=500 hibernate.c3p0.checkoutTimeout=30000 hibernate.jdbc.use_streams_for_binary = true hibernate.max_fetch_depth = 1 hibernate.show_sql = false hibernate.jdbc.batch_size 20
As a minimum you will need change 4 parameters:
hibernate.dialect - change this to the dialect that matches you database, some examples are listed
hibernate.connection.driver_class - change this to the class name of the JDBC driver which you will use to connect to the database
hibernate.connection.url - the url used to connect to the database. The format of this will be determined by the jdbc driver
hibernate.connection.username - the username used to connect to the database
hibernate.connection.password - the password used to connect to the database. As the comment suggests, with a little more work the password can be encrypted
For postgres these parameters will be something like:
hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect hibernate.connection.driver_class = org.postgresql.Driver hibernate.connection.url = jdbc:postgresql://localhost/grouperdb hibernate.connection.username = grouper hibernate.connection.password = grouper
For mysql they will look like this:
hibernate.dialect = org.hibernate.dialect.MySQL5Dialect hibernate.connection.driver_class = com.mysql.jdbc.Driver hibernate.connection.url = jdbc:mysql://localhost/grouperdb?useSSL=false hibernate.connection.username = grouper hibernate.connection.password = grouper
For Oracle like this:
hibernate.dialect = org.hibernate.dialect.Oracle10gDialect hibernate.connection.driver_class = oracle.jdbc.driver.OracleDriver hibernate.connection.url = jdbc:oracle:thin:@localhost:1521:grouperdb hibernate.connection.username = grouper hibernate.connection.password = grouper
It is important to get these parameters correct or Grouper will be unable to connect to your database. If you forget to change the hibernate.dialect parameter you may find that Grouper connects, but reports odd errors.
You also need to ensure that sufficient connections are available on the database. Grouper uses the C3PO libraries for connection pooling, which uses asynchronous background threads heavily.
If insufficient threads are available you may see reports of deadlock in the Grouper logs.
There are other parameters in the file that can be set for performance tuning which we will return to later.
You can now set up the Grouper database. Open a command line and navigate to GROUPER_HOME/bin. Run the following command
./gsh.sh -registry - check (on Linux) gsh.bat -registry - check (on Windows)
You will see output similar to the following (here a MySQL database is being initialised).
~/grouper.apiBinary-1.6.1/bin$ ./gsh.sh -registry -check Using GROUPER_HOME: /home/rob/grouper.apiBinary-1.6.2/bin/.. Using GROUPER_CONF: /home/rob/grouper.apiBinary-1.6.2/bin/../conf Using JAVA: /usr/local/java/jdk1.6.0_21-x86//bin/java using MEMORY: 64m-512m Grouper starting up: version: 1.6.2, build date: 2010/10/15 16:03:21, env: <no label configured> grouper.properties read from: /home/rob/grouper.apiBinary-1.6.2/conf/grouper.properties Grouper current directory is: /home/rob/grouper.apiBinary-1.6.2/bin log4j.properties read from: /home/rob/grouper.apiBinary-1.6.2/conf/log4j.properties Grouper is logging to file: /home/rob/grouper.apiBinary-1.6.2/bin/../logs/grouper_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties grouper.hibernate.properties: /home/rob/grouper.apiBinary-1.6.2/conf/grouper.hibernate.properties grouper.hibernate.properties: grouper@jdbc:mysql://localhost/grouperdb sources.xml read from: /home/rob/grouper.apiBinary-1.6.2/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 allow or deny db urls and users in the grouper.properties) Are you sure you want to schemaexport all tables (dropThenCreate=F,writeAndRunScript=F) in db user 'grouper', db url 'jdbc:mysql://localhost/grouperdb'? (y|n):
Type y<enter> and an SQL script will be generated to install the Grouper registry tables on the database.
Continuing... Grouper ddl object type 'Grouper' has dbVersion: 0 and java version: 23 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: /home/rob/grouper.apiBinary-1.6.2/ddlScripts/grouperDdl_20101026_16_38_44_038.sql Note: this script was not executed due to option passed in
To run script via gsh, carefully review it, then run this:
gsh -registry -runsqlfile /home/rob/grouper.apiBinary-1.6.2/ddlScripts/grouperDdl_20101026_16_38_44_038.sql
Make a note of the file location and name, check it if you wish (this is more important for upgrades), and then execute it with a modification of the command shown above.
Note: there is currently a bug which prevents the script running on installations of MySQL which use the MyISAM storage engine. This is often set up as the default engine as it is the fastest, but it does not offer ACID compliance so it risks losing data. If you want to use the MyISAM engine you should edit the script and change the line:
CREATE INDEX attribute_field_value_idx ON grouper_attributes (field_id, value);
to:
CREATE INDEX attribute_field_value_idx ON grouper_attributes (field_id, value(960));
In my generated script this is at line 31. Thanks to Colin Hudler who posted this on the grouper-users list, along with the fix. The cause of the error is a field size of 1024 bytes which is then indexed. MyISAM has a fixed maximum index size of 1000 bytes (hence the error reported by MySQL: "Specified key was too long; max key length is 1000 bytes"). An alternative option is to change your default storage engine to InnoDB (which is ACID compliant) in my.cnf. You do this by specifying the default-storage-engine parameter in the configuration file used when mysql is started (called my.cnf by convention). On a Debian squeeze system the file is found at /etc/mysql/my.cnf, and I add the following line after the comments about InnoDB:
default-storage-engine=innodb
There are many other configuration parameters for InnoDB, so you'll want to check the MySQL documentation.
When you're ready to run the script run this command:
./gsh.sh -registry -runsqlfile /home/rob/grouper.apiBinary-1.6.2/ddlScripts/grouperDdl_20101026_16_38_44_038.sql
You will see the following output
Using GROUPER_HOME: /home/rob/grouper.apiBinary-1.6.2/bin/.. Using GROUPER_CONF: /home/rob/grouper.apiBinary-1.6.2/bin/../conf Using JAVA: /usr/local/java/jdk1.6.0_21-x86//bin/java using MEMORY: 64m-512m (note, might need to type in your response multiple times (Java stdin is flaky)) (note, you can allow or deny db urls and users in the grouper.properties) Are you sure you want to run the sql file in db user 'grouper', db url 'jdbc:mysql://localhost/grouperdb'? (y|n):
Type y<enter>, you will see the following output:
Continuing... Script was executed successfully Grouper starting up: version: 1.6.2, build date: 2010/10/15 16:03:21, env: <no label configured> grouper.properties read from: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/conf/grouper.properties Grouper current directory is: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/bin log4j.properties read from: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/conf/log4j.properties Grouper is logging to file: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/bin/../logs/grouper_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties grouper.hibernate.properties: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/conf/grouper.hibernate.properties grouper.hibernate.properties: grouper@jdbc:mysql://localhost/grouperdb sources.xml read from: /home/rob/Customers/Cardiff/Grouper/mysql/grouper.apiBinary-1.6.2/conf/sources.xml sources.xml groupersource id: g:gsa sources.xml jdbc source id: jdbc: GrouperJdbcConnectionProvider
You now have the API and database installed and working correctly. Next you need to tell grouper how to get information on subjects.