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

Affected by ROLLBACK txn, COMMIT txn produce incorrect result

    XMLWordPrintable

    Details

      Description

      I used my fuzzing tool to test MariaDB and found a transaction-related bug that make server produce incorrect 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 3 transactions T0, T1 and T2
      Txn 0> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

      Txn 0> START TRANSACTION;
      Txn 2> START TRANSACTION;
      Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
      Txn 0> COMMIT;
      Txn 1> START TRANSACTION;
      Txn 2> insert into t_j_eqsc (wkey, pkey) values (79, 162000);
      Txn 2> ROLLBACK;
      Txn 1> select * from t_j_eqsc
      where t_j_eqsc.c_fm792b not in (
      select PI() as c0 from t_xqlwp as ref_0); --- output 10 rows
      Txn 1> COMMIT;

      Testcase 2

      /usr/local/mysql/bin/mysql -uroot -Dtestdb
      Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
      Txn 0> select * from t_j_eqsc
      where t_j_eqsc.c_fm792b not in (
      select PI() as c0 from t_xqlwp as ref_0); --- output empty

      Testcase 1 and Testcase 2 should produce the same results. However, the SELECT statement in Testcase 1 output 10 rows while the SELECT statement in Testcase 2 output empty.

      Analyzing the test case, I think the SELECT statement in Testcase 1 produces incorrect results. Because the UPDATE in txn 0 has changed t_j_eqsc.c_fm792b to PI(), the WHERE clause in SELECT in txn 1 will be violated and thus the SELECT should output empty.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              Zuming Jiang Zuming Jiang
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.