diff options
Diffstat (limited to 'mysql-test/main/query_cache_28249.result')
| -rw-r--r-- | mysql-test/main/query_cache_28249.result | 67 |
1 files changed, 67 insertions, 0 deletions
diff --git a/mysql-test/main/query_cache_28249.result b/mysql-test/main/query_cache_28249.result new file mode 100644 index 00000000..ba694179 --- /dev/null +++ b/mysql-test/main/query_cache_28249.result @@ -0,0 +1,67 @@ +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 +connect user1,localhost,root,,test,,; +connect user2,localhost,root,,test,,; +connect user3,localhost,root,,test,,; +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= 102400; +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); +connection user2; +LOCK TABLE t2 WRITE; +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; +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 = 'Waiting for table metadata lock' + AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1'; +user command state info +root Query Waiting for table metadata lock SELECT *, (SELECT COUNT(*) FROM t2) FROM t1 +INSERT INTO t1 VALUES (4); +connection user2; +UNLOCK TABLES; +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. +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 +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; +connection default; +disconnect user1; +disconnect user2; +disconnect 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; |