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

Inconsistent read and write, which use the same predicate (WHERE clause)

    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.10.1
      2) mkdir build; cd build
      3) cmake .. -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN=ON
      4) make -j12 && sudo make install

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

      Reproduce bug

      /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

      T0> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      T1> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      T1> START TRANSACTION;
      T1> update t_g6ckkb set wkey = 162;
      T0> START TRANSACTION;
      T0> select * from t_g6ckkb;
      T1> COMMIT;
      T0> select * from t_rpjlsd where t_rpjlsd.c_pfd8ab <= (select min(wkey) from t_g6ckkb);

      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      | wkey | pkey   | c_trycnb | c_5b3h_c | c_pfd8ab | c_mvgo1c | c_2twe2d | c_nmcpzc | c_loj6e | c_veoe1 |
      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      |   43 | 243000 |       30 | NULL     |        8 | NULL     |       70 |     NULL | awnrab  |   39.83 |
      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      1 rows in set (0.006 sec)
      

      T0> update t_rpjlsd set wkey = 63 where t_rpjlsd.c_pfd8ab <= (select min(wkey) from t_g6ckkb);

      Query OK, 2 rows affected (0.003 sec)
      Rows matched: 2  Changed: 2  Warnings: 0
      

      T0> select * from t_rpjlsd where wkey = 63;

      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      | wkey | pkey   | c_trycnb | c_5b3h_c | c_pfd8ab | c_mvgo1c | c_2twe2d | c_nmcpzc | c_loj6e | c_veoe1 |
      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      |   63 | 243000 |       30 | NULL     |        8 | NULL     |       70 |     NULL | awnrab  |   39.83 |
      |   63 | 332000 |       68 | _pqr1c   |       53 | 9g7bt    |     NULL |       75 | tb1ugc  |    7.62 |
      +------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
      2 rows in set (0.002 sec)
      

      T0> COMMIT;

      Analysis
      The first SELECT in T0 and the UPDATE in T0 should handle the same rows because they use the same predicate (WHERE clause) and execute adjacently. However, the first SELECT in T0 outputs only 1 row while the UPDATE in T0 changes 2 rows. To make sure what the UPDATE in T0 changes, we use the second SELECT in T0, and it outputs the changed 2 rows.

      Based on the analysis, I think it might be a bug triggering inconsistent read (first SELECT in T0) and write (UPDATE in T0).

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              Zuming Jiang Zuming Jiang
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.