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:
- I used Windowds XP.
- MySQL Migration tool has been EOLed but it is still available from mysql.com.
- Java 1.5 is required to run MySQL Migration tool.
- Set -Xmx to 512m or bigger, as shown below, if your openfire.script is big. Mine was 135MB and that was essential.
- Not doing so you will get the following error:
- 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:
- 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
- Copied hsqldb.jar from the server to lib/ directory of MySQL Migration tool where it keeps various jars.
- used the following connection string and the class name respectively (also shown on the screenshot):
cd "c:\Program Files\MySQL\MySQL Tools for 5.0\ .\MySQLMigrationTool.exe -Xmx 512m
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:
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
jdbc:hsqldb:file:c:\temp\embedded-db\openfire org.hsqldb.jdbc.Driver
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