Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10170

Misleading "Statements writing to a table with an auto-increment column after selecting from another table are unsafe" on DELETE ... SELECT

Details

    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

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19577 Replication does not work with innodb_autoinc_lock_mode=2

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-27844 Set innodb_autoinc_lock_mode=2 by default, and deprecate it

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Also reproducible with MySQL 5.6, but not MySQL 5.7.

            elenst Elena Stepanova added a comment - Also reproducible with MySQL 5.6, but not MySQL 5.7.
            elenst Elena Stepanova added a comment - 

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

            elenst Elena Stepanova added a comment - --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`);
            elenst Elena Stepanova added a comment -

            Sorry, my bad. Reproducible on 5.7 too.

            elenst Elena Stepanova added a comment - Sorry, my bad. Reproducible on 5.7 too.
            valerii Valerii Kravchuk added a comment -

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

            valerii Valerii Kravchuk added a comment - So, it is NOT upstream-fixed, isn't it? Do we have upstream MySQL bug report for this?
            valerii Valerii Kravchuk added a comment -

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

            valerii Valerii Kravchuk added a comment - I've reported http://bugs.mysql.com/bug.php?id=84185 for this. MySQL 5.7.17 and 8.0.0 are still affected.
            hholzgra Hartmut Holzgraefe added a comment -

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

            hholzgra Hartmut Holzgraefe added a comment - 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);

            People

              Elkin Andrei Elkin
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.