Changed behavior for NOT NULL columns with default values

[Eyal Segal]

I'm trying to migrate from H2 version 1.4.196 to 1.4.200 and I encountered a strange problem.

We're using H2 for unit tests to mimic MySql. The connection string is "...;MODE=MYSQL;IGNORECASE=TRUE;IFEXISTS=TRUE;INIT=SET SCHEMA TEST"

When running the following:

CREATE TABLE test_table(column1 VARCHAR(10), update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

INSERT INTO test_table(column1, update_time) VALUES ('test', null)

I get an exception:

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "UPDATE_TIME"; SQL statement:
INSERT INTO test_table(column1, update_time) VALUES ('test', null) [23502-200]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
 at org.h2.message.DbException.get(DbException.java:205)
 at org.h2.message.DbException.get(DbException.java:181)
 at org.h2.table.Column.validateConvertUpdateSequence(Column.java:374)
 at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845)
 at org.h2.command.dml.Insert.insertRows(Insert.java:187)
 at org.h2.command.dml.Insert.update(Insert.java:151)
 at org.h2.command.CommandContainer.update(CommandContainer.java:198)
 at org.h2.command.Command.executeUpdate(Command.java:251)
 at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:168)
 at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126)
 at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
 at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)

This works well in both 1.4.196 and on MySql. Is this a regression? Is there a workaround?

Thanks!

[Evgenij Ryazanov]

Hello.

Your command is obliviously invalid. You can't insert a NULL into column with NOT NULL constraint. You need to use DEFAULT

INSERT INTO test_table(column1, update_time) VALUES ('test', DEFAULT);

or you can simply remove this column from the list of INSERT columns.

INSERT INTO test_table(column1) VALUES ('test');

MySQL historically allows different incorrect commands, but recent versions of MySQL disallow many of them by default. H2 allowed some incorrect commands in MySQL compatibility mode too, but some such support was removed to match default behavior of MySQL better and to remove unnecessary complications from H2.

It looks like MySQL does not allow NULL for NOT NULL columns any more in some cases, but still allows it in others. This difference wasn't found earlier and such support was removed completely from H2 and I don't think that it will be restored. You need to fix your command instead or continue to use some outdated version of H2.

[Eyal Segal]

Thank you for your response, disappointing a bit, as I would expect it to have at least a feature that allows that, for compatibility with MySql.

[Eyal Segal]

Went over the code, I see I can add NULL_TO_DEFAULT to the column definition it works!

[Evgenij Ryazanov]

Don't rely on that undocumented feature, it is a hidden internal part of archaic implementation of identity columns, most likely it will be removed in the future. You really should fix your command instead, it currently abuses an old bug of MySQL.

[Eyal Segal]

Thanks!