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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30588 Failed to update duplicate data when running insert... on duplicate key update.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-17395 REPLACE/INSERT ODKU: support WITHOUT OVERLAPS

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31093 "ON DUPLICATE KEY UPDATE" saves wrong data to the database

          • Major - Major loss of function.
          • Open
          split to

          Task - A task that needs to be done. MDEV-34091 Refactor write_record and generalize the code with replication

          • Major - Major loss of function.
          • Stalled

          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.