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

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5.34
    • Fix Version/s: 5.5
    • Component/s: None
    • Labels:
    • Environment:
      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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Show
              dbart Daniel Bartholomew added a comment - http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.181

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  hexetic Tim Gokcen
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated: