| Bug #91610 | 5.7 or later returns an error on strict mode when del/update with error func. | ||
|---|---|---|---|
| Submitted: | 12 Jul 2018 7:57 | Modified: | 26 Jul 2018 8:11 |
| Reporter: | Meiji Kimura | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jul 2018 0:47]
MySQL Verification Team
5.5 & 5.1 behave same as 5.6.
mysql [localhost] {msandbox} (test) > SELECT @@version;
+---------------------------------------+
| @@version |
+---------------------------------------+
| 5.5.60-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SET @@session.sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp());
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp(1));
ERROR 1292 (22007): Incorrect datetime value: '1'
mysql [localhost] {msandbox} (test) > select * from tbl where b = unix_timestamp(1);
Empty set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > delete from tbl where b = unix_timestamp(1); -- Warn:5.6, Error:5.7,8.0
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > update tbl set b = unix_timestamp() where b = unix_timestamp(1);
ERROR 1292 (22007): Incorrect datetime value: '1'
mysql [localhost] {msandbox} (test) > SELECT @@version;
+----------------------------------+
| @@version |
+----------------------------------+
| 5.1.73-enterprise-commercial-pro |
+----------------------------------+
1 row in set (0.01 sec)
mysql [localhost] {msandbox} (test) > SET @@session.sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp());
Query OK, 1 row affected (0.00 sec)
mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp(1));
ERROR 1292 (22007): Incorrect datetime value: '1'
mysql [localhost] {msandbox} (test) > select * from tbl where b = unix_timestamp(1);
Empty set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > delete from tbl where b = unix_timestamp(1); -- Warn:5.6, Error:5.7,8.0
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > update tbl set b = unix_timestamp() where b = unix_timestamp(1);
ERROR 1292 (22007): Incorrect datetime value: '1'
[26 Jul 2018 8:11]
MySQL Verification Team
Hello Meiji-San, Thank you for the report and test case. Thanks, Umesh
Description: MySQL provide sql_mode, and can specify with 'Strict mode'. The behavior should be same between versions. But deleting with an error function, 5.6 is executed with warninig, 5.7 or later returns error. It cause the break of replication with SBR between 5.6 and 5.7. On 5.6 the statement is recorded, but the error will occur on 5.7. [Workaround: use RBR] How to repeat: [test case] use test; DROP TABLE if exists `tbl`; CREATE TABLE `tbl` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SELECT @@version; SET @@session.sql_mode='STRICT_TRANS_TABLES'; insert into tbl(b) values(unix_timestamp()); insert into tbl(b) values(unix_timestamp(1)); select * from tbl where b = unix_timestamp(1); delete from tbl where b = unix_timestamp(1); -- Warn:5.6, Error:5.7,8.0 update tbl set b = unix_timestamp() where b = unix_timestamp(1); -- 5.6 mysql [localhost] {msandbox} (test) > SET @@session.sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp()); Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp(1)); ERROR 1292 (22007): Incorrect datetime value: '1' mysql [localhost] {msandbox} (test) > select * from tbl where b = unix_timestamp(1); Empty set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > delete from tbl where b = unix_timestamp(1); -- Warn:5.6, Error:5.7,8.0 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > update tbl set b = unix_timestamp() where b = unix_timestamp(1); ERROR 1292 (22007): Incorrect datetime value: '1' -- 5.7, 8.0 mysql [localhost] {msandbox} (test) > SET @@session.sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp()); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into tbl(b) values(unix_timestamp(1)); ERROR 1292 (22007): Incorrect datetime value: '1' mysql [localhost] {msandbox} (test) > select * from tbl where b = unix_timestamp(1); Empty set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > delete from tbl where b = unix_timestamp(1); -- Warn:5.6, Error:5.7,8.0 ERROR 1292 (22007): Incorrect datetime value: '1' mysql [localhost] {msandbox} (test) > update tbl set b = unix_timestamp() where b = unix_timestamp(1); ERROR 1292 (22007): Incorrect datetime value: '1' Suggested fix: Their behavior should be same, or need document about this difference.