Convert MySQL script to H2

Question

I have an init script for my MySQL database but for test purposes I wan't to use a H2 database. Anyone knows how to convert the file or at least has a list of the syntax differences ? thanks.

Answer 1

There are a number of database tools that help migrating data from one to another database, for example:

• Flyway
• SQuirreL DB Copy Plugin

Answer 2

I saw this on the iConomy FAQ (http://ico.nexua.org/Main/FAQ#toc28):

How do I convert H2 to MySQL?

Since they are both SQL based all you need to do is Export the h2 sql data into a .sql file, and import it into a MySQL database using a GUI, or PHPMyAdmin, Admininer, SQLBuddy, Etc.. To do this, you can either use the h2 built in console or RazorSQL h2 GUI (Multi-platform). If you want the SQL output of the database, the full path to your minecraft.h2.db without the .h2.db part

Use the following line inside a .sh / .bat file or console inside the /lib folder where h2.jar is located:

java -cp h2*.jar org.h2.tools.Script -url jdbc:h2:path/to/minecraft -user sa -password sa

This will output a file named backup.sql and will contain the raw SQL output of the database. You may need to edit it a bit so it matches up with MySQL. :)

Answer 3

Here is a good instruction by Matthew Casperson

Exporting from MySQL to H2

Here is a short list of steps, to convert from mysql to h2:

Fix up single quotes

CREATE TABLE `user` ( `name` varchar(20) NOT NULL,
convert to
CREATE TABLE user ( name varchar(20) NOT NULL,

Fix up hex numbers
Fix up bits
Don't include ranges in keys
Remove character sets (remove CHARACTER SET ...)
Remove COLLATE settings (f.e. COLLATE utf8_unicode_ci)
Remove indexes on BLOBS, CLOBS and TEXT fields
Make all index names unique
Use the MySQL compatibility mode (jdbc:h2:~/test;MODE=MySQL)