Description:
If I do 'UPDATE tbl SET a=1 WHERE pkcol=1 LIMIT 1', the unsafe statement for binary logging considers this unsafe even though it is still deterministic. Although the best solution is to remove the LIMIT 1 instead, it might make sense to add additional checks server side if a statement is using the table's primary.
How to repeat:
master [localhost] {msandbox} (test) > SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a` int(10) unsigned NOT NULL,
`b` int(10) unsigned NOT NULL,
`c` char(1) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`pk`),
UNIQUE KEY `a` (`a`),
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > UPDATE t SET c = 'c' WHERE pk = 1 LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
master [localhost] {msandbox} (test) > SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
1 row in set (0.00 sec)
Description: If I do 'UPDATE tbl SET a=1 WHERE pkcol=1 LIMIT 1', the unsafe statement for binary logging considers this unsafe even though it is still deterministic. Although the best solution is to remove the LIMIT 1 instead, it might make sense to add additional checks server side if a statement is using the table's primary. How to repeat: master [localhost] {msandbox} (test) > SHOW CREATE TABLE t \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `pk` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(10) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, `c` char(1) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`pk`), UNIQUE KEY `a` (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > UPDATE t SET c = 'c' WHERE pk = 1 LIMIT 1; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 master [localhost] {msandbox} (test) > SHOW WARNINGS \G *************************** 1. row *************************** Level: Note Code: 1592 Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. 1 row in set (0.00 sec)