Sakai 23 Database Support

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/MariaDB, 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

vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
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

MariaDB Sample Configuration

vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=org.mariadb.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MariaDB103Dialect
url@javax.sql.BaseDataSource=jdbc:mariadb://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

Oracle Sample Configuration

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

auto.ddl=true

 Once the database schema is created you should set this property to false.

Sakairequires transaction support. In the case of MySQL/MariaDB you must implement theInnoDBstorage engineto ensure proper transaction handling.

Users upgrading from on a Mac have reported name comparison problems on startup (for more info seehttp://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 willnotneed 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 theautoDDLsetting insakai.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; we opted for lower case for the syntax while leaving the table and field names upper case. 

Install the binaries for the database you choose to use.

Assign a password for the root account:

mysql -u root -pmysqlpassword

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

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

 Restart in order for the new settings to take effect.