Bug #91610 5.7 or later returns an error on strict mode when del/update with error func.
Submitted: 12 Jul 7:57 Modified: 26 Jul 8:11
Reporter: Meiji Kimura Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.40 OS:Any
Assigned to: CPU Architecture:Any

[12 Jul 7:57] Meiji Kimura
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.
[13 Jul 0:47] Meiji Kimura
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 8:11] Umesh Shastry
Hello Meiji-San,

Thank you for the report and test case.

Thanks,
Umesh