Section - Database Installation (2.6)

Supported Databases

Sakai has been tested against and supports the following production-grade databases:

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

UTF-8 Character Set

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 Db 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, 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.

Database Drivers

Choose the appropriate MySQL or Oracle JDBC driver (or connector) for your installation.

MySQL Connector/J Driver

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 MySQL 4.1 users problems have been reported for both the MySQL Connector/J 3.1.10 and 3.1.11 drivers. Choose version 3.1.12 or higher.

Oracle OJDBC Driver

For Oracle download the ojdbc14.jar file and copy it to $CATALINA_HOME/common/lib.

Both Oracle 10g AND Oracle 9i users must use the 10g driver; the latest 10g "Release 2" (10.2.x) or higher is recommended.

Database Connection Settings

The sakai.properties configuration file defines database technology and connection information. Appropriate sample settings for HSQLDB, Oracle and MySQL are listed below.

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:

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

Set the Database Username and Password

Start by setting 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

Configure Database Settings

HSQLDB Sample Configuration

HSQLDB is Sakai's default database. Remember to comment out its settings should you choose to run Sakai utilizing either MySQL or Oracle.

## HSQLDB settings - on by default
vendor@org.sakaiproject.db.api.SqlService=hsqldb
driverClassName@javax.sql.BaseDataSource=org.hsqldb.jdbcDriver
hibernate.dialect=org.hibernate.dialect.HSQLDialect
validationQuery@javax.sql.BaseDataSource=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
# two hsqldb storage options: first for in-memory (no persistence between runs), second for disk based
#url@javax.sql.BaseDataSource=jdbc:hsqldb:mem:sakai
url@javax.sql.BaseDataSource=jdbc:hsqldb:file:${sakai.home}db/sakai.db

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=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

# To get accurate mysql query throughput statistics (for example for graphing) from the mysql command
# show status like 'Com_select'
# this alternate validation query should be used so as not to increment the query counter unnecessarily
# when validating the connection:
#validationQuery@javax.sql.BaseDataSource=show variables like 'version'

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.Oracle9Dialect
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 HSQL and 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

The auto.ddl Setting

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

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

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

Database Migration

Overview

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 in our SVN repository:

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 (see the following table). You cannot, for example, move from 2.2.1 to 2.4.0 by applying a single script. The were no database schema changes between 2.4.0 and 2.4.1, so you can migrate to the 2.5.0 schema from either version using the 2.4.0 scripts.

Upgrade Step	Conversion script prefix
1.5 to 2.0	sakai_1_5-2_0
2.0 to 2.1	sakai_2_0-2_1
2.1 to 2.1.1	sakai_2_1-2_1_1
2.1.1 to 2.1.2	sakai_2_1_1-2_1_2
2.1.2 to 2.2.0	sakai_2_1_2-2_2_0
2.2.0 to 2.2.1	sakai_2_2_0-2_2_1
2.2.1 to 2.3.2	sakai_2_2_1-2_3_1
2.3.0 to 2.3.2	sakai_2_3_0-2_3_1
2.3.1 to 2.3.2	no schema changes
2.3.2 to 2.4.1	sakai_2_3_1-2_4_0
2.4.0 to 2.4.1	no schema changes
2.4.1 to 2.5.0	sakai_2_4_0-2_5_0
2.5.0 to 2.5.1	sakai_2_5_0-2_5_1
2.5.0 to 2.5.2	sakai_2_5_0-2_5_2
2.5.1 to 2.5.2	no schema changes

The latter version represents a security release that replaced one or more intervening Sakai releases. In this case the provided conversion script contains the necessary information to migrate you through the no longer available, intervening Sakai versions.

As a general rule, be sure to read through the conversion scripts before applying them. The conversion scripts are generic in the sense that they do not take into account any special customizations you may have made - such as new roles, or the deployment of additional tools or if you are migrating from 2.4.x - and they may complicate your migration with unintended consequences if you execute them blindly.

