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

Incorrect results of SELECT statement found by transaction comparison

    XMLWordPrintable

    Details

      Description

      I used my fuzzing tool to test MariaDB and found a transaction-related bug that make the 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 for the transaction T0
      /usr/local/mysql/bin/mysql -uroot -Dtestdb # set up for the transaction T1

      Txn 1> START TRANSACTION;

      Txn 0> START TRANSACTION;

      Txn 1> insert into t_4rbssc (wkey, pkey, c_qrgwb, c_8u7ipc, c_mqgwfb, c_7j_zjb) values
      (225, 489000, null, 11.49, 89, 63);

      Txn 1> ROLLBACK;

      Txn 0> select *
      from
      t_4rbssc
      where t_4rbssc.wkey = 4 and t_4rbssc.c_sbxs3c not in (
      select
      count(ref_0.c_baxlp) over (partition by ref_0.c_lba4ac order by ref_0.c_baxlp) as c0
      from
      t__w2gab as ref_0);

      Txn 0> COMMIT;

      Output of SELECT statement:

      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      | wkey | pkey  | c_umaal | c_qrgwb | c_wzm9wc | c_8u7ipc | c_mqgwfb | c_sbxs3c | c_kkizw | c_7j_zjb |
      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      |    4 | 34000 | 4bquu   | entwob  |       87 |    84.64 |       93 |        5 | glalkc  |       47 |
      |    4 | 36000 | _wacsb  | 3_7us   |      100 |    91.97 |       77 |       51 | mf8txb  |       79 |
      |    4 | 37000 | obkbfb  | ku0pmd  |       74 |    97.73 |       47 |       41 | NULL    |       19 |
      |    4 | 38000 | yzdmqb  | sfxi_c  |       66 |    22.93 |       79 |       96 | xjkqb   |       56 |
      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      4 rows in set (0.006 sec)
      

      Testcase 2

      /usr/local/mysql/bin/mysql -uroot -Dtestdb set up for the transaction T0

      Txn 0> START TRANSACTION;

      Txn 0> select *
      from
      t_4rbssc
      where t_4rbssc.wkey = 4 and t_4rbssc.c_sbxs3c not in (
      select
      count(ref_0.c_baxlp) over (partition by ref_0.c_lba4ac order by ref_0.c_baxlp) as c0
      from
      t__w2gab as ref_0);

      Txn 0> COMMIT;

      Output of SELECT statement:

      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      | wkey | pkey  | c_umaal | c_qrgwb | c_wzm9wc | c_8u7ipc | c_mqgwfb | c_sbxs3c | c_kkizw | c_7j_zjb |
      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      |    4 | 35000 | 2w5lsc  | NULL    |        6 |    42.97 |       86 |        1 | evgzfc  |       77 |
      |    4 | 36000 | _wacsb  | 3_7us   |      100 |    91.97 |       77 |       51 | mf8txb  |       79 |
      |    4 | 37000 | obkbfb  | ku0pmd  |       74 |    97.73 |       47 |       41 | NULL    |       19 |
      |    4 | 38000 | yzdmqb  | sfxi_c  |       66 |    22.93 |       79 |       96 | xjkqb   |       56 |
      +------+-------+---------+---------+----------+----------+----------+----------+---------+----------+
      4 rows in set (0.007 sec)
      

      The SELECT statement in Testcase 1 and Testcase 2 should return the same results. However, they are different in their first row of output. The first row in Test case 1 is (4, 34000, '4bquu', 'entwob', 87, 84.64, 93, 5, 'glalkc', 47), while the first row in Test case 2 is (4, 35000, '2w5lsc', NULL, 6, 42.97, 86, 1, 'evgzfc', 77)

        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.