How to convert H2Database database file to MySQL d

Question

I have some data in H2Database file and I want to convert it to MySQL .sql database file. What are the methods I can follow?

Answer 1

I created a Groovy script that does the migration from h2 to mysql. From there you could do a mysqldump. It requires that the tables exists in the Mysql database. It should work for ohter DBMS with minor changes.

@Grapes(
[ 
    @Grab(group='mysql', module='mysql-connector-java', version='5.1.26'),
    @Grab(group='com.h2database', module='h2', version='1.3.166'),
    @GrabConfig(systemClassLoader = true)
])

import groovy.sql.Sql

def h2Url='jdbc:h2:C:\\Users\\xxx\\Desktop\\h2\\sonardata\\sonar'
def h2User='sonar'
def h2Passwd='sonar'

def mysqlUrl='jdbc:mysql://10.56.xxx.xxx:3306/sonar?useunicode=true&characterencoding=utf8&rewritebatchedstatements=true'
def mysqlUser='sonar'
def mysqlPasswd='xxxxxx'
def mysqlDatabase='sonar'


sql = Sql.newInstance(h2Url, h2User, h2Passwd, 'org.h2.Driver' )

def tables = [:]

sql.eachRow("select * from information_schema.columns where table_schema='PUBLIC'") {
    if(!it.TABLE_NAME.endsWith("_MY")) {
        if (tables[it.TABLE_NAME] == null) {
            tables[it.TABLE_NAME] = []
        }
        tables[it.TABLE_NAME] += it.COLUMN_NAME;
    }
}

tables.each{tab, cols ->
    println("processing $tab")
    println("droppin $tab"+"_my")

    sql.execute("DROP TABLE IF EXISTS "+tab+"_my;")
    sql.execute("create linked table "+tab+"_my ('com.mysql.jdbc.Driver', '"+mysqlUrl+"', '"+mysqlUser+"', '"+mysqlPasswd+"', '"+mysqlDatabase+"."+tab.toLowerCase()+"');")

    sql.eachRow("select count(*) as c from " + tab + "_my"){println("deleting $it.c entries from mysql table")}
    result = sql.execute("delete from "+tab+"_my")
    colString = cols.join(", ")
    sql.eachRow("select count(*) as c from " + tab){println("starting to copy $it.c entries")}
    sql.execute("insert into " + tab + "_my ("+colString+") select "+colString+" from " + tab)
}

Answer 2

The SQL script generated by the H2 database is not fully compatible with the SQL supported by MySQL. You would have to change the SQL script manually. This requires that you know both H2 and MySQL quite well.

To avoid this problem, an alternative, probably simpler way to copy the data from H2 to MySQL is to use a 3rd party tool such as the SQuirreL SQL together with the SQuirreL DB Copy Plugin plugin. (First you need to install SQuirreL SQL and on top of that the SQuirreL DB Copy Plugin.)

Answer 3

In answer to Thomas Mueller, SquirrelSQL worked fine for me. Here is the procedure for Windows to convert a H2 database:

1. Go to "drivers list", where everything is red by default.

2. Select "H2" driver, and specify the full path to "h2-1.3.173.jar" (for example) in "Extra Class Path". The H2 driver should display a blue check in the list.

3. Select your target driver (PostgreSQL, MySQL), and do the same, for example for PostgreSQL, specify the full path to "postgresql-9.4-1201.jdbc41.jar" in Extra Class Path.

4. Go to "Aliases", then click on "+" for H2 : configure your JDBC chain, for example copy/paste the jdbc chain you obtain when you launch H2, and do the same for your target database: click on "+", configure and "test".

5. When you double click on your alias, you should see everything inside your database in a new Tab. Go to the tables in source database, do a multi-select on all your tables and do a right-click : "Copy Table".

6. Go to your target database from Alias, and do a "Paste Table". When all tables are copied altogether, the foreign key references are also generated.

7. Check your primary keys : from H2 to PostgreSQL, I lost the Primary Key constraints, and the auto-increment capability. You could also rename columns and tables by a right click : "refactor". I used it to rename reserved words columns after full copy, by disabling name check in options.

   This worked well for me.

Answer 4

You can use fullconvert to convert database. it's easy to use.

Follow steps shown here

https://www.fullconvert.com/howto/h2-to-mysql

Answer 5

The H2 database allows you to create a SQL script using the SCRIPT SQL statement or the Script command line tool. Possibly you will need to tweak the script before you can run it against the MySQL database.