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

Affected by ROLLBACK txn, COMMIT txn produce incorrect result

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6, 10.8.3, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.6
    • None
    • Ubuntu 20.04

    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

              psergei Sergei Petrunia
              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.