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
on subversion:
| MySQL: | |
| Oracle: |
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. You cannot, for example, move from 2.2.0 to 2.3.1 by applying a single script. You will need to move first from 2.2.0 to 2.2.1, and then from 2.2.1 to 2.3.1 (there was no database conversion between 2.2.1 and 2.2.2, and while there is a conversion script from 2.3.0 to 2.3.1 for those who may have already migrated to 2.3.0, the 2.2.1-2.3.0 conversion had a significant gradebook error, and is no longer made available).
As a general rule, be sure to read through these conversion scripts before applying them. They do not take into account any special customizations you may have made - such as new roles, or the deployment of additional tools - and they may complicate your migration with unintended consequences if you execute them blindly.
The supported production-grade databases include MySQL 4.1.12+ (but MySQL 5.0 is
not yet recommended for production - see the Configuration for Performance
section below) and Oracle 9i+. The version of the JDBC driver (or connector) is also
important: for MySQL a 3.1.12+ connector should be used, while for Oracle the 10g
driver must be used, even if the database is Oracle 9i. These drivers should be
copied into your $CATALINA_HOME/common/lib directory, and they
are available from the official sites:
Database driver versions are a common source of problems. It's worth emphasizing again that the Oracle 10g driver must be used for Sakai installations running against Oracle, even when the database is Oracle 9i.
Problems have been reported for both the 3.1.10 and 3.1.11 MySQL drivers. 3.1.12+ will be adequate, but 3.1.14 is the latest recommended version.
A Sakai database and privileged user must be prepared for Sakai's use. Consult your database documentation for details, but below are sample commands for 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
When the database is created you must be sure to create it to use the UTF-8 character set, just as Tomcat was configured to use UTF-8. If you don't you may run into a range of issues when attempting to use Unicode characters in Sakai, and this goes for both MySQL and Oracle. Consult your DB documentation or a local DBA for instructions on how to do this.
If you're not certain how your database is currently configured, you can check with a query. Here is a sample query from Oracle showing the correct value:
SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; VALUE -------------------------------------------------------------------------------- AL32UTF8
Converting a database from one character set to another is non-trivial, particularly if it's a large production database, and so it's strongly recommended that you verify this aspect of your database creation before deploying Sakai.
There are settings in sakai.properties that also define the database technology
and connection information. Appropriate sakai.properties
settings for Oracle and MySQL, respectively, are listed below, and you need only
modify them with your local particulars:
| MySQL: |
hibernate.dialect=org.hibernate.dialect.MySQLDialect vendor@org.sakaiproject.db.api.SqlService=mysql driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver url@javax.sql.BaseDataSource=jdbc:mysql://SERVER:3306/DB?useUnicode=true&characterEncoding=UTF-8 username@javax.sql.BaseDataSource=USER password@javax.sql.BaseDataSource=PASSWORD validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED |
| Oracle: |
hibernate.dialect=org.hibernate.dialect.Oracle9Dialect vendor@org.sakaiproject.db.api.SqlService=oracle driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver url@javax.sql.BaseDataSource=jdbc:oracle:thin:@SERVER:1521:DB username@javax.sql.BaseDataSource=USER password@javax.sql.BaseDataSource=PASSWORD validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED |
Larger institutions have found the database to be the bottleneck when it comes to Sakai performance. Some additional DB configuration settings may be worth considering.
Oracle may have performance problems with some of the SQL settings that work
for HSQL and MySQL. Sakai installations using Oracle should strongly consider
the following settings in sakai.properties to avoid these
problems:
# For improved Oracle performance (from the University of Michigan) validationQuery@javax.sql.BaseDataSource= defaultTransactionIsolationString@javax.sql.BaseDataSource= testOnBorrow@javax.sql.BaseDataSource=false
Oracle should be set to the proper settings for the first two items (above) automatically; setting them with each use may affect performace. In addition, validating the connection on each transaction caused problems in at least one large production environment (University of Michigan).
MySQL performance can be considerably improved by caching queries (Oracle does this already, without any extra configuration). You can set this in sakai.properties by altering the connection string (i.e. this will replace the simpler value indicated above) as below, all on one line.
url@javax.sql.BaseDataSource=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8 &useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096
(all one line)
The parameter that enables use of the query cache is 'useServerPrepStmts=false', while the others (cachePrepStmts=true, prepStmtCacheSize=4096, and prepStmtCacheSqlLimit=4096) are for caching the parsing of prepared statements on the server side. Based on production experience, we'd recommend a query cache size of 64M, configurable in /etc/my.cnf like this:
[mysqld] query_cache_size = 64M
There are some other settable properties for the query cache, but there doesn't seem to be a need to change the defaults. To learn more, you can visit:
The above configuration properties work for MySQL 4.1 only. The connection parameters that allow MySQL caching on MySQL 4 cause problems with bit types on MySQL 5. We will likely have a better answer for the next Sakai release, but the issue was not resolved in time for Sakai 2.3. In the interim the basic, non-caching string must be used for MySQL 5, and since this means poorer performance, MySQL 5 is currently not recommended for production.
A Linux database server may see big disk-hanging delays (especially if the DB and the web server are on the same machine) under load. This appears to be a side effect of the default SLES9 I/O scheduler, CFQ. The Deadline scheduler, which has a maximum latency for serving requests, is therefore a better choice for database operations - although pages may still render slowly under load, performance will degrade more gracefully, avoiding hangs, and provide some feedback to users.
To switch to the Deadline scheduler, the boot parameter elevator=deadline must
be passed to the kernel that's being used. You can do so by editing the
/etc/grub.conf file and adding the parameter to the
kernel that's being used:
title Red Hat Enterprise Linux AS (2.4.21-32.0.1.ELhugemem)
root (hd0,0)
kernel /vmlinuz-2.4.21-32.0.1.ELhugemem ro root=/dev/sda2 elevator=deadline reboot=warm
initrd /initrd-2.4.21-32.0.1.ELhugemem.img
Make sure to reboot the system to activate the new scheduler. You can also see these links for more information:
Once you've configured the database appropriately, you need only stop and restart Tomcat. As Tomcat is coming up you can watch its log to see if there are any database connection errors (see the Troubleshooting section).
If you're running Oracle you should check the datatype of the MEDIA column in the SAM_MEDIA_T table. Hibernate tries to choose the right data type for a field, but has a habit of choosing the wrong one for Oracle. The correct types for each database are:
| HSQL: |
varbinary |
| MySQL: |
longblob |
| Oracle: |
blob |
If you need to change this type for your database, this will also involve finding the primary key constraint, dropping it and then recreating it. Contact your local DBA for further information on making this change. Below is some sample Oracle SQLplus output to better illustrate (SYS_C0064435 is the example constraint; replace it with yours):
SQL> alter table SAM_MEDIA_T modify MEDIA BLOB; Table altered. SQL> select constraint_name from user_constraints where table_name='SAM_MEDIA_T' and CONSTRAINT_TYPE='P'; CONSTRAINT_NAME ------------------------------ SYS_C0064435 SQL> alter table sam_media_t drop constraint SYS_C0064435; Table altered. SQL> alter table SAM_MEDIA_T add constraint SYS_C0064435 primary key (MEDIAID); Table altered. SQL> desc SAM_MEDIA_T; [table with BLOB type] SQL> select constraint_name from user_constraints where table_name='SAM_MEDIA_T' and CONSTRAINT_TYPE='P'; CONSTRAINT_NAME ------------------------------ SYS_C0064435 SQL> commit; Commit complete.