Sakai 1.5.1

Database

Sakai

Sakai Installation

Sakai Download

Sakai Documentation

Developing with Sakai

Description

The QuickDemo configuration of Sakai uses the HSQLDB database persisted in /usr/local/sakai/db/. At startup, these are read to re-populate the information in memory. This is a convenient solution for the QuickDemo and for development, but not suitable for production.

For a Sakai server in production, you should use an external database, such as Oracle or MySQL 4.1+. This release supports both. Note that Sakai requires MySQL 4.1 or better, and does not support MySQL 4.0 at this time. Support for other databases may be available in later releases.

Sakai Database Configuration

Sakai Database Configuration
1. Drivers

You need to have appropriate JDBC drivers for your database installed in your Tomcat's /common/lib. For Oracle and MySQL, drivers are available here:

Although Sakai does support the latest version of the Oracle 9i database, Sakai requires the Oracle 10g JDBC drivers. The Oracle 9i JDBC drivers are not sufficient for Sakai.

2. Prepare the Database

There are scripts in the Sakai reference module

  • $SAKAI_DEV/reference/src/sql/legacy/oracle/*.sql
  • $SAKAI_DEV/reference/src/sql/legacy/mysql/*.sql

The file all.sql lists the scripts you need to run - the path may not be correct for actually running this as a script.

Run these against the user you setup for Sakai's use on your database.

For example, here is a transcript of creating a MySQL user, database, and tables for Sakai:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\> cd C:\sakai\reference\src\sql\legacy\mysql
C:\sakai\reference\sql\legacy\mysql\> mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51 to server version: 4.1.5-gamma-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sakai default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on sakai.* to sakaiuser@'localhost' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on sakai.* to sakaiuser@'127.0.0.1' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

The file all.sql contains the Sakai table definitions:

C:\sakai\reference\src\sql\legacy\mysql\> mysql --user=sakaiuser --password=sakaipassword sakai < all.sql
Field Type Null Key Default Extra
CHANNEL_ID varchar(99) PRI
NEXT_ID int(11) YES NULL
.
.
.
.
CREATEDON datetime 0000-00-00 00:00:00
MODIFIEDON timestamp YES CURRENT_TIMESTAMP

C:\sakai\reference\src\sql\legacy\mysql\>

3. Configure Sakai's Database Connection Information

The /usr/local/sakai/sakai.properties (or cluster.properties) file needs to have the proper database connection information.

An example, for MySQL:

sql.vendor=mysql
sql.driver=com.mysql.jdbc.Driver
sql.connect=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8
sql.user=sakaiuser
sql.pw=sakaipassword
sql.maxConnection=50
sql.maxSlowConnections=25

An example, for Oracle:

sql.vendor=oracle
sql.driver=oracle.jdbc.driver.OracleDriver
sql.connect=jdbc:oracle:thin:@monster.oracle.org:1521:db
sql.user=sakaiuser
sql.pw=sakaipassword
sql.maxConnection=50
sql.maxSlowConnections=25

Set the appropriate driver, connection string, user and password. The maxConnections is the pool size for the connection pool. The maxSlowConnections defines a second pool used for "slow" operations, such as streaming download.
4. Configure Sakai's Service Components

To use the database, you need a set of service component implementations that use the database. Sakai ships with a set, and configurations for selecting them in the components_db.xml files. The easiest way to enable these is to run the maven command

  • maven conf_db

from your $SAKAI_DEV folder. This moves the database configuration files from the source tree into your /usr/local/sakai/*. Any changes you have made to the components.xml files in /usr/local/sakai/* will be overwritten.

5. Database Tips

Sakai has clustering capability. Multiple Sakai application servers can be run, all using the same back end database. Be sure to set the server.id setting in sakai.properties to a unique value for each server in your cluster.

You may want to set the default character set of your MySQL server to be UTF-8.

You may want to set the MySQL timezone to GMT, although this is not strictly necessary.

Large file uploads cause large database queries. You may need to increase the maximum query size your database allows. In MySQL this can be accomplished by increasing the max_allowed_packet property in the the configuration file my.cnf:

[mysqld]
max_allowed_packet=31M

Sakai uses a database connection pool to access the database. It's size can be configured (see above). If you have too small a pool, Sakai will slow down somewhat, as various components that need to access the database wait in line for a connection.

If you need to make back end database corrections to the data, by modifying the tables directly, not through Sakai, the Sakai servers in the cluster need to be informed of this. The admin site has a memory tool, and a command to Reset All Caches. This reset command is shared among all application servers in the cluster. When you make a change to the database, reset the caches so that Sakai can become aware of the change.

Prior versions of CHEF and Sakai used the database engine's record locking to implement our exclusive object locking policy. This tended to eat up database connections and, if overloaded, lead to massive gridlock and unresponsive Sakai servers. This version of Sakai uses a locking table in the database to accomplish exclusive locking, and no longer has this scaling problem.