Using MySQL with Address Book Server

Before you begin making changes you need to create a new database. The following instructions assume the database name to be addressbook however you can change this to whatever you like. If you do change the database name, you will also need to change the configuration files below. The collation for this database should be set to UTF-8.

Example SQL statement to create database with UTF-8 collation

CREATE DATABASE  addressbook
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

The configuration files containing the database connection parameters can be found in :

/usr/share/addressBookServerEnterpriseEdition/apache-tomcat-6.0.24/

Disable H2 from starting automatically by setting the flag to false in the configuration file

bin/setenv.sh

JAVA_OPTS="$JAVA_OPTS -Dcom.addressbookserver.ENABLE_H2=false"

conf/server.xml

In the server.xml you need to configure your database connection setting. Typically this means specifying the host and access credential you use to access your database. This has to be done twice as Address Book Server used multiple connection pool, one during standard operation and another to authenticate web users. Both connection pools should point to the same database.

...
<Resource name="jdbc/AddressBookDB" auth="Container" type="javax.sql.DataSource"
   maxActive="25" maxIdle="5" maxWait="-1"
   username="addressbook" password="addressbook" driverClassName="com.mysql.jdbc.Driver"
   url="jdbc:mysql://localhost/addressbook?autoReconnect=true"/>
<Realm className="org.apache.catalina.realm.JDBCRealm"
  driverName="com.mysql.jdbc.Driver"
  connectionURL="jdbc:mysql://localhost/addressbook?autoReconnect=true"
  connectionName="addressbook" connectionPassword="addressbook"
  userTable="WebUsers" userNameCol="userid" userCredCol="password"
  userRoleTable="UserRoles" roleNameCol="accessRole"/>
...

It is important to specifiy the tables WebUsers and WebRoles in the correct case.

lib/META-INF/persistence.xml

Depending on the version and configuration of MySQL specify the appropriate dialect for use. The following dialects are available:

Dialect	Database Version
org.hibernate.dialect.MySQL5Dialect	MySQL 5
org.hibernate.dialect.MySQL5InnoDBDialect	MySQL 5 with INNODB
org.hibernate.dialect.MySQLDialect	MySQL (prior to 5.x)
org.hibernate.dialect.MySQLInnoDBDialect	MySQL with INNODB (prior to 5.x)

Example configuration:

...
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect"/>
...

The MySQL JDCB driver is available from  MySQL Connector/J. The JAR file should be placed in lib directory.

Reserved Words Problem

Recent releases cause a problem during startup as the server is not able to construct all the required tables automatically. The Recurrece table is not created correctly as interval, until and others are reserved words in MySQL. Depending if you are using INNODB or not. And then restart tomcat. It should now startup without errors. Visit :  MySQL Reserved Words for more details on this problem.

To get around this problem download and place the column mapping configuration file (ORM.XML attached on the bottom of this page) into the following path : addressBookServerEnterpriseEdition/apache-tomcat-6.0.24/lib/META-INF. This corrects the column name mappings used by Address Book Server to be MySQL friendly.

Only now are you ready to start tomcat using the instruction : Restarting Address Book Server. It's best to start running tomcat in debug mode to see any error messages immediately in Terminal.

Upgrade process for existing users

Existing users can either re-create a new database schema, or use the following SQL statements to adjust their current schema to the column mapping defined in ORM.xml

ALTER TABLE `addressbook`.`recurrence` CHANGE COLUMN `interval` `intervl` INTEGER DEFAULT NULL,
 CHANGE COLUMN `until` `untl` DATETIME DEFAULT NULL;
ALTER TABLE `addressbook`.`davfile` MODIFY COLUMN `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 MODIFY COLUMN `parentFolder` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE `addressbook`.`davfolder` MODIFY COLUMN `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 MODIFY COLUMN `parentFolder` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

Thanks to Daniel Hoppe for submitting these statements.

conf/server.xml

In addition to creating those tables, existing users who are upgrading to a recent version will need to re-configure the server.xml file manually. In particular the following attributes: userTable="WebUsers" userNameCol="userid" userCredCol="password" userRoleTable="UserRoles" roleNameCol="accessRole"

...
<Resource name="jdbc/AddressBookDB" auth="Container" type="javax.sql.DataSource"
   maxActive="25" maxIdle="5" maxWait="-1"
   username="addressbook" password="addressbook" driverClassName="com.mysql.jdbc.Driver"
   url="jdbc:mysql://localhost/addressbook?autoReconnect=true"/>
<Realm className="org.apache.catalina.realm.JDBCRealm"
  driverName="com.mysql.jdbc.Driver"
  connectionURL="jdbc:mysql://localhost/addressbook?autoReconnect=true"
  connectionName="addressbook" connectionPassword="addressbook"
  userTable="WebUsers" userNameCol="userid" userCredCol="password"
  userRoleTable="UserRoles" roleNameCol="accessRole"/>
...