Re: Importing Database from MySQL Dump

[Jeff Steinkamp]

You will have to massage the dump file a little in order to get it to
import correctly into H2. All of those statements with the /* and */
will have to be manually removed.

I found it easier to create the structure in H2 first, then either do a
mysqldump of just the data and import that file, or dump the mysql
database into a csv file and use the csvread function and import that
data that way.


On 03/21/2013 04:17 AM, Peter wrote:
> Hello,
>
> I want to use H2 in-memory database for database driven integration
> tests of a struts web application (MySQL). Therefore i mysqldump my
> productive DB to a file and then try to initialize the H2 Dadabase
> with the mysql-Dump:
>
> setUpH2Database(){
> Class.forName("org.h2.Driver")
> Connection connection = DriverManager.
> getConnection("jdbc:h2:mem:YokoTrunkTest;MODE=MYSQL;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;INIT=CREATE
> SCHEMA IF NOT EXISTS YokoTrunkTest\\;RUNSCRIPT FROM '/tmp/out.sql'");
> }
>
> The Problem is that this proceeding always results in a
> org.h2.jdbc.JdbcSQLException:
>
> CREATE DATABASE[*] ""YOKOTRUNKTEST"" "; expected "OR, FORCE, VIEW,
> ALIAS, SEQUENCE, USER, TRIGGER, ROLE, SCHEMA, CONSTANT, DOMAIN, TYPE,
> DATATYPE, AGGREGATE, LINKED, MEMORY, CACHED, LOCAL, GLOBAL, TEMP,
> TEMPORARY, TABLE, PRIMARY, UNIQUE, HASH, INDEX"; SQL statement:
>
>
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `YokoTrunkTest` /*!40100
> DEFAULT CHARACTER SET latin1 */ [42001-171]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
> at org.h2.message.DbException.get(DbException.java:169)
> at org.h2.message.DbException.getSyntaxError(DbException.java:194)
> at org.h2.command.Parser.getSyntaxError(Parser.java:490)
> at org.h2.command.Parser.parseCreate(Parser.java:3844)
> at org.h2.command.Parser.parsePrepared(Parser.java:323)
> at org.h2.command.Parser.parse(Parser.java:278)
> at org.h2.command.Parser.parse(Parser.java:254)
> at org.h2.command.Parser.prepare(Parser.java:201)
> at org.h2.engine.Session.prepare(Session.java:390)
> at org.h2.engine.Session.prepare(Session.java:377)
> at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:73)
> at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:56)
> at org.h2.command.CommandContainer.update(CommandContainer.java:75)
> ....
>
> Is there any advice from you to correctly import my MySQL Database,
> what are I'm doing wrong??? I googled a lot for a solution, but can't
> find an answer. (I use com.h2database in
> version1.3.171). I would be grateful if you could help me.
>
> Best regards
> peter
> --
> 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 h2-database...@googlegroups.com.
> To post to this group, send email to h2-da...@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>


--
Jeff K. Steinkamp (N7YG)
Tucson, AZ
Scud Missile Coordinates
N32.229 W110.875

[Peter]

thank you for your quick reply and good ideas. I already suspected such syntax problems, but even without these /* ... */ statements ans without Backticks it results in the described exception.

What do you mean by "create the structure in H2". The Problem is that the Database is under heavy development. Therefore i wanted to mysqldump the current structure. Is there any other way of recreating the structure without always manually rewriting the structure creation in H2???

Here is the minimal example of the currently used sql-sciptd which results in a Syntax error in SQL statement " CREATE DATABASE[*] YOKOTRUNKTEST "; expected "OR,  ...":

CREATE DATABASE YokoTrunkTest;

USE YokoTrunkTest;

DROP TABLE IF EXISTS Address;
CREATE TABLE Address (
addressID int(10) unsigned NOT NULL,
type int(11) NOT NULL,
userID int(11) NOT NULL,
street char(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
housenumber char(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
zipcode char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
city char(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
countryID tinyint(1) unsigned NOT NULL,
label varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (addressID),
KEY INDEX_TYPE (type),
KEY INDEX_USER (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Address VALUES (1,8,1,'some street','12','12345','Berlin',2,'some company');

best regards

peter

[Ryan How]

There may be a 3rd party tool which exists which can extract schema and apply it to another database. I'm sure it would be a common problem to copy schema from one database to another one.

Or you could try and manipulate the file into a format which H2 can understand.

[Jeff Steinkamp]

The create statement for H2 would be the following:

DROP TABLE IF EXISTS Address;
CREATE TABLE Address (

addressID integer PRIMARY KEY,
type integer NOT NULL,
userID integer NOT NULL,
street varchar(50) NOT NULL,
housenumber varchar(5)  NOT NULL,
zipcode varchar(10) NOT NULL,
city varchar(50) NOT NULL,
countryID integer NOT NULL,
label varchar(255) );

CREATE INDEX INDEX_TYPE on Address(type);
CREATE INDEX INDEX_USER on Address(userID);

I've tested this on my linux machine and it works just fine.

[Peter]

Thanks Jeff for the correct Syntax. Your code also works on my machine. Now i realize that i have to convert the full database in H2 dialect (thanks Kartweel) but then i am afraid of the the fact that the mysql code of our data-access layer will be also not compatible. I hoped it would be easier with the H2 "mysql mode". 

Lastly i think this way of automated integration testing with converting differing SQL dialects will not be an feasible solution. Maybe we must use something that is independent from the SQL dialect (i.e. Hibernate -> H2) in our Project or find a way with mysql (i.e. Embedded MySql Server MXJ library).

But any other ideas for a better solution are welcome.

Thanks and best regards 

Peter