[MDEV-10170] Misleading "Statements writing to a table with an auto-increment column after selecting from another table are unsafe" on DELETE ... SELECT Created: 2016-06-02  Updated: 2018-10-30

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Replication
Affects Version/s: 10.1.14, 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Hartmut Holzgraefe Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

The following warning is raised with binlog_format=STATEMENT when deleting rows from an auto_increment table with a subquery:

"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. Statement: DELETE FROM table WHERE some_col IN (SELECT some_id FROM other_table)"

As far as I can tell this should only be an issue with INSERT, but not with UPDATE and especially not with DELETE, as in these cases no new auto_increment IDs will be generated. So SELECT result order should not matter unless actual row insertions happen.

The current code only seems to check for "table we write to has auto increment, and there's also another table we select from"

See https://github.com/MariaDB/server/blob/10.1/sql/sql_class.cc#L5758

 Comments   
Comment by Elena Stepanova [ 2016-06-02 ]

Also reproducible with MySQL 5.6, but not MySQL 5.7.

Comment by Elena Stepanova [ 2016-06-28 ] 

--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`);

Comment by Elena Stepanova [ 2016-06-28 ]

Sorry, my bad. Reproducible on 5.7 too.

Comment by Valerii Kravchuk [ 2016-12-04 ]

So, it is NOT upstream-fixed, isn't it? Do we have upstream MySQL bug report for this?

Comment by Valerii Kravchuk [ 2016-12-13 ]

I've reported http://bugs.mysql.com/bug.php?id=84185 for this. MySQL 5.7.17 and 8.0.0 are still affected.

Comment by Hartmut Holzgraefe [ 2017-09-19 ]

The original link above does not point to the right place anymore.

The message is raised in THD::decide_logging_format() in sql/sql_class.cc at this point:

    if (wsrep_binlog_format() != BINLOG_FORMAT_ROW)
 
    {
 
      /*
 
        DML statements that modify a table with an auto_increment
 
        column based on rows selected from a table are unsafe as the
 
        order in which the rows are fetched fron the select tables
 
        cannot be determined and may differ on master and slave.
 
      */
 
      if (has_auto_increment_write_tables && has_read_tables)
 
        lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT);

Generated at Thu Feb 08 07:40:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.