'[2.0] configure h2 in-memory database to be comptible with mysql

[sas]

I've just created an evolution file like this:

DROP TABLE IF EXISTS `mytable`;

CREATE TABLE `mytable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parentId` int(10) unsigned DEFAULT NULL,
`type`
set('BRAND','GPRODUCT','UPRODUCT','FEATURE','TOPIC','GCAMPAIGN','UCAMPAIGN')
NOT NULL,
`visible` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;

on mysql it work fine, but when running it on the h2 in memory databse
I get the following error:

We got the following error: Unknown data type: "SET"; SQL statement:
CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL
AUTO_INCREMENT, `parentId` int(10) unsigned DEFAULT NULL, `type`
set('BRAND','GPRODUCT','UPRODUCT','FEATURE','TOPIC','GCAMPAIGN','UCAMPAIGN')
NOT NULL, `visible` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY
(`id`), ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8
[50004-158] [ERROR:50004, SQLSTATE:HY004], while trying to run this
SQL script:

Seems like the "set" data type is not supported on h2, so I tried with
this in my application.conf

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play;MODE=MYSQL"

But I keep getting the same error

It's really comfortable to work with an in-memory db for development,
it's a shame we can't make it compatible with production db

do you know any way to set the mysql compatibility level?

saludos

sas

[kheraud]

This is not solving the problem of SET datatype but it helps for the Mysql specific notations.

You can use mysql c-style comments : http://dev.mysql.com/doc/refman/5.1/en/comments.html to define specific mysql configs out of the SQL statements.

For example :

create table project_tags (

id int unsigned not null auto_increment,

kind tinyint(3) not null default 0,

name varchar(200) not null,

constraint pk_project_tags primary key (id)

) ENGINE= INNODB;

won't work with H2. But :

/*! SET storage_engine=INNODB */;

create table project_tags (

id int unsigned not null auto_increment,

kind tinyint(3) not null default 0,

name varchar(200) not null,

constraint pk_project_tags primary key (id)

);

Works with H2 AND with MySQL

kheraud