Install Guide - DB (2.4)

5. Database Configuration

5.1. Deploy Drivers

The supported production-grade databases include MySQL 4.1.12+ (but MySQL 5.0 is not yet recommended for production - see the Database Configuration and Tuning section of the Sys Admin Guide) and Oracle 9i+.

InnoDB Storage Engine for MySQL Sakai requires transaction support, and so the InnoDB storage engine will be needed for MySQL deployments.

The version of the JDBC driver (or connector) is also important: for MySQL a 3.1.12+ connector should be used, while for Oracle the 10g driver must be used, even if the database is Oracle 9i. These drivers should be copied into your $CATALINA_HOME/common/lib directory, and they are available from the official sites:

Driver Versions Database driver versions are a common source of problems. It's worth emphasizing again that the Oracle 10g driver must be used for Sakai installations running against Oracle, even when the database is Oracle 9i. Problems have been reported for both the 3.1.10 and 3.1.11 MySQL drivers. 3.1.12 or higher should be fine.

5.2. Create Database and User

A Sakai database and privileged user must be prepared for Sakai's use. Consult your database documentation for details, but below are sample commands for MySQL.

C:\sakai\reference\sql\legacy\mysql\>
mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 51 to server version: 4.1.5-gamma-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sakai default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on sakai.* to sakaiuser@'localhost' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec

mysql> grant all on sakai.* to sakaiuser@'127.0.0.1' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Note that you will not need to create the database schema with its table structures, etc. A setting in sakai.properties allows it to create the necessary tables as Tomcat is starting up. See the section on database properties below.

UTF-8 Character Set When the database is created you must be sure to create it to use the UTF-8 character set, just as Tomcat was configured to use UTF-8. If you don't you may run into a range of issues when attempting to use Unicode characters in Sakai, and this goes for both MySQL and Oracle. Consult your DB documentation or a local DBA for instructions on how to do this. If you're not certain how your database is currently configured, you can check with a query. Here is a sample query from Oracle showing the correct value: SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; VALUE -------------------------------------------------------------------------------- AL32UTF8 Converting a database from one character set to another is non-trivial, particularly if it's a large production database, and so it's strongly recommended that you verify this aspect of your database creation before deploying Sakai.

5.3. Database Properties

There are settings in sakai.properties that also define the database technology and connection information. Appropriate sakai.properties settings for Oracle and MySQL, respectively, are listed below, and you need only modify them with your local particulars:

MySQL:

hibernate.dialect=org.hibernate.dialect.MySQLDialect
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
url@javax.sql.BaseDataSource=jdbc:mysql://SERVER:3306/DB?useUnicode=true&characterEncoding=UTF-8
username@javax.sql.BaseDataSource=USER
password@javax.sql.BaseDataSource=PASSWORD
validationQuery@javax.sql.BaseDataSource=show variables like 'version'
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

Oracle:

hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
vendor@org.sakaiproject.db.api.SqlService=oracle
driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
url@javax.sql.BaseDataSource=jdbc:oracle:thin:@SERVER:1521:DB
username@javax.sql.BaseDataSource=USER
password@javax.sql.BaseDataSource=PASSWORD
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

For a new installation you can have Sakai create its schema as Tomcat starts up (i.e. you don't need to create tables yourself) with the following setting in sakai.properties:

auto.ddl=true

Once the tables are created you should set this to false and restart.

5.4. Migrating from an Earlier Version

A database conversion is typically required between Sakai versions. Database conversion scripts - in distinct versions for MySQL and Oracle, respectively - are found in the reference/docs/conversion folder of the release or on subversion:

In the same directory you'll also find conversion scripts for earlier Sakai versions. Migration from an earlier version will require the successive application of all intermediate scripts. You cannot, for example, move from 2.2.1 to 2.4.0 by applying a single script. You will need to move first from 2.2.1 to 2.3.1, and then from 2.3.1 to 2.4.0.

Examine before using As a general rule, be sure to read through these conversion scripts before applying them. They do not take into account any special customizations you may have made - such as new roles, or the deployment of additional tools - and they may complicate your migration with unintended consequences if you execute them blindly.

5.5. Startup

Once you've configured the database appropriately, you need only stop and restart Tomcat. As Tomcat is coming up you can watch its log to see if there are any database connection errors (see the Troubleshooting section).

Oracle and Tests and Quizzes If you're running Oracle and using the Tests & Quizzes tool, you will likely have a schema issue stemming from Hibernate assigning the wrong data type to one field. For help diagnosing and correcting this, see the discussion on the "Verifying and Troubleshooting your Installation" page of the Sys Admin Guide.