Description:
I'm experiencing a strange case where on my build from source MySQL 8.0.16 a check constraint comparing two date columns sometimes works as expected with UPDATE query and sometimes does not. Consider the following (simplified) example:
mysql> CREATE TABLE tst (
-> id INT,
-> start_date DATE,
-> end_date DATE,
-> PRIMARY KEY (id),
-> CONSTRAINT chk_dat CHECK (start_date < end_date)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tst (id, start_date, end_date) VALUES (1, '2019-04-25', '2019-04-30');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tst (id, start_date, end_date) VALUES (2, '2019-04-30', '2019-04-25');
ERROR 3819 (HY000): Check constraint 'chk_dat' is violated.
mysql> SELECT * FROM tst;
+----+------------+------------+
| id | start_date | end_date |
+----+------------+------------+
| 1 | 2019-04-25 | 2019-04-30 |
+----+------------+------------+
1 row in set (0.00 sec)
mysql> UPDATE tst SET end_date = '2019-04-20' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE tst SET start_date = start_date, end_date = '2019-04-20' WHERE id = 1;
ERROR 3819 (HY000): Check constraint 'chk_dat' is violated.
The first UPDATE statement should also violate the CHECK constraint, but instead it's executed OK.
Details: The 8.0.16 server (on which I encounter the problem) is replicating from MySQL 5.7.26 master on which the binary log is set up as follows:
mysql> show global 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 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.00 sec)
On the replica (8.0.16) I use the following setup (queries suggested by lefred):
mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED';
+-------------------------------------------------+--------------------------------------+-----------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE |
+-------------------------------------------------+--------------------------------------+-----------------+
| autocommit | ON | DYNAMIC |
| basedir | /usr/ | COMMAND_LINE |
| binlog_expire_logs_seconds | 10080 | GLOBAL |
| connection_control_failed_connections_threshold | 3 | GLOBAL |
| connection_control_min_connection_delay | 1500 | GLOBAL |
| datadir | /var/lib/mysql/data/ | COMMAND_LINE |
| default_authentication_plugin | mysql_native_password | GLOBAL |
| foreign_key_checks | ON | DYNAMIC |
| log_error | ./slack64-curr.err | COMMAND_LINE |
| pid_file | /var/lib/mysql/data/slack64-curr.pid | COMMAND_LINE |
| plugin_dir | /usr/lib64/mysql/plugin/ | COMMAND_LINE |
| server_id | 64999 | GLOBAL |
| transaction_isolation | REPEATABLE-READ | DYNAMIC |
| validate_password_check_user_name | ON | GLOBAL |
| validate_password_dictionary_file | /usr/share/dict/words | GLOBAL |
| validate_password_policy | STRONG | GLOBAL |
+-------------------------------------------------+--------------------------------------+-----------------+
16 rows in set (0.01 sec)
mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.session_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED';
+-------------------------------------------------+--------------------------------------+-----------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE |
+-------------------------------------------------+--------------------------------------+-----------------+
| autocommit | ON | DYNAMIC |
| basedir | /usr/ | COMMAND_LINE |
| binlog_expire_logs_seconds | 10080 | GLOBAL |
| connection_control_failed_connections_threshold | 3 | GLOBAL |
| connection_control_min_connection_delay | 1500 | GLOBAL |
| datadir | /var/lib/mysql/data/ | COMMAND_LINE |
| default_authentication_plugin | mysql_native_password | GLOBAL |
| foreign_key_checks | ON | DYNAMIC |
| log_error | ./slack64-curr.err | COMMAND_LINE |
| pid_file | /var/lib/mysql/data/slack64-curr.pid | COMMAND_LINE |
| plugin_dir | /usr/lib64/mysql/plugin/ | COMMAND_LINE |
| server_id | 64999 | GLOBAL |
| transaction_isolation | REPEATABLE-READ | DYNAMIC |
| validate_password_check_user_name | ON | GLOBAL |
| validate_password_dictionary_file | /usr/share/dict/words | GLOBAL |
| validate_password_policy | STRONG | GLOBAL |
+-------------------------------------------------+--------------------------------------+-----------------+
16 rows in set (0.00 sec)
How to repeat:
See example in description. Let me know if other information is necessary.
Suggested fix:
Both update queries should fail.
Description: I'm experiencing a strange case where on my build from source MySQL 8.0.16 a check constraint comparing two date columns sometimes works as expected with UPDATE query and sometimes does not. Consider the following (simplified) example: mysql> CREATE TABLE tst ( -> id INT, -> start_date DATE, -> end_date DATE, -> PRIMARY KEY (id), -> CONSTRAINT chk_dat CHECK (start_date < end_date) -> ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tst (id, start_date, end_date) VALUES (1, '2019-04-25', '2019-04-30'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tst (id, start_date, end_date) VALUES (2, '2019-04-30', '2019-04-25'); ERROR 3819 (HY000): Check constraint 'chk_dat' is violated. mysql> SELECT * FROM tst; +----+------------+------------+ | id | start_date | end_date | +----+------------+------------+ | 1 | 2019-04-25 | 2019-04-30 | +----+------------+------------+ 1 row in set (0.00 sec) mysql> UPDATE tst SET end_date = '2019-04-20' WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE tst SET start_date = start_date, end_date = '2019-04-20' WHERE id = 1; ERROR 3819 (HY000): Check constraint 'chk_dat' is violated. The first UPDATE statement should also violate the CHECK constraint, but instead it's executed OK. Details: The 8.0.16 server (on which I encounter the problem) is replicating from MySQL 5.7.26 master on which the binary log is set up as follows: mysql> show global 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 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +--------------------------------------------+--------------+ 15 rows in set (0.00 sec) On the replica (8.0.16) I use the following setup (queries suggested by lefred): mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED'; +-------------------------------------------------+--------------------------------------+-----------------+ | VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE | +-------------------------------------------------+--------------------------------------+-----------------+ | autocommit | ON | DYNAMIC | | basedir | /usr/ | COMMAND_LINE | | binlog_expire_logs_seconds | 10080 | GLOBAL | | connection_control_failed_connections_threshold | 3 | GLOBAL | | connection_control_min_connection_delay | 1500 | GLOBAL | | datadir | /var/lib/mysql/data/ | COMMAND_LINE | | default_authentication_plugin | mysql_native_password | GLOBAL | | foreign_key_checks | ON | DYNAMIC | | log_error | ./slack64-curr.err | COMMAND_LINE | | pid_file | /var/lib/mysql/data/slack64-curr.pid | COMMAND_LINE | | plugin_dir | /usr/lib64/mysql/plugin/ | COMMAND_LINE | | server_id | 64999 | GLOBAL | | transaction_isolation | REPEATABLE-READ | DYNAMIC | | validate_password_check_user_name | ON | GLOBAL | | validate_password_dictionary_file | /usr/share/dict/words | GLOBAL | | validate_password_policy | STRONG | GLOBAL | +-------------------------------------------------+--------------------------------------+-----------------+ 16 rows in set (0.01 sec) mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.session_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED'; +-------------------------------------------------+--------------------------------------+-----------------+ | VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE | +-------------------------------------------------+--------------------------------------+-----------------+ | autocommit | ON | DYNAMIC | | basedir | /usr/ | COMMAND_LINE | | binlog_expire_logs_seconds | 10080 | GLOBAL | | connection_control_failed_connections_threshold | 3 | GLOBAL | | connection_control_min_connection_delay | 1500 | GLOBAL | | datadir | /var/lib/mysql/data/ | COMMAND_LINE | | default_authentication_plugin | mysql_native_password | GLOBAL | | foreign_key_checks | ON | DYNAMIC | | log_error | ./slack64-curr.err | COMMAND_LINE | | pid_file | /var/lib/mysql/data/slack64-curr.pid | COMMAND_LINE | | plugin_dir | /usr/lib64/mysql/plugin/ | COMMAND_LINE | | server_id | 64999 | GLOBAL | | transaction_isolation | REPEATABLE-READ | DYNAMIC | | validate_password_check_user_name | ON | GLOBAL | | validate_password_dictionary_file | /usr/share/dict/words | GLOBAL | | validate_password_policy | STRONG | GLOBAL | +-------------------------------------------------+--------------------------------------+-----------------+ 16 rows in set (0.00 sec) How to repeat: See example in description. Let me know if other information is necessary. Suggested fix: Both update queries should fail.