Sakai 23 Database Support

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

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.

1.0 Configure the database

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

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.
username@javax.sql.BaseDataSource=yourDbUserName
password@javax.sql.BaseDataSource=yourDbPassword

3.0 Set the database connection

Locate the database 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 default settings.

MySQL Sample Configuration

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

4.0 Database table creation

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

auto.ddl=true

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

5.0 Install MySQL 8 / MariaDB 10

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

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.

MySQL (8 is the recommended version)
MariaDB (10 is the recommended version)

Assign a password for the root account:

mysql -u root -pmysqlpassword

5.1 Configure `my.cnf/my.ini` configuration file

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

Scope	Location	Override
global	`/etc/my.cnf`
server-specific	binary install: `/usr/local/mysql/data/my.cnf`; source install: `/usr/local/var/my.cnf`	global
user	`~/my.cnf`	global, server-specific

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.

5.3 Create a "sakai" database and user account

Create the db by connecting to your db and running one of the following, depending on your db the default character set may be utf8mb3 or utf8mb4.

create database <db-name> default character set 'utf8';

This becomes important if you want your database to store high order range of utf8 which is usually emoji. If using utf8mb4 you will want to set the following property to false.

content.cleaner.filter.utf8=false

Next you will want to create a user that can be used to access the db

create user 'sakai'@'localhost' identified by 'password';

Give the user full access to the db

grant all on <db-name>.* to 'sakai'@'localhost';