MySQL Mode: convertInsertNullToZero should be turned off by default?

[bood]

On a real mysql, when insert a null value into a not null column, it will fail. However, h2 mysql mode does allow this happen by default. AFAIK h2 converts the null into zero by default.

I wonder why convertInsertNullToZero is turned on by default? I thought it should match the mysql behavior.

I saw a similar case here, but did not get really answered:
#250

Also a working solution here:
https://portal.permeance.com.au/web/terry.mueller/home1/-/blogs/solution-for-h2-not-failing-when-inserting-null-into-not-null-column-in-mysql-compatibility-mode

[katzyn]

This compatibility code was written a long time ago when MySQL was very different from others. MySQL got a STRICT mode that disables different legacy tricks. It was enabled by default some time ago:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

There is no normal way to toggle convertInsertNullToZero in H2, only the whole MySQL compatibility mode can be enabled. And the MySQL compatibility mode is the only one user of that compatibility code.

I'm not sure what to do with it. Because default behavior of MySQL was changed in 2015 we may want to remove this flag and all related code now for better compatibility with default behavior of modern versions. But somebody may still use this trick, of course.

@grandinj
What do you think?

[grandinj]

yeah, lets tracks the behaviour of the current stable version of MySQL, we're not aiming to be perfect with these other-database modes