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

wrong row targeted with "insert ... on duplicate" and "replace", leading to data corruption

    XMLWordPrintable

Details

    Description

      (Also reported here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1015293)

      Using the MySQL interface, these statements:

      DROP TABLE IF EXISTS t;
      CREATE TABLE t (s BLOB, n INT, UNIQUE (s));
      INSERT INTO t VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1);
      INSERT INTO t VALUES ('Hrecvx_0004mm-00',2) ON DUPLICATE KEY UPDATE n = VALUES (n);
      SELECT * FROM t;
      

      produce this output:

      s n
      Hrecvx_0004ln-00 2
      Hrecvx_0004mm-00 1

      So the latter "INSERT" updates the wrong row.

      This happens whether the first column is "BLOB" or "TEXT", but only
      with specific values. (In my actual use case with ~1 million rows,
      it happened a few dozen times, which might be consistent e.g. with
      collisions of a 32 bit hash or so.)

      Likewise, these statements:

      DROP TABLE IF EXISTS t;
      CREATE TABLE t (s BLOB, n INT, UNIQUE (s));
      INSERT INTO t VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1);
      REPLACE INTO t VALUES ('Hrecvx_0004mm-00',2);
      SELECT * FROM t;
      

      give the error:

      ERROR 1062 (23000) at line 4: Duplicate entry 'Hrecvx_0004mm-00' for key 's'

      In my understanding, this error should actually be impossible with
      "REPLACE INTO".

      It might be the same issue, i.e. it tries to delete the wrong row
      before inserting the new one, so it's still duplicate.

      Attachments

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              fh Frank Heckenbach
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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