Child pages
  • Sakai 19 Database Support
Skip to end of metadata
Go to start of metadata

(minus) Irrespective of whether you utilize MySQL or Oracle, be sure you have configured your database to use the UTF-8 character set. Failure to do so will result in range of issues when attempting to use Unicode characters in Sakai. Consult your database documentation or a local DBA for instructions on how to set your database up properly.

If you are uncertain as to how your database is currently configured, you can check with a query. Here is a sample query for checking an Oracle instance:

SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';


For MySQL, the command to see what encoding your database is currently set to is "show create database sakai", assuming, of course, that your database is named "sakai". e.g.:

mysql> show create database sakai;
| Database | Create Database |
| sakai | CREATE DATABASE `sakai` /*!40100 DEFAULT CHARACTER SET utf8 */ |
1 row in set (0.00 sec)

Converting a database from one character set to another is a non-trivial operation, particularly if it is a large production database. We recommend strongly that you verify this aspect of the database creation and configuration process before deploying Sakai.

1.0 Configure the database

By default, all Sakai distributions are configured to use an in-memory version of HSQLDB. HSQLDB is provided for testing/demo purposes only and should not be run in production.

Whatever database you choose to use you will need to modify at a minimum the following connection settings in


2.0 Set the database username and password

Set your database username and password:

# DATABASE CONFIGURATION - make sure to modify details to match your particular setup

# The username and password. The defaults are for the out-of-the-box HSQLDB. Change to match your setup.

3.0 Set the database connection

MySQL Sample Configuration

Locate the MySQL configuration block, uncomment the settings and save your changes. Make sure you modify the data source, username and password settings to match your local environment. Do not forget to comment out the HSQLDB and Oracle settings.

# MySQL settings - make sure to alter as appropriate

Oracle Sample Configuration

Locate the Oracle configuration block, uncomment the settings and save your changes. Make sure you modify the data source, username and password settings to match your local environment. Do not forget to comment out the HSQLDB and MySQL settings.

# Oracle settings - make sure to alter as appropriate
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL

(warning) Oracle users may experience performance issues with certain of the SQL settings that work for MySQL. Oracle users can reduce Db load by uncommenting the following settings:

# For improved Oracle performance (from the University of Michigan)

4.0 Set auto.ddl=true

(info) On startup, Sakai will generate all database objects (tables, keys, constraints, etc.) automatically, obviating the need to run DDL scripts manually per the setting auto.ddl.

# establish auto.ddl - on by default

(warning) Once the database schema is created you should set auto.ddl=false.

5.0 Install MySQL 5.5 / 5.1

Configuring the Sakai CLE to use MySQL is an excellent option both for local development and production purposes.

(minus) Sakai requires transaction support. In the case of MySQL you must implement the InnoDB storage engine to ensure proper transaction handling.

(warning) Users upgrading from MySQL 5.0 to MySQL 5.1 on a Mac have reported name comparison problems on startup (for more info see This issue is solved by specifying the following property in a local my.cnf configuration file:


(info) You will not need to create Sakai database objects (tables, indices, etc) when setting up your database. Sakai generates its own database schema automatically during the Tomcat setup process via the autoDDL setting in

(info) Case sensitivity. In most varieties of UNIX, MySQL is case sensitive since the underlying operating system determines the case sensitivity of database and table names. This is not the case for MySQL running in Windows which is case insensitive. Previous MySQL conversion scripts contained a mixture of upper case and lower case statements which occasionally caused problems for upgraders; for 2.8 we opted for lower case for the syntax while leaving the table and field names upper case. Testing on MySQL 5.1 did not reveal any issues with this choice.

Before installing MySQL, confirm whether or not it is already installed on your system by checking the version from the command line:

mysql --version
mysql  Ver 14.14 Distrib 5.1.52, for apple-darwin10.3.0 (i386) using readline 5.1

If MySQL is not installed download MySQL 5.1/5.5 binaries or source from Linux users should install MySQL using a package or binaries if possible. Choose the standard configuration. Windows users should consider installing MySQL as a service. Remember to include MySQL's /bin directory in your PATH statement.

MySQL 5.5:

MySQL 5.1:

Assign a password for the root account:

mysql -u root -pmysqlpassword

5.1 Set MySQL environment variables


Set the MYSQL_HOME environment variable to point to the base directory of your MySQL installation. In the example below, the path points to a symbolic link file rather than the actual MySQL installation directory. You can create a symbolic link ("sym link") in Unix using the ln -s command (e.g., ln -s mysql-5.1.46-osx10.6-x86_64 mysql).


export MYSQL_HOME=/usr/local/mysql


set MYSQL_HOME=C:\mysql

Add MySQL's /bin directory to your PATH variable.





5.2 Create a my.cnf/my.ini configuration file

You can configure MySQL to read a wide variety of startup options from a file named my.cnf. Settings are scoped and can be overridden by file location:








binary install: /usr/local/mysql/data/my.cnf; source install: /usr/local/var/my.cnf




global, server-specific

Below is a minimalist MySQL 5.1 configuration file for a development laptop /etc/my.cnf:


(warning) Restart MySQL in order for the new settings to take effect.

5.3 Create a "sakai" database and user account

Unable to render {include} The included page could not be found.

  • No labels