Openfire. Migrating from HSQLDB to MySQL.

The other day, I had to migtate Openfire from HSQLDB to MySQL using MySQL Migration tool and below just a couple tips that could save a bit of your time if you up to the same task:

  1. I used Windowds XP.
  2. MySQL Migration tool has been EOLed but it is still available from mysql.com.
  3. Java 1.5 is required to run MySQL Migration tool.
  4. Set -Xmx to 512m or bigger, as shown below, if your openfire.script is big. Mine was 135MB and that was essential.
  5. cd "c:\Program Files\MySQL\MySQL Tools for 5.0\
    .\MySQLMigrationTool.exe -Xmx 512m
  6. Not doing so you will get the following error:
  7. Connecting to source database and retrieve schemata names.
    Initializing JDBC driver …
    Driver class Generic Jdbc
    Opening connection …
    Connection jdbc:hsqldb:c:\temp\embedded-db\openfire
    The list of schema names could not be retrieved (error: 0).
    ReverseEngineeringGeneric.getSchemata :Out of Memory
    Details:

  8. Using MySQL Migration tool is trivial but you should provide a proper connection string. If you don’t none of your tables will be migrated and what you’ll see in the end is a report similar to this one:
  9. 1. Schema Migration
    ——————-

    Number of migrated schemata: 1

    Schema Name: PUBLIC
    – Tables: 0
    – Views: 0
    – Routines: 0
    – Routine Groups: 0
    – Synonyms: 0
    – Structured Types: 0
    – Sequences: 0

  10. I stopped Openfire copied the content (there are actually just two files inside – openfire.log and openfire.script) of /opt/openfire/embedded-db to c:\temp\embedded-db on my Windows PC
  11. Copied hsqldb.jar from the server to lib/ directory of MySQL Migration tool where it keeps various jars.
  12. used the following connection string and the class name respectively (also shown on the screenshot):
  13. jdbc:hsqldb:file:c:\temp\embedded-db\openfire
    org.hsqldb.jdbc.Driver

MySQLMigrationTool

The rest is just a series of clicks on the “Next” button.
Please note that if you choose to migrate the data directly into your MySQL DB all the tables will be created with their names in UPPER case. If it’s not what you prefer instead of checking “Create Objects Online” and “Trabfer Data Online” simply select “Create Script File for Create Statements” and “Create Script File for Insert Statements” and the tool will create to files Creates.sql and Inserts.sql which you could later update to meet your preferences.
To solve that issue I came up with a dumb and bold script that fixes that which I put on my GitHub repository

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.