Compatibility mode with MySQL

[Sharad]

H2 database has one great feature i:e ability to run in MySQL
compatible mode by appending MODE=MYSQL to jdbc URL.

It works simply great, most of the table creating SQL scripts created
for MySQL work without modification.

I have only found some issues, is it possible to implement them ?

In MySQL inserting 0 in AUTO_INCREMENT column behaves like inserting
NULL so that automatically a new incremental integer is assigned to
identity column.

Whereas for H2 database, if you insert 0 in IDENTITY column, it is
inserted as coerced 0 value ignoring auto incremental behavior. To
get proper auto increment key either we must insert NULL or not
include that field in INSERT statement.

Can H2 be made to behave like MySQL accepting 0 as alias for NULL
only for IDENTITY column ? This should be the case only when the
database is connected in MYSQL compatibility mode. ( MODE=MYSQL ).

Is it possible to preserve the column name case accepting both upper
and lower case rather than turning all column names to lower case as
it happens currently

If this happens than it can be made to behave almost indistinguishable
from MySQL.

If Java is the middle-ware, than even a guru will have tough time to
figure out which database engine is running behind the scene in web
application H2 / MySQL

Many web developers willing to get rid of infectious nature of GPL
license terms will quickly turn to H2

Thanks
Sharad Kelkar

[Thomas Mueller]

Hi,

Thanks for your mail! I will add this to the feature request list:

"MySQL compatibility: for auto_increment columns, convert 0 to next
value (as when inserting NULL)."

However, for me it doesn't have a high priority at the moment (this
may change if more people request the feature).

My test case is:

drop table test;
create table test(id int auto_increment primary key, name varchar(255));
insert into test(name) values('hello');
insert into test(id, name) values(null, 'world');
insert into test(id, name) values(10, 'test 10');
insert into test(id, name) values(0, 'test');
select * from test;
> id name
> 1 hello
> 2 world
> 10 test 10
> 11 test

> In MySQL inserting 0 in AUTO_INCREMENT column behaves like inserting
> NULL so that automatically a new incremental integer is assigned to
> identity column.

I didn't know about that. If you have a link to the MySQL
documentation about that, could you post it?

I think you know that MySQL will also convert NULL to the next value
(as H2 does). Of course you would need to change your application
then.

> Is it possible to preserve the column name case accepting both upper
> and lower case rather than turning all column names to lower case as
> it happens currently

There is already a feature request, I will increase the priority:
"MySQL, MS SQL Server compatibility: support case sensitive (mixed
case) identifiers without quotes."

> Many web developers willing to get rid of infectious nature of GPL
> license terms will quickly turn to H2

The MySQL license is quite restrictive, and probably will get more
restrictive over time. GPL + commercial (dual licensing) is more and
more used to force companies to buy the commercial version. So GPL is
used to make others _not_ use it. I don't think this was the intention
of those who wrote the GPL...

Regards,
Thomas