UPDATE/DELETE with LIMIT clause unsafe for SBR even when ORDER BY PK is present
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Sergei Glushchenko | ||
5.1 |
Won't Fix
|
Undecided
|
Unassigned | ||
5.5 |
Fix Released
|
Medium
|
Sergei Glushchenko | ||
5.6 |
Fix Released
|
Medium
|
Sergei Glushchenko |
Bug Description
An update or delete when done using a limit generates a warning of the query being unsafe although an ORDER BY pk_column clause is used.
Test case is as follows:
master [localhost] {msandbox} (test) > show create table a\G
*******
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`p` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
master [localhost] {msandbox} (test) > insert into a(id) values(null);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > insert into a(id) select null from a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > insert into a(id) select null from a;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > DELETE FROM a ORDER BY id LIMIT 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
master [localhost] {msandbox} (test) > show warnings;
+------
| Level | Code | Message |
+------
| Note | 1592 | 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)
You can see that the warning is generated even though there is an ORDER clause that orders by `id` which is a PK column.
Related branches
- Laurynas Biveinis (community): Needs Information
-
Diff: 2490 lines (+2350/-9)11 files modifiedPercona-Server/mysql-test/suite/binlog/r/binlog_stm_ps.result (+0/-2)
Percona-Server/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result (+0/-4)
Percona-Server/mysql-test/suite/binlog/r/percona_binlog_unsafe_limit.result (+1581/-0)
Percona-Server/mysql-test/suite/binlog/t/percona_binlog_unsafe_limit.test (+227/-0)
Percona-Server/sql/sql_base.cc (+466/-0)
Percona-Server/sql/sql_base.h (+4/-0)
Percona-Server/sql/sql_class.cc (+30/-1)
Percona-Server/sql/sql_class.h (+2/-0)
Percona-Server/sql/sql_lex.h (+11/-0)
Percona-Server/sql/sql_select.cc (+19/-0)
Percona-Server/sql/sql_yacc.yy (+10/-2)
- Laurynas Biveinis (community): Approve
-
Diff: 2498 lines (+2348/-8)12 files modifiedPercona-Server/mysql-test/suite/binlog/r/binlog_stm_ps.result (+0/-2)
Percona-Server/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result (+0/-4)
Percona-Server/mysql-test/suite/binlog/r/percona_binlog_unsafe_limit.result (+1579/-0)
Percona-Server/mysql-test/suite/binlog/t/percona_binlog_unsafe_limit.test (+227/-0)
Percona-Server/sql/binlog.cc (+29/-0)
Percona-Server/sql/sql_base.cc (+466/-0)
Percona-Server/sql/sql_base.h (+4/-0)
Percona-Server/sql/sql_class.cc (+1/-0)
Percona-Server/sql/sql_class.h (+2/-0)
Percona-Server/sql/sql_lex.h (+11/-0)
Percona-Server/sql/sql_optimizer.cc (+19/-0)
Percona-Server/sql/sql_yacc.yy (+10/-2)
tags: | added: upstream |
The statements above generate following series of warnings:
======= ======= ======= ======= ======= ======= ======= ======= ======= ======= ======= =
TEST RESULT TIME (ms) or COMMENT ------- ------- ------- ------- ------- ------- ------- ------- ------- ----
-------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 percona_ bug1132194 [ fail ] Found warnings/errors in server log file!
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`p` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO a (id) VALUES (NULL);
INSERT INTO a (id) SELECT NULL FROM a;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
INSERT INTO a (id) SELECT NULL FROM a;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
DELETE FROM a ORDER BY id LIMIT 1;
Warnings:
Note 1592 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.
SHOW WARNINGS;
Level Code Message
Note 1592 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.
innodb.
This bug is about the last warning (using LIMIT).