1SET @query_cache_type= @@global.query_cache_type; 2SET @query_cache_limit= @@global.query_cache_limit; 3SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit; 4SET @query_cache_size= @@global.query_cache_size; 5# Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock 6connect user1,localhost,root,,test,,; 7connect user2,localhost,root,,test,,; 8connect user3,localhost,root,,test,,; 9connection user1; 10SET GLOBAL query_cache_type=1; 11SET GLOBAL query_cache_limit=10000; 12SET GLOBAL query_cache_min_res_unit=0; 13SET GLOBAL query_cache_size= 102400; 14FLUSH TABLES; 15DROP TABLE IF EXISTS t1, t2; 16CREATE TABLE t1 (a INT); 17CREATE TABLE t2 (a INT); 18INSERT INTO t1 VALUES (1),(2),(3); 19connection user2; 20LOCK TABLE t2 WRITE; 21connection user1; 22# "send" the next select, "reap" the result later. 23# The select will be blocked by the write lock on the t1. 24SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; 25connection user3; 26# Poll till the select of connection user1 is blocked by the write lock on t1. 27SELECT user,command,state,info FROM information_schema.processlist 28WHERE state = 'Waiting for table metadata lock' 29 AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1'; 30user command state info 31root Query Waiting for table metadata lock SELECT *, (SELECT COUNT(*) FROM t2) FROM t1 32INSERT INTO t1 VALUES (4); 33connection user2; 34UNLOCK TABLES; 35connection user1; 36# Collecting ("reap") the result from the previously blocked select. 37# The printing of the result (varies between 3 and 4 rows) set has to be suppressed. 38connection user3; 39# The next select enforces that effects of "concurrent_inserts" like the 40# record with a = 4 is missing in result sets can no more happen. 41SELECT 1 FROM t1 WHERE a = 4; 421 431 44connection user1; 45# The next result set must contain 4 rows. 46SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; 47a (SELECT COUNT(*) FROM t2) 481 0 492 0 503 0 514 0 52RESET QUERY CACHE; 53SELECT *, (SELECT COUNT(*) FROM t2) FROM t1; 54a (SELECT COUNT(*) FROM t2) 551 0 562 0 573 0 584 0 59DROP TABLE t1,t2; 60connection default; 61disconnect user1; 62disconnect user2; 63disconnect user3; 64SET GLOBAL query_cache_type= @query_cache_type; 65SET GLOBAL query_cache_limit= @query_cache_limit; 66SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit; 67SET GLOBAL query_cache_size= @query_cache_size; 68