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

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

            Transition Time In Source Status Execution Times
            Alice Sherepa made transition -
            Open Confirmed
            		6h 24m 1
            Nikita Malyavin made transition -
            Confirmed In Progress
            		35d 3h 16m 1
            Nikita Malyavin made transition -
            In Progress Stalled
            		14d 16h 45m 2
            Nikita Malyavin made transition -
            Stalled In Progress
            		435d 10h 23m 2
            Nikita Malyavin made transition -
            In Progress In Testing
            		36d 22h 19m 1
            Nikita Malyavin made transition -
            In Testing Stalled
            		6s 1
            Nikita Malyavin made transition -
            Stalled In Review
            		1h 37m 1
            Sergei Golubchik made transition -
            In Review Stalled
            		5d 2h 25m 1
            Nikita Malyavin made transition -
            Stalled Closed
            		3d 20h 47m 1

            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.