FYI i ran into about a half-dozen issues getting a mysql dump file to pass successfully into h2, even in MODE=MySQL. They are as follows:
1) mysql allows inserting either 0 or NULL into an auto_increment column to mean that a proper id should be generated. In H2, only NULL is given that treatment. 2) H2 barfs on locking statements (better would be to just ignore them), such as: LOCK TABLES `steps` WRITE; UNLOCK TABLES; 3) H2 barfs on the extra table properties in create table such as: CREATE TABLE `stories` ( `uid` int(11) NOT NULL auto_increment, `pcode` char(100) NOT NULL, `sname` char(100) NOT NULL, `sdesc` text NOT NULL, `racfid` char(100) NOT NULL, `insuite` int(11) NOT NULL, `metadataerror` int(11) NOT NULL, PRIMARY KEY (`uid`), KEY `pcode` (`pcode`), KEY `sname` (`sname`), KEY `metadataerror` (`metadataerror`) ) ENGINE=MyISAM AUTO_INCREMENT=2623 DEFAULT CHARSET=latin1; I automatically edited out everything between last closing paren and last semi. 4) In the mysql KEY (aka INDEX) declaration within CREATE TABLE, mysql apparently scopes the index names by table, while H2 treats the name as global, resulting in a duplicate key name error in H2 if the above create table is followed by: CREATE TABLE `cpworkflowstatuses` ( `uid` int(11) NOT NULL auto_increment, `pcode` char(100) NOT NULL, `wcode` char(100) NOT NULL, `wstatus` char(100) NOT NULL, PRIMARY KEY (`uid`), KEY `pcode` (`pcode`), KEY `wcode` (`wcode`) ) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=latin1; 5) mysql supports backslash escaping of single quote within strings; H2 just does the sql standard of double'd single quotes. INSERT INTO `steps` VALUES (76766,1965,22,'NARR','PATIENT\'s gets added.') ; -> expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement: -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
