A database conversion is required for a migration from 2.2.0 to 2.2.1,
but not for a migration from 2.2.1 to 2.2.2. Database conversion scripts - in distinct versions for MySQL and Oracle, respectively -
are found in the reference/docs/updating 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.1.2 to 2.2.1 by applying a single script. You will need to move first from 2.1.2 to 2.2.0, and then to 2.2.1.
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 Message Center tool offers one such particular example in 2.2. A number of schools deployed Message Center in 2.1.x production, before it was yet a provisional tool. Now that it is a provisional tool, the conversion scripts were written to introduce its tables, and thus assume that Message Center is a new tool for your deployment. If it isn't, you'll want to comment out the portions of the conversion script that treat it, and instead perform the following conversions:
| MySQL: |
ALTER TABLE MFR_TOPIC_T ADD DEFAULTASSIGNNAME VARCHAR(255); ALTER TABLE MFR_OPEN_FORUM_T ADD DEFAULTASSIGNNAME VARCHAR(255); ALTER TABLE MFR_MESSAGE_T ADD GRADEASSIGNMENTNAME VARCHAR(255); ALTER TABLE MFR_MESSAGE_T ADD GRADECOMMENT VARCHAR(255); |
| Oracle: |
ALTER TABLE MFR_TOPIC_T ADD DEFAULTASSIGNNAME VARCHAR2(255) NULL; ALTER TABLE MFR_OPEN_FORUM_T ADD DEFAULTASSIGNNAME VARCHAR2(255) NULL; ALTER TABLE MFR_MESSAGE_T ADD GRADEASSIGNMENTNAME VARCHAR2(255) NULL; ALTER TABLE MFR_MESSAGE_T ADD GRADECOMMENT VARCHAR2(255) NULL; |
The supported production-grade databases include MySQL 4.1.12+ (but MySQL 5.0 has
not yet been adequately tested for it to be recommended for production) 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 is the recommended version since it has the greatest weight of production experience behind hit, however early testing of 3.1.13 has not yet revealed any significant issues.
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 |
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).
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.