Hello folks... this is Caio speaking from Brazil! Yes, we are in the sunny country.. and the country of smiles and cry.
Today I'm here to speak about the migration procedure from embedded-db to MySQL database.
First of all I would like to say that if you are going to use Openfire or any system that have the option to use HSQLDB, you just do it if the mass of data will not be big. In the maximum 200MB you can use HSQLDB... more than it you can have problems... not problems in your application, but problem to migrate your database if you will have to do it.
My .script today have more than 428MB... and I had the mission to migrate it to MySQL
I started to do my research and I arrived in this procedure.
After did all steps, I had the problem about the database size. As I said the file was more than 428MB. And for that, the MySQL Migration Tool couldn't load into memory... I receive the error message saying "Heap Memory". MySQL Migration tool is a old tool from MySQL (for that, it can't allocate a big mass of data into RAM)... they did it before Oracle buy MySQL, and today the official tool to migrate database is the MySql WorkBench... but the problem about this tool is there is no module for the HSQLDB... and there is no way to "insert" this module in the application as you do with the Mysql Migration Tool (.jar file).
After suffer a little more trying to make it work with my database, I arrive in another tool called RazorSQL. http://http://razorsql.com/
This is going to be the answer for our problems!
After install it (in Windows) and do the procedure (there is no document about how to use it to convert your databse, but this application is very intuitive), again I have the memory problem. If you look to task manager, this tool can't allocate the quantity of memory that is needed to read the database. After many trying and modify parameters, it didn't work! So, what did I do? Change to Linux... and... voilá!! It work out! =)
But attention, if you have some memory problem (using linux) you can modify the .sh file that compile this application modifying the Xmx and Xms parameter. Just change it to the size of memory you have in your computer, or something less and this should be work. The SH file call razorsql_himem. If you do a little research you will find a lot of information about it.
Well... that's all folks! Hope this explanation help someone someday! Fell free to contact me if I maybe I know something you don't know.