Percona Server with XtraDB

UPDATE/DELETE with LIMIT clause unsafe for SBR even when ORDER BY PK is present

Reported by Ovais Tariq on 2013-02-23
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.6
5.1
Undecided
Unassigned
5.5
Medium
Sergei Glushchenko
5.6
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
*************************** 1. row ***************************
       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.

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
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.percona_bug1132194 [ fail ] Found warnings/errors in server log file!

This bug is about the last warning (using LIMIT).

There is another bug reported for the assert at the same location: bug 1086269, thus marking this one as its duplicate.

Please ignore comment #2, commented on the wrong bug by mistake.

tags: added: upstream
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.