MySQL Bugs: #84185: Not all "Statements writing to a table with an auto-increment..." are unsafe

Bug #84185	Not all "Statements writing to a table with an auto-increment..." are unsafe
Submitted:	13 Dec 2016 14:07	Modified:	14 Dec 2016 7:18
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6, 5.7.17, 8.0.0, 5.6.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, delete, replication, statement, unsafe, warning

Description:
Basically, MySQL (including recent 5.7.17) is affected by the same problem as described in https://jira.mariadb.org/browse/MDEV-10170 by Hartmut Holzgraefe. Warning with the text:

"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."

is produced for:

DELETE FROM `table` WHERE some_col IN (SELECT some_id FROM `other_table`)

where `table` has auto_icrement column. This warning makes no sense as auto_increment value generation does NOT happen for DELETE.

How to repeat:
I've used test case provided by Elena Stepanova in https://jira.mariadb.org/browse/MDEV-10170:

[openxs@fc23 mysql-test]$ cat suite/rpl/t/mdev10170.test
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

create table `table` (some_col int auto_increment primary key);
create table `other_table` (some_id int);

insert into `table` values (1),(2);
insert into `other_table` values (1),(2);

DELETE FROM `table` WHERE some_col IN (SELECT some_id FROM `other_table`);

Now when I run it I get the warning:

[openxs@fc23 mysql-test]$ ./mtr --suite=rpl mdev10170                           Logging: ./mtr  --suite=rpl mdev10170
2016-12-13T13:55:43.839262Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-12-13T13:55:43.839406Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
MySQL Version 5.7.17
Checking supported features...
 - SSL connections supported
Collecting tests...
 - adding combinations for rpl
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/openxs/dbs/5.7/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.mdev10170 'mix'                      [ skipped ]  Doesn't support --binlog-format='mixed'
rpl.mdev10170 'row'                      [ skipped ]  Doesn't support --binlog-format='row'
rpl.mdev10170 'stmt'                     [ fail ]
        Test ended at 2016-12-13 15:56:03

CURRENT_TEST: rpl.mdev10170
--- /home/openxs/dbs/5.7/mysql-test/suite/rpl/r/mdev10170.result        2016-12-13 16:51:35.543053596 +0300
+++ /home/openxs/dbs/5.7/mysql-test/var/log/mdev10170.reject    2016-12-13 16:56:02.776450718 +0300
@@ -0,0 +1,12 @@
+include/master-slave.inc
+Warnings:
+Note   ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
+Note   ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
+[connection master]
+create table `table` (some_col int auto_increment primary key);
+create table `other_table` (some_id int);
+insert into `table` values (1),(2);
+insert into `other_table` values (1),(2);
+DELETE FROM `table` WHERE some_col IN (SELECT some_id FROM `other_table`);
+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.

mysqltest: Result length mismatch

safe_process[25027]: Child process: 25028, exit: 1

 - the logfile can be found in '/home/openxs/dbs/5.7/mysql-test/var/log/rpl.mdev10170-stmt/mdev10170.log'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 20 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): rpl.mdev10170

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

2 tests were skipped, 0 by the test itself.

mysql-test-run: *** ERROR: there were failing test cases

In reality, this DELETE does not depend on the order of rows retrival in SELECT. The warning is at least misleading.

Suggested fix:
See https://jira.mariadb.org/browse/MDEV-10170 for some more details.

MySQL 8.0.0 is also affected:

[openxs@fc23 mysql-test]$ ./mtr --suite=rpl mdev10170                           Logging: ./mtr  --suite=rpl mdev10170
2016-12-13T14:09:57.045913Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-12-13T14:09:57.046045Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
MySQL Version 8.0.0
Checking supported features...
 - SSL connections supported
Collecting tests...
 - adding combinations for rpl
Removing old var directory...
Creating var directory '/home/openxs/dbs/8.0/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.mdev10170 'mix'                      [ skipped ]  Doesn't support --binlog-format='mixed'
rpl.mdev10170 'row'                      [ skipped ]  Doesn't support --binlog-format='row'
rpl.mdev10170 'stmt'                     [ fail ]
        Test ended at 2016-12-13 16:10:56

CURRENT_TEST: rpl.mdev10170
--- /home/openxs/dbs/8.0/mysql-test/suite/rpl/r/mdev10170.result        2016-12-13 17:09:50.596282923 +0300
+++ /home/openxs/dbs/8.0/mysql-test/var/log/mdev10170.reject    2016-12-13 17:10:56.402095701 +0300
@@ -0,0 +1,12 @@
+include/master-slave.inc
+Warnings:
+Note   ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
+Note   ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
+[connection master]
+create table `table` (some_col int auto_increment primary key);
+create table `other_table` (some_id int);
+insert into `table` values (1),(2);
+insert into `other_table` values (1),(2);
+DELETE FROM `table` WHERE some_col IN (SELECT some_id FROM `other_table`);
+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.
...

Hello Valeriy,

Thank you for the report.
Verified as described with 5.7.17 build.

Thanks,
Umesh

- 5.6.35 also affected