In our test suite we are using an Embedded h2 (1.4.190) database in MYSQL mode. Quite franky for this kind of use I didn’t really care how performant this database is, so the decision to use h2 was pretty random. In the begining I have not noticed any syntax issues and was quite content with it, but as the project grew bigger and we required to execute more types of queries, things quite quirkier. h2 started to derive from MSQL(5.5.46) syntax and we spent a lot of time trying to make our tests not fail over syntax errors.
Lately I have noticed a new kind of issue with h2 : DDL queries were successfully executed on the h2 schema, but testing showed that the changes were actually not applied. On both cases this happended I was using CHANGE key.
This was our original layout:
CREATE TABLE snapshots ( id INT(11) NOT NULL AUTO_INCREMENT, json_snapshot TEXT DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
And we neededd to make the following alternation:
ALTER TABLE snapshots CHANGE id id BINARY(16) NOT NULL;
After running this migration our tests were still failing on casting exception when persisting transient entites with a BINARY PK to the schema which still had the old INT TYPE.
The solution to this was to change to this query so it uses MODIFY keyword:
ALTER TABLE snapshots MODIFY id BINARY(16) NOT NULL;
Using the modify keyword (which is also accepted by both h2 and MySQL) solved the problem, and the desired changes were applied.