Database Migration (2.5)

Added by Anthony Whyte, last edited by Peter A. Knoop on Nov 05, 2008  (view change)

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:

MySQL
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-0/docs/conversion/sakai_2_4_0-2_5_0_mysql_conversion.sql
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-1/docs/conversion/sakai_2_5_0-2_5_1_mysql_conversion.sql
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-2/docs/conversion/sakai_2_5_0-2_5_2_mysql_conversion.sql
Oracle
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-0/docs/conversion/sakai_2_4_0-2_5_0_oracle_conversion.sql
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-1/docs/conversion/sakai_2_5_0-2_5_1_oracle_conversion.sql
https://source.sakaiproject.org/svn/reference/tags/sakai_2-5-2/docs/conversion/sakai_2_5_0-2_5_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 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:

Discussion

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

Labels

migrate migrate Delete
migration migration Delete
migrating migrating Delete
update update Delete
updating updating Delete
upgrade upgrade Delete
upgrading upgrading Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (2)

 

  1. Apr 11, 2008

    Peter A. Knoop says:

    Jim Eng's email to the sakaidev list, Mon 7Apr2008 10:14 PM, "Re: sakai25x : Con...

    Jim Eng's email to the sakai-dev list, Mon 7-Apr-2008 10:14 PM, "Re: sakai-2-5-x : Content Hosting Conversion"

    Hi Thomas,

    Here's what I recommend for oracle systems right now (until the
    oracle queries get updated in trunk and 2.5.x):

    I'd recommend you do a separate build to generate the conversion
    utility, rather than building it as part of building sakai for your
    production servers. The best way to do that for oracle at this point
    is to base it on 2.4.x rather than 2.5.x. The conversion utility
    will be essentially the same. Here's how to do that:

    Check out Sakai 2.4.x. You could start with the cafe checkout of
    2.4.x, I think. That should give everything needed. Replace the
    content, db and entity with the SAK-12239 branches of those projects.

    https://source.sakaiproject.org/svn/content/branches/SAK-12239
    https://source.sakaiproject.org/svn/db/branches/SAK-12239
    https://source.sakaiproject.org/svn/entity/branches/SAK-12239

    You need to select a project.xml file for the content/content-
    conversion project. I do that by going to dev/sakai/content/content-
    conversion and making a copy of project.xml.oracle named project.xml
    in that folder. Then I go back to dev/sakai/ and run a maven-1 build
    of that entire sakai.

    You will need the oracle jdbc driver to build this project. We can't
    provide a mechanism to include that automatically because you need to
    agree to the copyright protection. So you need to get the latest
    oracle driver and put it in your local maven-1 repository. Here's the
    maven dependency:

    <dependency>
    <groupId>oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <jar>ojdbc14.jar</jar>
    <type>jar</type>
    <url>http://download.oracle.com/otn/utilities_drivers/jdbc/10203/
    ojdbc14.jar</url>
    <properties>
    <war.bundle>true</war.bundle>
    </properties>
    </dependency>

    That indicates that the jar can be downloaded from http://
    download.oracle.com/otn/utilities_drivers/jdbc/10203/ojdbc14.jar,
    which was the version we used. If you have an earlier version of the
    driver, it may not work. If there's a newer version, I'd use that
    instead. The default location for the maven-1 repo on my laptop is
    ~/.maven/repository, so I put a copy of that file at this location:

    /.maven/repository/oracle/jars/ojdbc14.jar

    If you don't do that, your maven-1 build will fail and display the
    URL from which you can get the oracle jdbc driver.

    Once you've built the war file, you can find it in dev/sakai/content/
    content-conversion/target. That war file contains everything needed
    to run the conversion utility. You will need to do a little more
    configuration before running the conversion utility.

    You could start up the conversion utility without any other
    preparation, but I recommend you first run the 2.4.x to 2.5.x
    conversion script that is in reference and then you can start up the
    2.5.x code. If you haven't run the conversion script, you can find a
    script named "init1.sql" that contains the ddl statements to add the
    required new columns and indexes.

    Here are the steps for running the conversion (using a war file built
    from the latest code from subversion, r45197 or later):

    1) Expand the war file, which creates a folder named "sakai-
    content-conversion"
    2) Navigate down to the sakai-content-conversion/WEB-INF/classes
    directory
    3) Edit the file sakai-content-conversion/WEB-INF/classes/
    runconversion.sh and provide the absolute path for the java runtime
    4) Edit the file named sakai-content-conversion/WEB-INF/classes/
    upgradeschema-step1.config and provide the connection info (i.e.
    supply values for dbURL, dbUser and dbPass)
    5) Start the conversion utility for part 1 of the conversion by
    running the shell script named runconversion.sh in no-hangup mode
    with the upgradeschema-step1.config file as a parameter and piping
    output to log files. For example, after changing directories to
    sakai-content-conversion/WEB-INF/classes/, from the shell prompt,
    issue a command with this form (where $LOGS is the url for a
    directory where you have permission to write log files):

    1. nohup ./runconversion.sh ./upgradeschema-step1.config 1>>
      $LOGS/conversion1.log 2>> $LOGS/conversion1.errors &

    6) Allow part 1 of the conversion to be completed without
    intervention, if possible. It is possible to stop the conversion by
    creating a file named "quit.txt" in the sakai-content-conversion/WEB-
    INF/classes directory. If this part of the conversion is stopped, it
    can be restarted by removing the quit.txt file from the sakai-content-
    conversion/WEB-INF/classes directory and then repeating step 5. The
    conversion will pick up where it left off.
    7) Edit the file named sakai-content-conversion/WEB-INF/classes/
    upgradeschema-step2.config and provide the connection info (i.e.
    supply values for dbURL, dbUser and dbPass)
    8) Start the conversion utility for part 2 of the conversion by
    running the shell script named runconversion.sh in no-hangup mode
    with the upgradeschema-step2.config file as a parameter and piping
    output to log files. For example, after changing directories to
    sakai-content-conversion/WEB-INF/classes/, from the shell prompt,
    issue a command with this form:

    1. nohup ./runconversion.sh ./upgradeschema-step2.config 1>>
      $LOGS/conversion2.log 2>> $LOGS/conversion2.errors &

    9) Allow part 2 of the conversion to be completed without
    intervention, if possible. It is possible to stop the conversion by
    creating a file named "quit.txt" in the sakai-content-conversion/WEB-
    INF/classes directory. If this part of the conversion is stopped, it
    can be restarted by removing the quit.txt file from the sakai-content-
    conversion/WEB-INF/classes directory and then repeating step 8. The
    conversion will pick up where it left off.

    It is not necessary to convert the CONTENT_RESOURCE_DELETE table.

    The current code in this branch is very similar to what we used at
    Michigan to do the conversion last month. The primary difference is
    that we separated out the ddl statements a little more and didn't
    allow the conversion utility to do any ddl. Letting the conversion
    utility handle some of the ddl simplifies the process but uses the
    same sql statements.

    I'm available to answer questions or provide advice.

    Jim

  2. Apr 11, 2008

    Peter A. Knoop says:

    Jim Eng's email to the sakaidev list, Mon 8Apr2008 5:21 PM, "Re: sakai25x : Cont...

    Jim Eng's email to the sakai-dev list, Mon 8-Apr-2008 5:21 PM, "Re: sakai-2-5-x : Content Hosting Conversion"

    Hi Thomas,

    I'm not sure whether you're referring to the conversion scripts or
    the conversion utility when you say "scripts", so I will try to
    answer about both.

    When I referred to "scripts" I meant the sql conversion scripts
    (from /reference/). The part of those scripts related to Content
    Hosting can be run while 2.4.x is still running. They add columns
    and indexes that will be ignored by the 2.4.x code. Then you can
    shut down 2.4.x and bring up 2.5.0. The 2.5.0 code will convert a
    few of the existing content collection records during startup (the
    top-level folders). It will also convert a limited number of other
    records as people create or revise resources and collections.

    Then there's the piece Ian wrote that you start up using the
    runconversion.sh shell script. I refer to that as the "conversion
    utility" in an ineffective attempt to distinguish it from the sql
    conversion scripts. The conversion utility can be run while the new
    version of sakai (2.5.0 or later) is running. Once the conversion
    utility finishes migrating the data, the running instances of sakai
    will automatically start using the new columns that were added.

    HTH. Please ask again if this isn't clear. Every time I answer that
    question, I see lots of room for misunderstandings.

    Jim