MySQL Bugs: #86483: sql_mode NO_ZERO_DATE not work in binlog mode

Bug #86483	sql_mode NO_ZERO_DATE not work in binlog mode
Submitted:	27 May 2017 10:03	Modified:	2 Jun 2017 9:37
Reporter:	Jie Zhou	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With log_bin=1, binlog_row_image=full, NO_ZERO_DATE sql_mode does not work when I insert a date field using default value '0000-00-00'.
Example: 
CREATE TABLE default_date(a DATE NOT NULL DEFAULT '0000-00-00');
INSERT INTO default_date VALUES();

Real result: Query OK, 1 row affected (0.00 sec)
Expect result: ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1

This problem does not occur when binlog is disabled or binlog_row_image=minimal.

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

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

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

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Suggested fix:
In table.cc, function mark_columns_per_binlog_row_image will set table->writeset when binlog is open and binlog_row_image=full.
In sql_insert.cc, function validate_default_values_of_unset_fields only validate fields not in write_set. So the check for zero date is skipped.

Hello Jie Zhou,

Thank you for the report and test case.

Thanks,
Umesh

## 5.7.18

rm -rf 86483/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/86483 -v
bin/mysqld --basedir=$PWD --datadir=$PWD/86483 --socket=/tmp/mysql_86483.sock --port=86483 --log-error=$PWD/86483/log.err --log-bin --server-id=1 --binlog_row_image=full 2>&1 &

[umshastr@hod04]/export/home/ushastry/mysql-5.7.18-linux-glibc2.5-x86_64: bin/mysql -uroot -S /tmp/mysql_86483.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> CREATE TABLE default_date(a DATE NOT NULL DEFAULT '0000-00-00');
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> SET sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> INSERT INTO default_date VALUES();
Query OK, 1 row affected (0.01 sec)

root@localhost [test]> show variables like 'binlog%';
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | ABORT_SERVER |
| binlog_format                           | ROW          |
| binlog_group_commit_sync_delay          | 0            |
| binlog_group_commit_sync_no_delay_count | 0            |
| binlog_gtid_simple_recovery             | ON           |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | FULL         |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
+-----------------------------------------+--------------+
13 rows in set (0.00 sec)

root@localhost [test]>
root@localhost [test]> set global binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> \q
Bye
[umshastr@hod04]/export/home/ushastry/mysql-5.7.18-linux-glibc2.5-x86_64: bin/mysql -uroot -S /tmp/mysql_86483.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> SET sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> INSERT INTO default_date VALUES();
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1
root@localhost [test]>
root@localhost [test]> show variables like 'binlog%';
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | ABORT_SERVER |
| binlog_format                           | ROW          |
| binlog_group_commit_sync_delay          | 0            |
| binlog_group_commit_sync_no_delay_count | 0            |
| binlog_gtid_simple_recovery             | ON           |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | MINIMAL      |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
+-----------------------------------------+--------------+
13 rows in set (0.00 sec)