Sakai 10 database support

The Sakai CLE have official support for Mysql with production-grade databases, but MariaDB is a binary drop in replacement for Mysql (source : https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/) and should work with Sakai but it is not officially supported.

Choose the appropriate MySQL or Oracle JDBC driver (or connector) for your installation. For MySQL, download the *zip/*tar.gz archive, extract its contents and copy the mysql-connector-java-<version>-bin.jar to $CATALINA_HOME/common/lib. For Oracle download the ojdbc14.jar file and copy it to $CATALINA_HOME/common/lib.

 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 sakai.properties.

In actuality, Sakai is not limited to these database choices and integration with other RDBMS systems is not difficult. In the past at least one installation used Microsoft SQL Server while requests for PostgreSQL integration are occasionally raised on the Sakai developers list. However, to date no one in the Sakai Community has stepped forward to support alternatives to either Oracle or MySQL, a prerequisite for adding additional database options to the release.

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';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

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.

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

url@javax.sql.BaseDataSource
username@javax.sql.BaseDataSource
password@javax.sql.BaseDataSource

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
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
url@javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8
validationQuery@javax.sql.BaseDataSource=
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

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
vendor@org.sakaiproject.db.api.SqlService=oracle
driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
#hibernate.dialect=org.hibernate.dialect.Oracle9iDialect
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
url@javax.sql.BaseDataSource=jdbc:oracle:thin:@your.oracle.dns:1521:SID
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

 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)
#validationQuery@javax.sql.BaseDataSource=
#defaultTransactionIsolationString@javax.sql.BaseDataSource=
#testOnBorrow@javax.sql.BaseDataSource=false

# establish auto.ddl - on by default
auto.ddl=true
#auto.ddl=false

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

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

 Users upgrading from MySQL 5.0 to MySQL 5.1 on a Mac have reported name comparison problems on startup (for more info see http://collab.sakaiproject.org/pipermail/sakai-dev/2010-June/008066.html. This issue is solved by specifying the following property in a local my.cnf configuration file:

lower_case_table_names=1

 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 sakai.properties.

 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 http://dev.mysql.com/downloads/. 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: http://dev.mysql.com/downloads/mysql/5.5.html

MySQL 5.1: http://dev.mysql.com/downloads/mysql/5.1.html

Assign a password for the root account:

mysql -u root -pmysqlpassword

MYSQL_HOME

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).

Unix/Mac:

export MYSQL_HOME=/usr/local/mysql

Windows:

set MYSQL_HOME=C:\mysql

PATH

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

Unix/Mac:

export PATH=$PATH:$CATALINA_HOME/bin:$JAVA_HOME/bin:$M2_HOME/bin:$MYSQL_HOME/bin:$PHP_HOME/bin:$SUBVERSION_HOME/bin:$USR_LOCAL/bin

Windows:

;C:\mysql\bin

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

[mysqld]
default-storage-engine=InnoDB
lower_case_table_names=1

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