H2 and MySQL compatibility issues

Question

i am using H2 for my integration tests with Liquibase and it seems that there are compatibility issues.

A simple change column function does not pass on H2 where it works perfectly on MySQL distribution.

here is the query:

ALTER TABLE `designs`
ALTER COLUMN `description` `description` TEXT NULL DEFAULT NULL AFTER `created`;

And the error:

2014-06-07 14:27:05,708 [DEBUG] [NewPooledConnection,handleThrowable(),430] - com.mchange.v2.c3p0.impl.NewPooledConnection@78af2ac3 handling a throwable.
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE DESIGNS 
ALTER COLUMN DESCRIPTION TEXT NULL DEFAULT NULL AFTER[*] CREATED "; SQL statement:
ALTER TABLE designs ALTER COLUMN description TEXT NULL DEFAULT NULL AFTER created [42000-178]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

Some configuration:

repository.connectionString = jdbc:h2:mem:db;MODE=MySQL;DB_CLOSE_ON_EXIT=FALSE
repository.driver = org.h2.Driver
repository.username = sa
repository.password = 

Any ideas how to tackle this? as far as i understand H2 should be compatible with Mysql

Answer 1

As documented, H2 is (up to a certain point) compatible to other databases such as HSQLDB, MySQL and PostgreSQL. But there are certain areas where H2 is incompatible.

See also the SQL syntax supported by H2.