I am currently working with a MySQL 5.5 and want to upgrade to 5.6 or 5.7. However I ran into strange behavior (may be mysql bug or some global default variable). Following code works fine with 5.5, but doesn't work with 5.6, 5.7. So either 5.5 has an issue or I am missing some settings with 5.6/5.7.
create table null_test( not_null VARCHAR(255) NOT NULL);
insert into null_test values();
As expected, following doesn't work.
insert into null_test values(NULL);
Based on some early digging, it might have to do with the SQL MODE, especially ER_NO_DEFAULT_FOR_FIELD.
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-full
This is a result of Strict SQL mode being enabled by default in the newer MySQL versions.
When Strict mode is disabled, omitting a value for a column that's declared NOT NULL and doesn't have an explicit DEFAULT value produces a warning and assumes an automatic default. For VARCHAR, this automatic default is an empty string.
When Strict mode is enabled, this condition causes an error unless you use INSERT IGNORE.
There's no specific SQL mode that just controls this behavior; if you want to turn off this check, you'll need to disable Strict mode entirely. Since Strict mode is likely to produce other incompatibilities (e.g. ONLY_FULL_GROUP_BY errors), this may be the most expedient way for you to handle the migration.
But if you just want to deal with this specific error, the best solution is to declare an explicit default in the schema:
create table null_test(not_null VARCHAR(255) NOT NULL DEFAULT '');