Migration from embedded-db to MySQL, Oracle, MSSQL, ...

Version 3

    Warning: This guide is not complete. If you see errors here or want to contribute more information feel free to edit it.

     

    Warning: I did compile and attach hsqldbutil.jar here. Do not use it or at least use it at your own risk. `jdk/bin/upack200 hsqldbutil.jar.pack.gz hsqldbutil.jar´.

     

    MySQL to MSSQL

    http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx

    Using HSQLDB Transfer Tool

    You need to compile the GUI tool. You also need the JDBC drivers, likely hsqldb.jar and mysql.jar. Start the tool with `java -cp Path-To/hsqldbutil.jar:Path-To/hsqldb.jar:Path-To/mysql.jar org.hsqldb.util.Transfer´ (`;´ instead of `:´ on Windows) and enter then the connection details.

     

    For the embedded database (source) you may want to enter:

    Type: HSQLDB In-Memory

    Driver: org.hsqldb.jdbcDriver

    URL: jdbc:hsqldb:Path-To/embedded-db/openfire

    User: SA

    Password: (enter nothing here)

     

    For the new database (destination) you may want to enter the proper values.

    Todo: someone may want to complete the guide after migrating a database.

     

    Manual transfer

     

    This is the hard way but it should also be possible. And it is of course not tested.

     

    Prepare the `openfire.script´ file

    The embedded database is located in the `embedded-db´ directory. To migrate to another database there should be no `openfire.log´ file, so you may need to stop, start and stop Openfire to make sure that everything is in the `openfire.script´ file.

    openfire.sql.txt file

    Now make a backup of `openfire.script´ and name it `openfire.sql.txt´.

     

    Open it and search for `SET SCHEMA PUBLIC´. Remove this and all lines above.

     

    Search for `INSERT INTO JIVEUSER VALUES('admin'´ and remove this line as you did already create an admin user.

     

    Now you want to convert the \uNNNN characters to either UTF-8 or a local encoding. If you skip this step you may end up with \uNNNN characters in the new database.

     

    Append `commit;´ as the last line.

     

    Save the file.

     

    New database

    Install a new Openfire server and connect it to the new database engine. Install all plugins as they may add some tables which are needed to import all data.

    Shutdown Openfire and import the `openfire.sql.txt´ file. Some errors may occur which you need to correct manually.