MySQL Bugs: #41098: Query Cache returns wrong result with concurrent insert

Bug #41098	Query Cache returns wrong result with concurrent insert
Submitted:	28 Nov 2008 14:33	Modified:	18 Mar 2009 14:49
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.1,6.0	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	qc, qcache, query cache, random, regression, wrong result

Description:
Please run the attached test query_cache_28249.
It contains the a modified test for the historic
Bug#28249 Query Cache returns wrong result with
          concurrent insert / certain lock
There is a high likelihood (my experiments on tmpfs
~90%) that you will get the following bad effect:
<connection user1>
SELECT 1 FROM t1 WHERE a = 4;
1
1
# Switch to connection user1
# The next result set must contain 4 rows.
SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
a       (SELECT COUNT(*) FROM t2)
1       0
2       0
3       0
    <=== Here is a row missing.
RESET QUERY CACHE;
SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
a       (SELECT COUNT(*) FROM t2)
1       0
2       0
3       0
4       0

It looks like connection user1 gets on its first select
above the outdated result of a previous select.

Please have also a look into the comments around
random failures of the test query_cache.test
   http://bugs.mysql.com/bug.php?id=40179
   Bug#40179 Test main.query_cache failing randomly
             on Pushbuild, test weakness

If the fix for Bug#40179 gets approved than
- the test for bug#28249 will be improved and
  moved from query_cache.test to 
  query_cache_28249.test (see attachment)
- query_cache_28249 will be added to t/disabled.def
  because of the current bug

The experiments in 5.1-bugteam showed that a
combination:
- MyISAM + concurrent-inserts on
- extreme small delays between the SQL commands
- query cache on
is needed for getting the problem above.

Versions showing the current bug:
- mysql-5.1, mysql-5.1-bugteam, mysql-6.0-bugteam
  last changeset 2008-Nov
  compiled from source with
  BUILD/compile-pentium-debug-max
- mysql-enterprise-commercial-advanced-5.1.30-linux-i686-glibc23
  Binary created by the build team

I was unable to reproduce the problem on 
mysql-5.0-bugteam. The reason seem to be changes
in 5.1 and up, which drastically increase the
likelihood that prerequisites for the current bug
are fulfilled. But it seems to be improbable that
5.0 cannot suffer from the current bug.

My environment:
- Linux, OpenSuSE 11.0 64 Bit
- Intel Core2Duo (64 Bit) 

How to repeat:
Please see above

test script

Attachment: query_cache_28249.test (application/octet-stream, text), 3.62 KiB.

Expected results

Attachment: query_cache_28249.result (application/octet-stream, text), 1.78 KiB.

How to avoid this bug based on my experiments:
- use a engine <> MyISAM
or
- switch concurrent_inserts off
or
- switch the query cache off

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60193

2713 Matthias Leich	2008-11-28
      - Fix for Bug#40179 Test main.query_cache failing randomly on Pushbuild, test weakness
      - disable the test suffering from Bug#41098 Query Cache returns wrong result with
                                                  concurrent insert
      - additional improvements
      
      Details:
      - Move the test for the historic bug
        Bug 28249 Query Cache returns wrong result with concurrent insert / certain lock
        into its own testscript query_cache_28249.test.
      - query_cache.test:
        - replace error numbers with error names
        - remove trailing spaces, replace tabs with spaces
        - reset of @@global.log_bin_trust_function_creators to its original value
          at the end of the test
      - query_cache_28249.test:
        - replace sleep 5 with poll routine
        - avoid random differences caused by concurrent_inserts effects
        - improved comments and formatting

Unintended action of push email processing.
The changeset only disables this test.

The patch for
Bug#40179 Test main.query_cache failing randomly on
    Pushbuild, test weakness
is pushed to 5.1/6.0-bugteam. 
Please use "query_cache_28249" (included in fix above)
for replaying the problem and remove this test from
t/disabled.def if the bug is fixed.

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

A fix that went in 5.1 removed a unlock tables and broke the fix for Bug#28249.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66030

2801 Davi Arnaut	2009-02-12
      Bug#41098: Query Cache returns wrong result with concurrent insert
      
      The problem is that select queries executed concurrently with
      concurrent inserts on a MyISAM table could be cached if the
      select started after the query cache invalidation but before
      the unlock of tables performed by the concurrent insert. This
      race could happen because the concurrent insert was failing
      to prevent cache of select queries happening at the same time.
      
      The solution is to a 'uncacheable' status flag to signal that
      a concurrent insert is being performed on the table and that
      queries executing at the same time shouldn't cache the results.
      modified:
        mysql-test/r/query_cache_debug.result
        mysql-test/t/disabled.def
        mysql-test/t/query_cache_debug.test
        sql/sql_cache.cc
        sql/sql_insert.cc
        storage/myisam/ha_myisam.cc
        storage/myisam/mi_locking.c
        storage/myisam/myisamdef.h

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66972

2809 Davi Arnaut	2009-02-19
      Bug#41098: Query Cache returns wrong result with concurrent insert
      
      The problem is that select queries executed concurrently with
      a concurrent insert on a MyISAM table could be cached if the
      select started after the query cache invalidation but before
      the unlock of tables performed by the concurrent insert. This
      race could happen because the concurrent insert was failing
      to prevent cache of select queries happening at the same time.
      
      The solution is to add a 'uncacheable' status flag to signal
      that a concurrent insert is being performed on the table and
      that queries executing at the same time shouldn't cache the
      results.
     @ mysql-test/r/query_cache_debug.result
        Add test case result for Bug#41098
     @ mysql-test/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/query_cache_debug.test
        Add test case for Bug#41098
     @ sql/sql_cache.cc
        Debug sync point for regression testing purposes.
     @ sql/sql_insert.cc
        Remove meaningless query cache invalidate. There is already
        a preceding invalidate for queries that started before the
        concurrent insert.
     @ storage/myisam/ha_myisam.cc
        Check for a active concurrent insert.
     @ storage/myisam/mi_locking.c
        Signal the start of a concurrent insert. Flag is zeroed once
        the state is updated back.
     @ storage/myisam/myisamdef.h
        Add flag to signal a active concurrent insert.

Queued to 5.1-bugteam

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090224070618-mr7stu6rfcvoj18g) (merge vers: 5.1.33) (pib:6)

Noted in 5.1.33 changelog.

SELECT statements executed concurrently with INSERT statements for a
MyISAM table could cause incorrect results to be returned from the
query cache. 

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090223123708-n9rf2to3g15br7za) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.11 changelog.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)