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

    XMLWordPrintable

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

          Activity

            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.