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

VM cold migration. Invalid configuration for device.

Last weekend I was doing a cold migration of one of our VMs and when the migration was one step before it would be done the following error popped up:

Invalid configuration for device 15.

Must admit, that wasn’t one of the helpful messages I expected to see. Anyway, quick googling led me to the following article at kb.vmware.com with more information and a workaround that finally helped me to finish the task.

Hope that would save someone’s time in the future.