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

MariaDB issues unsafe binary log warning due to "LIMIT" even when determinstic "ORDER BY" specified

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5.34
    • 10.4(EOL)
    • None
    • Binary logging enabled

    Description

      For a table with a PRIMARY KEY:

      CREATE TABLE `t1` (
      `c1` INTEGER,
      PRIMARY KEY (`c1`)
      );

      When running an "INSERT ... SELECT" statement with a LIMIT clause, albeit while also using ORDER BY:

      CREATE TABLE `t2` LIKE `t1`;
      INSERT INTO `t2` SELECT * FROM `t1` ORDER BY `c1` LIMIT 500;

      MariaDB generates the following warning:
      Jan 9 16:58:37 localhost mysqld: 140109 16:58:37 [Warning] 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. Statement: insert into t2 select * from t1 order by c1 limit 500

      The warning also occurs when doing a similar "CREATE TABLE ... SELECT" statement:

      CREATE TABLE `t2` SELECT `c1` FROM `t1` ORDER BY `c1` LIMIT 500;

      Jan 9 16:57:25 localhost mysqld: 140109 16:57:25 [Warning] 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. Statement: CREATE TABLE `t2` SELECT `c1` FROM `t1` ORDER BY `c1` LIMIT 500

      Unless I'm mistaken, these statements are actually safe because the ORDER BY clause (in this case) provides a deterministic order.

      This may be related to MySQL bug #42415, which has sat in verified-but-unresolved state for more than a year:
      http://bugs.mysql.com/bug.php?id=42415

      Attachments

        Activity

          People

            Elkin Andrei Elkin
            hexetic Tim Gokcen
            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.