Migrating from HSQLDB (formerly Hypersonic) to MySQL

Overview

This document is intended as an aid for a user to migrate the data in a HSQLDB version of the Pentaho Preconfigured Install(PCI) into a MySQL version of the PCI.  This assumes that you've had the HSQLDB version of the PCI and have created data and content that you would like to store into a MySQL version of the PCI.

General Steps

  1. Obtain the MySQL version of the PCI
  2. Replace the Pentaho-Solutions directory in the MySQL PCI with the one from the HSQLDB PCI.
  3. Use kettle to migrate the quartz and hibernate data from HSQLDB to MySQL (Sample data can also be migrated if it has been altered).

Detailed Steps

Obtain the MySQL version of the PCI. 

This can be found at http://sourceforge.net/project/showfiles.php?group_id=140317.  Make sure that the any existing Pentaho installations are shut down.  Then install and start the MySQL version of the PCI.  If the installation is successful and the PCI can be started then you can continue with the next step.

Replace the Pentaho-Solutions directory in the MySQL PCI with the one from the HSQLDB PCI.

If using the standard installation the Pentaho-solutions directory is located in the root of the Pentaho installation directory.

Use kettle to migrate the quartz and hibernate data from HSQLDB to MySQL.

If you've gotten to this point the last step is to migrate the quartz database (which holds scheduler and subscription information) and the hibernate database (that holds a myriad of state information about the Pentaho platform) to the MySQL database.

At this point you should have the MySQL PCI running with the solutions from the HSQLDB installation.

Start the HSQLDB database that was being used by the HSQLDB PCI install.  This can be accomplished by using the yourpathtopentaho/pentaho-demo/data/start_hypersonic.sh or start_hypersonic.bat.

At this point you should use Kettle to move all existing data rows from all hibernate and quartz tables to their corresponding tables in MySQL.  The operation of Kettle is beyond the scope of this page.  Please consult the Kettle documentation.

Once this is complete go to the Pentaho admin page and refresh the solution repository.  This should complete the process and all solutions, schedules and runtime information should now be available in the MySQL PCI Â