23

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.

user1502150
  • 357
  • 1
  • 4
  • 11

4 Answers4

12

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)

Vitali Heinrich
  • 151
  • 1
  • 6
  • 2
    Try to include the main idea of the article in your answer. I may be of use if the link goes down. – Alex Tartan May 27 '15 at 07:10
  • Its hard to do that, because Matthew discibes it very short and clearly, but i list all important steps and hope he is all right with that. – Vitali Heinrich Jun 05 '15 at 06:19
10

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

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • 4
    I had some fun creating the bash script that will convert a mysqldump file to h2 format. https://github.com/Joebh/MysqlDumpToH2 – Joseph Helfert Jan 15 '15 at 15:30
  • 1
    He is not tying to migrate data, he is just looking for syntax differences between the file – sayil aguirre Jan 10 '21 at 12:33
  • @sayilaguirre why do you think so? The question is clearly about converting. Converting the table definitions, and I assume also some of the data. Those tools can do both. – Thomas Mueller Jan 11 '21 at 07:19
  • @sayilaguirre if _you_ are looking for a way to look at syntax differences, then please ask a new question. – Thomas Mueller Jan 11 '21 at 07:45
0

Use this java tool, It so easy to use just download and run the jar file. It has both a GUI and can also be intergrated within your code if need be. MysqlDumpToH2

katwekibs
  • 1,342
  • 14
  • 17
-3

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. :)

Dina Kaiser
  • 476
  • 3
  • 6