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

Inconsistent SELECT results when query cache is enabled

    XMLWordPrintable

Details

    Description

      MariaDB 10.2 and newer returns inconsistent results when query caching is enabled (query_cache_type=ON).

      Steps to reproduce

      1. Start a MariaDB 10.2+ database with default settings.

      For example by running a docker container on port 3500:

      shell> docker run --name mariadb-10.2.14 -p 3500:3306 -e MYSQL_ROOT_PASSWORD=test -e MYSQL_USER=test -e MYSQL_PASSWORD=test -e MYSQL_DATABASE=test -d mariadb:10.2.14
      

      2. Run the attached mysqltest case MDEV-16087.mysqltest

      shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file=MDEV-16087.record test < MDEV-16087.mysqltest
      

      It fails with:

      --- MDEV-16087.record	2018-05-15 15:42:11.495372790 +0200
      +++ MDEV-16087.reject	2018-05-15 15:52:24.547984967 +0200
      @@ -18,7 +18,7 @@
       1
       select count(*) from table3;
       count(*)
      -1
      +0
       select sql_no_cache count(*) from table3;
       count(*)
       1
       
      mysqltest: Result content mismatch
      

      3. Disable query caching and re-run the mysqltest case:

      shell> echo "set global query_cache_type = off;" | mysql -h 127.0.0.1 --port=3500 --user=root --password=test
      shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file=MDEV-16087.record test < MDEV-16087.mysqltest
      

      Mysqltest succeeds.

      Environment

      • Ubuntu
      • MariaDB 10.2.14
      • Query cache settings:

        MariaDB [(none)]> show variables like '%query_cache%';
        +------------------------------+----------+
        | Variable_name                | Value    |
        +------------------------------+----------+
        | have_query_cache             | YES      |
        | query_cache_limit            | 131072   |
        | query_cache_min_res_unit     | 4096     |
        | query_cache_size             | 67108864 |
        | query_cache_strip_comments   | OFF      |
        | query_cache_type             | ON       |
        | query_cache_wlock_invalidate | OFF      |
        +------------------------------+----------+
        

      Attachments

        1. MDEV-16087.mysqltest
          0.9 kB
          Benedikt Waldvogel
        2. MDEV-16087.record
          0.6 kB
          Benedikt Waldvogel

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              bwaldvogel Benedikt Waldvogel
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.