There were no schema changes between Sakai 2.5.1 and 2.5.2. The Sakai 2.5.1 and 2.5.2 releases are both based on 2.5.0; Sakai 2.5.1 containing a major portfolio tool bug that was reverted in Sakai 2.5.2.

Other Conversion

2.5 also includes data and schema conversion outside the standard scripts for Assignments and Content Hosting to address data integrity for assignments and performance enhancements in both tools. Below is a quick summary and overview of the process for each tool.

Assignments

The assignment service has permitted the creation of duplicate submission objects (i.e. 2 or more submissions for the same student and assignment). While the UI should prevent this from happening, at various stages in the evolution of the Assignments code, bugs, race conditions or other failures have led to duplicate objects being created. This conversion seeks to reconcile those duplicates and add database constraints to prevent this is the future.

In summary, the conversion script is necessary to

1. Remove existing duplicate submission records, if any
2. Prevent future submission duplicates by applying unique constraint on the ASSIGNMENT_SUBMISSION table
3. Improve performance of the Assignment tool

The conversion script does the following to the existing ASSIGNMENT_SUBMISSION table in Sakai database:

1. read in all tuples as AssignmentSubmission object, parse out data such as submitter_id, submit_time, submitted, and graded, and stores those attributes as separate columns in the ASSIGNMENT_SUBMISSION table;
2. Runs though the table, combine and remove submission duplicates (tuples with same "context" and "submitter_id" combination);
3. apply the unique constraint of "context" + "submitter_id" to ASSIGNMENT_SUBMISSION table.

There is a readme file with detailed instructions on this process at https://source.sakaiproject.org/svn/assignment/tags/sakai_2-5-0. In addition, there is further information in the related JIRA SAK-11821.

This conversion was a part of the post-2.4 assignments branch so those migrating from a version already running this can disregard this step.

Content Hosting

Moving from 2.4 to 2.5 requires adding several columns to the database tables used by the Content Hosting Service (the service used by the Resources tool, Dropbox, filepicker and WebDAV). The conversion scripts contain DDL statements to accomplish those changes. You need to run these conversions scripts (or perform the equivalent operations manually) and then convert your existing Resources via one of the methods outlined below, before you will gain the performance improvements Sakai 2.5 offers.

The new columns added to the database tables support a switch from XML serialization to "binary-entity" serialization, and enable Resources to perform faster and use less memory. One of the key areas this impacts is improving the performance of quota calculations.

There are two methods for converting existing Resources, with the first being the recommended option, as it enables all performance improvements when completed:

1. Run the conversion utility, which can be run on a live system. (See readme for more details.)
   • Systems running oracle should read the email threads copied to the comments section below.
2. Let the code convert each Resource as it is accessed.
   • This is only recommended for implementations with small datasets, such as pilot deployments; otherwise you should use the above conversion utility.
   • While some performance benefits from the binary-entity serialization can be realized immediately using this method, others, such as the quota calculation improvements will not be available until all Resources have been accessed and converted.

Based on the state of the data in the Content Hosting Service tables, it will start up in one of two modes:

1. Binary only - If the code detects on start-up that all of the XML fields are null – as would be the case after running the conversion utility – it will run in binary mode. The means the system will only read and write using binary-entity serialization, and you will be able to fully realize all the performance enhancements that it offers.
2. Dual mode - If the code detects there is still data in the XML fields – as would be the case if the conversion utility has not be run – it will run in dual mode. This means the system will be capable of reading both XML-serialized and binary-entity-serialized resources, but will write using only binary, and will convert any XML data it encounters into binary data. This gives you some of the performance benefits of binary-entity serialization without running the conversion utility, but you will never get the quota-calculation improvements unless all Resources end up converted.

Need Further Help? For further information, please contact: Ian Boston for MySQL backends Jim Eng for Oracle backends

Discussion

The Discussion tool was retired and removed for Sakai 2.5. (Upgrade/Migration options...)