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

"ON DUPLICATE KEY UPDATE" saves wrong data to the database

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.12
    • None
    • None
    • None
    • 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

    Description

      Hello:

      While developing a new version of our software, I found some issues during tests and couldn't figure out why.

      I think this data produce a COLLISION when using the HASH engine for our INDEX.

      I can reproduce this bug with the next code:

      SET @c1=200193;
      		 
      SET @c2='B2B';
      		 
       
      		 
      DROP TABLE IF EXISTS `test`;
      		 
       
      		 
      CREATE TABLE `test` (
      		 
          `intA` int(11) NOT NULL,
      		 
          `string` varchar(20) NOT NULL,
      		 
          `intB` int(11) NOT NULL,
      		 
          `intC` int(11) NOT NULL,
      		 
          `intD` int(11) NOT NULL,
      		 
          UNIQUE KEY `key` (`intA`,`string`,`intB`,`intC`) USING HASH
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
       
      		 
      INSERT INTO `test` (`intA`, `string`, `intB`, `intC`, `intD`) VALUES
      		 
      (@c1, @c2, 3, 2, 1234),
      		 
      (@c1, @c2, 4, 0, 99999999);
      		 
       
      		 
      SELECT * FROM test;
      		 
       
      		 
      INSERT INTO test (intA,string,intB,intC,intD) VALUES (@c1, @c2, 4, 0, 999) ON DUPLICATE  KEY UPDATE intD = VALUES(intD);
      		 
       
      		 
      SELECT * FROM test;
      		 
       
      		 
      DROP TABLE test;

      The result we get after executing this is:

      intA    string  intB    intC    intD
      		 
      200193  B2B     3       2       1234
      		 
      200193  B2B     4       0       99999999
      		 
      intA    string  intB    intC    intD
      		 
      200193  B2B     3       2       999
      		 
      200193  B2B     4       0       99999999

      I think the expected behaviour from the database should be:

      intA    string  intB    intC    intD
      		 
      200193  B2B     3       2       1234
      		 
      200193  B2B     4       0       99999999
      		 
      intA    string  intB    intC    intD
      		 
      200193  B2B     3       2       1234
      		 
      200193  B2B     4       0       999

      Thank you

      Attachments

        Issue Links

          is caused by

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

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30046 wrong row targeted with "insert ... on duplicate" and "replace", leading to data corruption

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            Before MDEV-371, the HASH index type for InnoDB was silently interpreted as BTREE.

            marko Marko Mäkelä added a comment - Before MDEV-371 , the HASH index type for InnoDB was silently interpreted as BTREE .
            juanmitaboada Juanmi Taboada added a comment -

            I don't have this problem when using BTREE. I switched to BTREE to avoid this bug until resolved.

            juanmitaboada Juanmi Taboada added a comment - I don't have this problem when using BTREE. I switched to BTREE to avoid this bug until resolved.
            alice Alice Sherepa added a comment -

            Could you please add you .cnf file? I tried to repeat on 10.6.12 and it worked as expected:

            MariaDB [test]> CREATE TABLE `test` (
            		 
                ->     `intA` int(11) NOT NULL,
            		 
                ->     `string` varchar(20) NOT NULL,
            		 
                ->     `intB` int(11) NOT NULL,
            		 
                ->     `intC` int(11) NOT NULL,
            		 
                ->     `intD` int(11) NOT NULL,
            		 
                ->     UNIQUE KEY `key` (`intA`,`string`,`intB`,`intC`) USING HASH
            		 
                -> ) ENGINE=InnoDB;
            		 
            Query OK, 0 rows affected (0,039 sec)
            		 
             
            MariaDB [test]> INSERT INTO `test` (`intA`, `string`, `intB`, `intC`, `intD`) VALUES
            		 
                -> (@c1, @c2, 3, 2, 1234),
            		 
                -> (@c1, @c2, 4, 0, 99999999);
            		 
            Query OK, 2 rows affected (0,002 sec)
            		 
            Records: 2  Duplicates: 0  Warnings: 0
            		 
             
            		 
            MariaDB [test]> SELECT * FROM test;
            		 
            +--------+--------+------+------+----------+
            		 
            | intA   | string | intB | intC | intD     |
            		 
            +--------+--------+------+------+----------+
            		 
            | 200193 | B2B    |    3 |    2 |     1234 |
            		 
            | 200193 | B2B    |    4 |    0 | 99999999 |
            		 
            +--------+--------+------+------+----------+
            		 
            2 rows in set (0,000 sec)
            		 
             
            		 
            MariaDB [test]> INSERT INTO test (intA,string,intB,intC,intD) VALUES (@c1, @c2, 4, 0, 999) ON DUPLICATE  KEY UPDATE intD = VALUES(intD);
            		 
            Query OK, 2 rows affected (0,002 sec)
            		 
             
            MariaDB [test]> SELECT * FROM test;
            		 
            +--------+--------+------+------+------+
            		 
            | intA   | string | intB | intC | intD |
            		 
            +--------+--------+------+------+------+
            		 
            | 200193 | B2B    |    3 |    2 | 1234 |
            		 
            | 200193 | B2B    |    4 |    0 |  999 |
            		 
            +--------+--------+------+------+------+
            		 
            2 rows in set (0,000 sec)
            		 
             
            		 
            MariaDB [test]> select version();
            		 
            +-----------------+
            		 
            | version()       |
            		 
            +-----------------+
            		 
            | 10.6.12-MariaDB |
            		 
            +-----------------+
            		 
            1 row in set (0,000 sec)

            alice Alice Sherepa added a comment - Could you please add you .cnf file? I tried to repeat on 10.6.12 and it worked as expected: MariaDB [test]> CREATE TABLE `test` ( -> `intA` int(11) NOT NULL, -> `string` varchar(20) NOT NULL, -> `intB` int(11) NOT NULL, -> `intC` int(11) NOT NULL, -> `intD` int(11) NOT NULL, -> UNIQUE KEY `key` (`intA`,`string`,`intB`,`intC`) USING HASH -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0,039 sec) MariaDB [test]> INSERT INTO `test` (`intA`, `string`, `intB`, `intC`, `intD`) VALUES -> (@c1, @c2, 3, 2, 1234), -> (@c1, @c2, 4, 0, 99999999); Query OK, 2 rows affected (0,002 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT * FROM test; +--------+--------+------+------+----------+ | intA | string | intB | intC | intD | +--------+--------+------+------+----------+ | 200193 | B2B | 3 | 2 | 1234 | | 200193 | B2B | 4 | 0 | 99999999 | +--------+--------+------+------+----------+ 2 rows in set (0,000 sec)   MariaDB [test]> INSERT INTO test (intA,string,intB,intC,intD) VALUES (@c1, @c2, 4, 0, 999) ON DUPLICATE KEY UPDATE intD = VALUES(intD); Query OK, 2 rows affected (0,002 sec) MariaDB [test]> SELECT * FROM test; +--------+--------+------+------+------+ | intA | string | intB | intC | intD | +--------+--------+------+------+------+ | 200193 | B2B | 3 | 2 | 1234 | | 200193 | B2B | 4 | 0 | 999 | +--------+--------+------+------+------+ 2 rows in set (0,000 sec)   MariaDB [test]> select version(); +-----------------+ | version() | +-----------------+ | 10.6.12-MariaDB | +-----------------+ 1 row in set (0,000 sec)
            juanmitaboada Juanmi Taboada added a comment -

            So far I can reproduce this bug in:

            • Ubuntu:10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
            • Debian: 10.5.19-MariaDB-1:10.5.19+maria~deb11 mariadb.org binary distribution

            Configurations attached. etc_mysql.zip

            juanmitaboada Juanmi Taboada added a comment - So far I can reproduce this bug in: Ubuntu :10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04 Debian : 10.5.19-MariaDB-1:10.5.19+maria~deb11 mariadb.org binary distribution Configurations attached. etc_mysql.zip

            People

              Unassigned Unassigned
              juanmitaboada Juanmi Taboada
              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.