Running h2 in MODE=MySQL doesn't support MySQL dumps

Question

I'm trying to embed h2 to test my mysql-application (integration-test)

I added com.h2database:h2:1.3.170 via maven and run the following code:

public class InMemoryTest
{
    @Test
    public void test() throws Exception {
      Class.forName("org.h2.Driver");

      Connection conn = DriverManager.
          getConnection("jdbc:h2:mem:test;MODE=MySQL;IGNORECASE=TRUE;INIT=RUNSCRIPT FROM 'src/test/resources/test.sql'");
    }
}

which gives me the following Exception:

Syntax error in SQL statement "
CREATE TABLE IF NOT EXISTS ""usr_avatar"" (
  ""usr_avatar_id"" INT(11) NOT NULL AUTO_INCREMENT,
  ""usr_avatar_user_id"" INT(11) NOT NULL,
  ""usr_avatar_img"" BLOB NOT NULL,
  PRIMARY KEY (""usr_avatar_id""),
  UNIQUE KEY ""usr_avatar_id_UNIQUE"" (""usr_avatar_id""),
  UNIQUE KEY ""usr_avatar_user_id_UNIQUE"" (""usr_avatar_user_id""),
  KEY ""usr_user_id"" (""usr_avatar_user_id""),
  KEY ""fk_user_id"" (""usr_avatar_user_id"")
) AUTO_INCREMENT[*]=1  ";

Apparently, the "AUTO_INCREMENT" causes this?

Since this is valid MySQL (I exported the dump from my real database using MySQL Workbench), I'm a bit confused since h2 claims to support MySQL? Here are a few lines from the .sql:

DROP TABLE IF EXISTS `usr_avatar`;
CREATE TABLE IF NOT EXISTS "usr_avatar" (
  "usr_avatar_id" int(11) NOT NULL AUTO_INCREMENT,
  "usr_avatar_user_id" int(11) NOT NULL,
  "usr_avatar_img" blob NOT NULL,
  PRIMARY KEY ("usr_avatar_id"),
  UNIQUE KEY "usr_avatar_id_UNIQUE" ("usr_avatar_id"),
  UNIQUE KEY "usr_avatar_user_id_UNIQUE" ("usr_avatar_user_id"),
  KEY "usr_user_id" ("usr_avatar_user_id"),
  KEY "fk_user_id" ("usr_avatar_user_id")
) AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `usr_restriction`;
CREATE TABLE IF NOT EXISTS "usr_restriction" (
  "usr_restriction_id" int(11) NOT NULL AUTO_INCREMENT,
  "usr_restriction_user_id" int(11) DEFAULT NULL,
  "usr_restriction_ip" varchar(39) DEFAULT NULL,
  "usr_restriction_valid_from" date NOT NULL,
  "usr_restriction_valid_to" date DEFAULT NULL,
  PRIMARY KEY ("usr_restriction_id"),
  UNIQUE KEY "usr_restriction_id_UNIQUE" ("usr_restriction_id"),
  KEY "user_id" ("usr_restriction_user_id"),
  KEY "usr_user_id" ("usr_restriction_user_id")
) AUTO_INCREMENT=1 ;

What are my options? Should I export the dump with a different software and force it to be plain SQL? Which software could do that? Or am I doing something wrong?

score 3 · Answer 1 · answered Jan 25 '13 at 05:33

3

The problem is that H2 doesn't support AUTO_INCREMENT=1, which you have specified in the SQL statement. Try removing it. I don't think it's necessary for MySQL either.

answered Jan 25 '13 at 05:33

Thomas Mueller

48,905
14
116
132

1

Thanks Thomas for letting us have a definite answer! As I said before, obviously h2 starts with 1 by default. Maybe it's worth mentioning on your page which quotes (apparently back-tick or no quotes at all) should be used, and that it's only possibly to add one contraint at a time. Thank you very much for all the time and effort you've put into h2 - this database is definitely my choice when it comes to integration-testing in-memory ;-) – Sebastian Saip Jan 25 '13 at 16:29
Double quotes and back-ticks are not the problem: they are supported by H2. The position in the SQL statement where the error occurs is actually marked with a `[*]`: the error message as `... AUTO_INCREMENT[*]=1 ...` – Thomas Mueller Jan 26 '13 at 00:09

score 2 · Accepted Answer · answered Jan 24 '13 at 20:15

2

The source SQL exported from MySQL has double-quotes surrounding it's literals. The first DROP statement also has a "back-tick" (`). But when H2 is reporting the error, H2 is showing the literals surrounded by double-double quotes. I think this is the problem.

Try a couple of things. First, take the back-tick in the DROP statement and convert it to single quotes. If that doesn't work, convert all of the double-quotes to single-quotes. If that doesn't work, remove all of the quotes.

I think H2 is trying to create tables with the double-quotes as a part of the actual table names/column names and this is causing it to bomb.

answered Jan 24 '13 at 20:15

mightyrick

910
4
6

Thanks for the hint - backticking or removing quotes/ticks from table-names worked fine, while single/double-quotes caused problems. Nevertheless, there is still a problem with the "AUTO_INCREMENT=1;" at the end of my table. But since itt turns out that h2 always starts with value 1 anyways, this is not needed. – Sebastian Saip Jan 24 '13 at 20:43
Good, I'm glad it worked. It honestly doesn't surprise me that the AUTO_INCREMENT modifier didn't work. I don't think H2 has an exact one-for-one implementation for MySQL-specific behavior. But H2 does have the ability to ALTER SEQUENCE which you can look into if you need it. – mightyrick Jan 24 '13 at 20:51
Thanks! Talking about ALTER'ing things .. When adding constraints, it's only possible to add one at a time. So "ALTER TABLE `a` ADD CONSTRAINT ... ; ALTER TABLE `a`..." – Sebastian Saip Jan 24 '13 at 21:12

score 2 · Answer 3 · edited Jun 17 '16 at 13:48

2

H2 doesn't support AUTO_INCREMENT=1.

Use this instead:

ALTER TABLE table_name ALTER COLUMN id RESTART WITH 1;

edited Jun 17 '16 at 13:48

Jamesking56

3,683
5
30
61

answered Jun 17 '16 at 13:11

Jos

21
1

Running h2 in MODE=MySQL doesn't support MySQL dumps

3 Answers3