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

MariaDB produce different results for INSERT statement when using transaction

    XMLWordPrintable

    Details

      Description

      I used my fuzzing tool to test MariaDB and found a transaction-related bug that make server produce different results.

      Mariadb installation
      1) cd mariadb-10.8.3
      2) mkdir build; cd build
      3) cmake .. -DCMAKE_BUILD_TYPE=Debug
      4) make -j12 && sudo make install

      Setup the environment
      1) export ASAN_OPTIONS=detect_leaks=0
      2) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
      3) /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql # set up the database

      Reproduce bug

      Testcase 1

      /usr/local/mysql/bin/mysql -uroot -Dtestdb # set up 2 transactions T0 and T1
      Txn 0> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
      Txn 0> START TRANSACTION;
      Txn 1> START TRANSACTION;
      Txn 1> delete from t_euhshb;
      Txn 1> ROLLBACK; Txn 1
      Txn 0> insert into t_7sdcgd values
      (91, 167000, case when exists (
      select *
      from
      (t_euhshb as ref_0
      inner join t_euhshb as ref_1
      on (ref_0.pkey = ref_1.wkey ))
      where ref_1.wkey = (
      select
      ref_0.c_oyg4yd as c0
      from
      t_euhshb as ref_2)
      ) then 1
      else 2 end
      , 96, 71.64, '1c08ld');
      Txn 0> select * from t_7sdcgd where wkey = 91;
      Txn 0> COMMIT;

      Testcase 2

      /usr/local/mysql/bin/mysql -uroot -Dtestdb
      Txn 0> insert into t_7sdcgd values
      (91, 167000, case when exists (
      select *
      from
      (t_euhshb as ref_0
      inner join t_euhshb as ref_1
      on (ref_0.pkey = ref_1.wkey ))
      where ref_1.wkey = (
      select
      ref_0.c_oyg4yd as c0
      from
      t_euhshb as ref_2)
      ) then 1
      else 2 end
      , 96, 71.64, '1c08ld');
      Txn 0> select * from t_7sdcgd where wkey = 91;

      Testcase 1 and Testcase 2 should produce the same results. However, The INSERT statement in Testcase 1 succeeds, and the last SELECT statement output one row (91, 167000, 2, 96, 71.64 , 1c08ld), while the INSERT statement in Testcase 2 fails, and the last SELECT statement output empty.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              Zuming Jiang Zuming Jiang
              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.