MySQL Bugs: #96310: INSERT accepts invalid date from default val while turn on mysql binary log

Bug #96310	INSERT accepts invalid date from default val while turn on mysql binary log
Submitted:	24 Jul 2019 9:01	Modified:	16 Jul 4:09
Reporter:	Haixing Weng (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.23, 5.7.27, 8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	invalid date, log_bin

Description:
When variable 'log_bin' is ON (that is, mysql will generate binary log for DML query) some invalid date from default value will be accepted by mysql.

How to repeat:
We can repeat it according to a test case from mysql-test/t/insert.test, just like :

From line 444:

--echo #
--echo # Bug#11745889 Traditional: INSERT accepts invalid date from default val
--echo #

SET sql_mode='';

CREATE TABLE default_date(a DATE NOT NULL DEFAULT '0000-00-00');

INSERT INTO default_date VALUES();

SET sql_mode=default;

--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO default_date VALUES();   /*We need return error here*/

Now we set mysql's log_bin to ON (by restart mysql with --loose-skip-log-bin ),

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

And send query from test case, invalid value will be accept by mysql:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE default_date(a DATE NOT NULL DEFAULT '0000-00-00');
Query OK, 0 rows affected (0.02 sec)

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

mysql> 
mysql> SET sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

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

Suggested fix:
I have not  figure it out yet,  and I may supply patch to fix it later.

Sorry for some clerical error...

While repeat error, we need restart mysql without  --loose-skip-log-bin ...

Hello Haixing Weng,

Thank you for the report and feedback.

regards,
Umesh

Hi, I created a PR by https://github.com/mysql/mysql-server/pull/617

Please review my code. And I would greatly appreciate it if it could be helpful.

Contribution submitted via Github - BUG #96310 INSERT accepts invalid date from default val With Binlog Open 
(*) Contribution by Haixing Weng (Github beifangxiuwhx, mysql-server/pull/617#issuecomment-3083202903): "I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it."

Contribution: git_patch_2669279104.txt (text/plain), 3.61 KiB.