Setting up the database

Mysql advice

https://lists.internet2.edu/sympa/arc/grouper-users/2019-02/msg00019.html

mysql sample config

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.