h2 does not enforce NOT NULL in MySQL compatibility mode

Question

In MySQL compatibility mode, the following SQL succeeds and returns 0:

CREATE TABLE test2 (i INTEGER NOT NULL);
INSERT INTO test2 VALUES (NULL);
SELECT * FROM test2;

It fails as expected in the default mode. It also fails with MySQL 5.5 / InnoDB. Does "MySQL compatibility" actually mean "MyISAM compatibility"?

Answer 1

This problem is due to both the way MySQL handles Null values and h2. Basically MySQL converts nulls received to empty string/0 where as in h2 there is clear distinction among all three types.

So, they(h2) have decided to convert the nulls to 0/empty string/current time stamp in MYSQL mode. But since the as per core h2 0/empty string are not nulls, so it inserts the data.

Use org.h2.engine.Mode and modify the variable related to this:

Mode mode = Mode.getInstance("MYSQL");
mode.convertInsertNullToZero = false;

This has worked for me. Thanks to this link.