diff options
Diffstat (limited to 'mysql-test/r/query_cache_28249.result')
| -rw-r--r-- | mysql-test/r/query_cache_28249.result | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/mysql-test/r/query_cache_28249.result b/mysql-test/r/query_cache_28249.result new file mode 100644 index 00000000000..aed84911032 --- /dev/null +++ b/mysql-test/r/query_cache_28249.result @@ -0,0 +1,62 @@ +SET @query_cache_type= @@global.query_cache_type; +SET @query_cache_limit= @@global.query_cache_limit; +SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit; +SET @query_cache_size= @@global.query_cache_size; +# Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock +# Establish connections user1,user2,user3 (user=root) +# Switch to connection user1 +SET GLOBAL query_cache_type=1; +SET GLOBAL query_cache_limit=10000; +SET GLOBAL query_cache_min_res_unit=0; +SET GLOBAL query_cache_size= 100000; +FLUSH TABLES; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Switch to connection user2 +LOCK TABLE t2 WRITE; +# Switch to connection user1 +# "send" the next select, "reap" the result later. +# The select will be blocked by the write lock on the t1. +SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; +# Switch to connection user3 +# Poll till the select of connection user1 is blocked by the write lock on t1. +SELECT user,command,state,info FROM information_schema.processlist +WHERE state = 'Locked' + AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1'; +user command state info +root Query Locked SELECT *, (SELECT COUNT(*) FROM t2) FROM t1 +INSERT INTO t1 VALUES (4); +# Switch to connection user2 +UNLOCK TABLES; +# Switch to connection user1 +# Collecting ("reap") the result from the previously blocked select. +# The printing of the result (varies between 3 and 4 rows) set has to be suppressed. +# Switch to connection user3 +# The next select enforces that effects of "concurrent_inserts" like the +# record with a = 4 is missing in result sets can no more happen. +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 +4 0 +RESET QUERY CACHE; +SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; +a (SELECT COUNT(*) FROM t2) +1 0 +2 0 +3 0 +4 0 +DROP TABLE t1,t2; +# Switch to connection default + close connections user1,user2,user3 +SET GLOBAL query_cache_type= @query_cache_type; +SET GLOBAL query_cache_limit= @query_cache_limit; +SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit; +SET GLOBAL query_cache_size= @query_cache_size; |