 | 2.1.2 Release Documentation The information below has been copied from the 2.1.2 release site, and is posted here for ongoing comment and correction. New content not present in the HTML guide on the release site appears in RED. |
5.1 If Migrating from a Previous Version
There are database schema changes between 2.1.1 and 2.1.2, and the update scripts to be applied - in distinct versions for MySQL and Oracle, respectively - are found in the docs/updating folder of the release or on subversion:
MySQL: https://source.sakaiproject.org/svn/tags/sakai_2-1-2/docs/updating/sakai_2_1_1-2_1_2_mysql_conversion.sql
Oracle: https://source.sakaiproject.org/svn/tags/sakai_2-1-2/docs/updating/sakai_2_1_1-2_1_2_oracle_conversion.sql
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 update scripts. You cannot, for example, move from 2.0.1 to 2.1.1 by applying a single DB script. You will need to move first from 2.0.1 to 2.1.0, and then to 2.1.1.
 | Examine Before Using As a general rule, be sure to read through these conversion scripts before applying them. The 2.0-2.1 script, in particular, includes notes about roles that may spare you a potential headache if you've been running 2.0 in production. |
5.2 Deploy Drivers
The supported production-grade databases include MySQL 4.1+ and Oracle 9i+. The version of the JDBC driver (or connector) is also important. For MySQL the 3.1.12 connector should be used, while for Oracle the 10g driver must be used, even if the database is Oracle 9i.
| Driver Versions 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 Oracle 9i is the database version. Problems have been reported for both the 3.1.10 and 3.1.11 MySQL drivers. 3.1.12 is the recommended version. |
You need to have the appropriate JDBC drivers for your database installed in your $CATALINA_HOME/common/lib directory, and they are available from the official sites:
Oracle: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
MySQL: http://dev.mysql.com/downloads/connector/j/3.1.html
5.3 Create Sakai Database and User
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.
 | Proposed addition Note in particular that the database needs to be configured for the UTF-8 character set, and this is true for Oracle as well. |
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
5.4 Database Properties
| Significant Change to DB configuration for 2.1 The process of setting database properties has changed significantly from previous versions of Sakai. Specifically, the placeholder.properties file in its entirety has been deprecated, and all configuration settings are now made in sakai.properties. |
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 (although some pieces will of course need to be altered appropriately for your installation):
| Oracle |
|---|
vendor@org.sakaiproject.service.framework.sql.SqlService=oracle driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver url@javax.sql.BaseDataSource=jdbc:oracle:thin:@your.oracle.dns:1521:SID username@javax.sql.BaseDataSource=[database user name] password@javax.sql.BaseDataSource=[password] hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect auto.ddl=true validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED |
| Oracle Performance Oracle may have performance problems with some of the SQL settings that work for HSQL and perhaps even for MySQL. By default, Oracle should be set to the proper settings automatically; setting it 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). 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 This unsets the first two values and overrides the settings that are in the base (kernel) sakai.properties file. |
| MySQL |
|---|
vendor@org.sakaiproject.service.framework.sql.SqlService=mysql driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver url@javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8 username@javax.sql.BaseDataSource=[database user name] password@javax.sql.BaseDataSource=[password] hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect auto.ddl=true validationQuery@javax.sql.BaseDataSource=select 1 from DUAL defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED |
| Oracle Change for Tests&Quizzes 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:
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. |
Comments (2)
Jan 30, 2006
Bill Crosbie says:
When upgrading from 2.0.x to 2.1.x be sure to open up the SQL script file and re...When upgrading from 2.0.x to 2.1.x be sure to open up the SQL script file and read through the comments pertaining to ROLEs. If you have been using the OOTB role configuration of access & maintain for both projects AND courses, there is a potential 'gotcha'.
At Rutgers our users are authenticated via LDAP for course particiaption. The provider sets the user type. We performed the upgrade and changed the user type to Student. This had the effect of locking students out from all courses taken in the previous semester.
To workaround this problem we added both access and maintain roles to !site.helper. This realm/group provides global settings for roles that can not be overridden in local realms. We gave both Roles the same set of permissions on all sites.
We then checked in the table SAKAI_REALM_RL_GR and SAKAI_REALM_RL_FN to ensure that both RoLes and been GRanted access to the sites, and that both RoLes had the FuNction of site.visit in their access permissions for both sites.
The workaround is admittedly a hack, and it was applied directly to the production databases. We will revisit this fix once we get past other migration issues relating to a change in our interal course ID schema..
Jan 30, 2006
Clay Fenlason says:
This is good. Thanks, Bill. I've put in a more generic blurb warning people to e...This is good. Thanks, Bill.
I've put in a more generic blurb warning people to examine the conversion scripts before applying them, and noted the role comments as an example without going into the details you provide. It's good to have it here as a reference, though.