MySQL Bugs: #92658: Failed to insert a NULL value into a not-nullable column under Strict-Mode-off t

Bug #92658	Failed to insert a NULL value into a not-nullable column under Strict-Mode-off t
Submitted:	3 Oct 2018 14:55	Modified:	10 Oct 2018 13:33
Reporter:	Xiangyu Xiao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Storage Engines	Severity:	S3 (Non-critical)
Version:	8	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Per my understanding:
o SQL_Mode, STRICT_ALL_TABLES/STRICT_TRANS_TABLES are not engine specific, they will put MySQL into Strict SQL mode, and the effect will depend on the specific capabilities by the engine chosen (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict).
o When working with InnoDB tables, innodb_strict_mode system variable also take effects (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html ).
As can be seen from the following, I tried to insert a NULL value into a not-nullable column  when turning off strict mode

However, I did the same test in Windows MySQL Workbench and Linux, but got different results:
In Windows, a NULL value cannot be inserted regardless sql_mode or storage engine;
In Linux, a NULL value cannot be inserted regardless sql_mode or storage engine.

How to repeat:

In Linux: 

mysql> SHOW CREATE TABLE test;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "innodb_strict_mode";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO test VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+---------------------+
| created_at          |
+---------------------+
| 2018-09-28 01:45:40 |
| 2018-09-28 01:46:35 |
| 2018-09-28 01:47:17 |
| 2018-09-28 03:49:15 |
+---------------------+
4 rows in set (0.00 sec)

In windows, I did the same test, but a NULL cannot be inserted anyway.

Hi,

Thank you for your bug report. However, this is not a bug.

First of all, you are creating table with MEMORY SE, which means that setting of innodb_strict_mode is totally irrelevant. Next, first TIMESTAMP column in the table will always accept NULL, regardless of any settings, since it will always store the timestamp when insertion occurred.

Last, but not least, there is no difference in the behaviour between Linux and Windows versions. It depends on other factors, like the default settings, configurable settings, version and release of the MySQL, structure of the table, etc, etc ......

Thank you so much for your quick response.
Please help me understand following:

SQL_Mode, STRICT_ALL_TABLES/STRICT_TRANS_TABLES are not engine
specific, they will put MySQL into Strict SQL mode. Correct?
I checked sql_mode and it return empty. If I got this correct, the strict mode is OFF. But I cannot insert a NULL value into the not-nullable column regardless of which engine I used.

While in Linux, I can insert a NULL value no matter which engine or what value of sql_mode. I tried to set sql_mode to STRICT_ALL_TABLES, but I still can insert a NULL value. This makes sense when you are saying that a TIMESTAMP column will always store the timestamp when insertion
occurred. 
But I dont understand why I cant do so in Windows.

I am using MySQL 8 in Windows 10 and MySQL 5.6 in Ubuntu 14.
Please advise. I am so confused.
Thank you so much.

Hi,

Here are my replies.

STRICT_ALL_TABLES is not specific for a storage engine. However, storage engines that can rollback behave differently from those that can't. Read our manual about it.

STRICT_TRANS_TABLES is valid only for the transactional SE and MEMORY is NOT transactional.

If sql_mode is empty, then you should read our manual for the version / release that you are using and see what are the default values, because those change with time. And strict_mode is bye default ON.

Regarding Windows, it is obvious. Strict mode default has changed between 5.6 and 8.0.

Hi sorry I have been bothering you for this.

As you mentioned that :
1, TIMESTAMP column in the table will always accept NULL, regardless of any settings.
2, default value will be different based on the different version of MySQL if sql_mode is empty 
3, Memory is not a transactional engine

I tested the following:
1, setting sql_mode to NO_ENGINE_SUBSITUTION rather than empty to avoid default value
2, setting innodb_strict_mode to OFF
3, insert a NULL into a TIMESTAMP column under innodb

As expected, in Linux MySQL 5.6, the query ran OK with no errors or warnings. However, in Windows MySQL, it said: "The column cannot be null".

Please advise.

HI,

You are repeating the questions that I have already answered.

Sorry but I dont get it.

You said that TIMESTAMP column in the table will always accept NULL, regardless of any settings. But I cannot insert a NULL into a TIMESTAMP column if this column is created as NOT NULL. (Using innodb and strict mode is off)

You said that default value will be different based on the different version of MySQL if sql_mode is empty. So I set sql_mode to NO_ENGINE_SUBSITUTION only to avoid default value. But I cannot insert a NULL value to a NOT NULL column. (Using innodb and strict mode is off)

Please advise and help me understand this. I checked the document but did not find an explanation

The explanation is simple.

Behaviour has changed between 5.6 and 8.0.

Hence, you need to read both manuals properly.