H2 database script cannot be imported into MySQL

Question

I have created a local H2 database which I always opened in MySQL mode to insert data. Now, I want to export it with SCRIPT, in order to import it with phpMyAdmin on a remote MySQL database on a remote server. I get the following:

SET LOCK_MODE 3;
;             
CREATE USER IF NOT EXISTS SA SALT '...' HASH '...' ADMIN;           
CREATE CACHED TABLE PUBLIC.RAWVALUEITEM(
    LANGUAGE VARCHAR(2) NOT NULL SELECTIVITY 1,
    RAWVALUE VARCHAR(40) NOT NULL SELECTIVITY 99,
    STRIPPED VARCHAR(40) NOT NULL SELECTIVITY 96
);
...

Unfortunately, phpMyAdmin import is not happy:

#1193 - Unknown system variable 'LOCK_MODE'

When I manually remove the set instruction, I get further errors:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS SA SALT '...' HASH '...' at line 1

The code I use to export the database as a script is:

public static final String DB_DIR_LOCATION = "E:/Temp/FWDB/";
public static final String H2_CONNECTION = "jdbc:h2:file:"
    + DB_DIR_LOCATION + "FWDB_PHP_TEST" + "Mode=MySQL;IFEXISTS=TRUE";

public static void main(String[] args) throws SQLException {

    Connection conn = DriverManager.
        getConnection(H2_CONNECTION, "sa", "");

    PreparedStatement ps;

    ps = conn.prepareStatement("SCRIPT TO 'E:/Temp/FWDB/FWDB_EXPORT.gz' " 
        + "COMPRESSION GZIP");
    ps.execute();

}

How can generate a script from my DB which will be imported successfully by phpMyAdmin on my remote server?

Thomas Mueller · Accepted Answer · 2012-03-07 19:22:32Z

1

The SQL script generated by the SCRIPT TO command is not cross-platform. A better solution might be to use a database tool such as the SQuirreL DB Copy Plugin or another database tool.

answered Mar 7, 2012 at 19:22

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

H2 database script cannot be imported into MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related