This documentation relates to Crowd 2.4.
If you are using an earlier version, please view the previous versions of the Crowd documentation and select the relevant version.
Skip to end of metadata
Comment: Corrected links that should have been relative instead of absolute.
Go to start of metadata

Follow the steps below to connect CrowdID to MySQL.

1. Configure MySQL

  1. Create a database user which CrowdID will connect as (e.g. crowduser).

  2. Create a database for CrowdID to store data in (e.g. crowdiddb).
    (warning) This must be a different database from the one used by Crowd.
    For a UTF-8 encoded database:

    create database crowdiddb character set utf8;
  3. Ensure that the user has permission to connect to the database, and create and populate tables.

2. Copy the MySQL Driver to your Application Server

  1. Download the latest MySQL Connector/J JDBC driver.
  2. Add the MySQL JDBC driver jar (mysql-connector-java-3.x.x-bin.jar) to the following directory:
    • For Crowd distribution:
      • Crowd 2.0.2 or later: {CROWD_INSTALL}/apache-tomcat/lib/.
      • Crowd 2.0.1 or earlier: {CROWD_INSTALL}/apache-tomcat/common/lib/.
    • For Crowd WAR distribution, copy the driver JAR to your application server. For example, on Tomcat:
      • Tomcat 5.5.x: common/lib/.
      • Tomcat 6.x: lib/.

Do not place the Debug Driver (mysql-connector-java-3.x.x-bin-g.jar) on the CLASSPATH as this can cause issues. (JRA-8674).

3. Configure your Application Server to Connect to MySQL

  1. Edit the file apache-tomcat-X.X.XX/conf/Catalina/localhost/openidserver.xml and customise the username, password, driverClassName and urlparameters for the Datasource.

    <Context path="/openidserver" docBase="../../crowd-openidserver-webapp" debug="0">

<Resource name="jdbc/CrowdIDDS" auth="Container" type="javax.sql.DataSource"
username="[enter db username here]"
password="[enter db password here]"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/crowdiddb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8"
[ delete the minEvictableIdleTimeMillis, timeBetweenEvictionRunsMillis and maxActive params here ]
/>

<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>

</Context>

    The URL above assumes a UTF-8 database — i.e. created with create database crowdiddb character set utf8;.

    MySQL closes idle connections after 8 hours, so the autoReconnect=true is necessary to tell the driver to reconnect.

  2. Delete the minEvictableIdleTimeMillis, timeBetweenEvictionRunsMillis and maxActive attributes (which are only needed for HSQL, and degrade performance otherwise).

4. Configure CrowdID to use MySQL

  1. Edit the build.properties file (located in the root of the Crowd distribution, not Crowd EAR-WAR) and modify the hibernate.dialect to the following. Please choose only one of the 3 available options depending on how you have configured your database server.

    *For MySQL set:*
hibernate.dialect=org.hibernate.dialect.MySQLDialect
*For MySQL with InnoDB set:*
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
*For MySQL with MyISAM set:*
hibernate.dialect=org.hibernate.dialect.MySQLMyISAMDialect
  2. Then run ./build.sh or build.bat. This will configure CrowdID to use the MySQL dialect.

If you do not wish to edit this file and run the build script, you can edit the jdbc.properties (which the above script modifies) directly. The jdbc.properties file is located here: crowd-openidserver-webapp\WEB-INF\classes\jdbc.properties. Modify the file to the following:

# - Crowd Configuration Options

hibernate.connection.datasource=java\:comp/env/jdbc/CrowdIDDS
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory

...

Next steps

You should now have an application server configured to connect to a database, and CrowdID configured to use the correct database. Now start up CrowdID and watch the logs for any errors.

RELATED TOPICS

4 Comments

Hide/Show Comments

  1. Jan 07, 2011

    Chad Beaudin

    These instructions refer to the mysql-connector-java-3.x.x-bin.jar.  However, the Connecting Crowd To A Database refer to the mysql-connector-java-5.x.x-bin.jar.  Should the crowdID MySQL instructions also be referring to the 5.x.x jar?

  2. Jan 07, 2011

    Chad Beaudin

    When using MySQL, step 4 states: For MySQL with InnoDB set:
    hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect

    However when running through the setup wizard for Crowd and selecting MySQL as the JDBC connection the "Hibernate Dialect" field is pre-populated with "org.hibernate.dialect.MySQL5InnoDBDialect"

    Should CrowdID also be using MySQL5InnoDBDialect?

  3. May 26, 2011

    Foo Guan Sim [Atlassian]

    I also noticed that setting hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect causes some table creation exceptions (SQL Syntax error, since the table creation syntax ends with Engine=InnoDB - Incompatible with MySQL5). Reverting it back to hibernate.dialect=org.hibernate.dialect.MySQLDialect is a temporary solution, although, I think Chad's suggestion is cleaner and is worth a try. 

  4. Nov 10, 2011

    Anonymous

    If you are using MySQL 5.x you will need to set your dialect to 

    org.hibernate.dialect.MySQL5InnoDBDialect

    or it will fail to create your CrowdID tables.