1--source include/not_embedded.inc 2--source include/have_query_cache.inc 3--source include/have_debug_sync.inc 4--source include/long_test.inc 5 6set global query_cache_type= ON; 7set @save_query_cache_size=@@global.query_cache_size; 8SET @save_concurrent_insert= @@GLOBAL.concurrent_insert; 9 10# 11# Bug #30887 Server crashes on SET GLOBAL query_cache_size=0 12# 13flush status; 14set query_cache_type=DEMAND; 15set global query_cache_size= 1024*768; 16--disable_warnings 17drop table if exists t1; 18--enable_warnings 19create table t1 (a varchar(100)); 20insert into t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 21connect (bug30887con1, localhost, root, ,test); 22connect (bug30887con2, localhost, root, ,test); 23 24connection bug30887con1; 25--echo Activate debug hook and attempt to retrieve the statement from the cache. 26set debug_sync="wait_in_query_cache_insert SIGNAL parked WAIT_FOR go"; 27--send select SQL_CACHE * from t1; 28 29connection default; 30set debug_sync="now WAIT_FOR parked"; 31 32connection bug30887con2; 33--echo clear the query cache. 34show status like 'Qcache_queries_in_cache'; 35set global query_cache_size= 0; 36 37connection default; 38--echo Signal the debug hook to release the lock. 39set debug_sync="now SIGNAL go"; 40 41--echo Show query cache status. 42show status like 'Qcache_queries_in_cache'; 43 44connection bug30887con1; 45--reap 46 47disconnect bug30887con1; 48disconnect bug30887con2; 49connection default; 50set debug_sync= 'RESET'; 51set global query_cache_size= 0; 52use test; 53drop table t1; 54 55# 56# Bug#41098: Query Cache returns wrong result with concurrent insert 57# 58 59--disable_warnings 60DROP TABLE IF EXISTS t1, t2; 61--enable_warnings 62CREATE TABLE t1 (a INT); 63CREATE TABLE t2 (a INT); 64INSERT INTO t1 VALUES (1),(2),(3); 65 66SET GLOBAL concurrent_insert= 1; 67SET GLOBAL query_cache_size= 1024*512; 68SET GLOBAL query_cache_type= ON; 69 70connect(con1,localhost,root,,test,,); 71connect(con2,localhost,root,,test,,); 72 73connection con1; 74SET DEBUG_SYNC = "wait_after_query_cache_invalidate SIGNAL parked WAIT_FOR go"; 75--echo # Send concurrent insert, will wait in the query cache table invalidate 76--send INSERT INTO t1 VALUES (4) 77 78connection default; 79--echo # Wait for concurrent insert to reach the debug point 80SET DEBUG_SYNC = "now WAIT_FOR parked"; 81 82connection con2; 83--echo # Send SELECT that shouldn't be cached 84SELECT * FROM t1; 85 86connection default; 87--echo # Notify the concurrent insert to proceed 88SET DEBUG_SYNC = "now SIGNAL go"; 89 90connection con1; 91--echo # Gather insert result 92--reap 93SHOW STATUS LIKE "Qcache_queries_in_cache"; 94--echo # Test that it's cacheable 95SELECT * FROM t1; 96SHOW STATUS LIKE "Qcache_queries_in_cache"; 97 98disconnect con1; 99disconnect con2; 100 101connection default; 102--echo # Restore defaults 103SET DEBUG_SYNC= 'RESET'; 104RESET QUERY CACHE; 105DROP TABLE t1,t2; 106SET GLOBAL concurrent_insert= DEFAULT; 107SET GLOBAL query_cache_size= DEFAULT; 108SET GLOBAL query_cache_type= DEFAULT; 109 110 111--echo # 112--echo # Bug43758 Query cache can lock up threads in 'freeing items' state 113--echo # 114FLUSH STATUS; 115SET GLOBAL query_cache_type=DEMAND; 116SET GLOBAL query_cache_size= 1024*768; 117--disable_warnings 118DROP TABLE IF EXISTS t1,t2,t3,t4,t5; 119--enable_warnings 120CREATE TABLE t1 (a VARCHAR(100)); 121CREATE TABLE t2 (a VARCHAR(100)); 122CREATE TABLE t3 (a VARCHAR(100)); 123CREATE TABLE t4 (a VARCHAR(100)); 124CREATE TABLE t5 (a VARCHAR(100)); 125 126INSERT INTO t1 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 127INSERT INTO t2 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 128INSERT INTO t3 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 129INSERT INTO t4 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 130INSERT INTO t5 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 131 132connect (thd2, localhost, root, ,test); 133connect (thd3, localhost, root, ,test); 134connect (thd1, localhost, root, ,test); 135 136connection thd1; 137--echo ** 138--echo ** Load Query Cache with a result set and one table. 139--echo ** 140SELECT SQL_CACHE * FROM t1; 141--echo ************************************************************************* 142--echo ** We want to accomplish the following state: 143--echo ** - Query cache status: TABLE_FLUSH_IN_PROGRESS 144--echo ** - THD1: invalidate_table_internal (iterating query blocks) 145--echo ** - THD2: query_cache_insert (cond_wait) 146--echo ** - THD3: query_cache_insert (cond_wait) 147--echo ** - No thread should be holding the structure_guard_mutex. 148--echo ** 149--echo ** First step is to place a DELETE-statement on the debug hook just 150--echo ** before the mutex lock in invalidate_table_internal. 151--echo ** This will allow new result sets to be written into the QC. 152--echo ** 153SET DEBUG_SYNC="wait_in_query_cache_invalidate1 SIGNAL parked1_1 WAIT_FOR go1_1"; 154SET DEBUG_SYNC="wait_in_query_cache_invalidate2 SIGNAL parked1_2 WAIT_FOR go1_2"; 155--send DELETE FROM t1 WHERE a like '%a%'; 156 157connection default; 158--echo ** Assert that the expect process status is obtained. 159SET DEBUG_SYNC="now WAIT_FOR parked1_1"; 160-- echo ** 161 162connection thd2; 163--echo ** On THD2: Insert a result into the cache. This attempt will be blocked 164--echo ** because of a debug hook placed just before the mutex lock after which 165--echo ** the first part of the result set is written. 166SET DEBUG_SYNC="wait_in_query_cache_insert SIGNAL parked2 WAIT_FOR go2 EXECUTE 1"; 167--send SELECT SQL_CACHE * FROM t2 UNION SELECT * FROM t3 168 169connection default; 170--echo ** Assert that the SELECT-stmt thread reaches the sync point. 171SET DEBUG_SYNC="now WAIT_FOR parked2"; 172--echo ** 173--echo ** 174 175connection thd3; 176--echo ** On THD3: Insert another result into the cache and block on the same 177--echo ** debug hook. 178SET DEBUG_SYNC="wait_in_query_cache_insert SIGNAL parked3 WAIT_FOR go3 EXECUTE 1"; 179--send SELECT SQL_CACHE * FROM t4 UNION SELECT * FROM t5 180 181connection default; 182--echo ** Assert that the SELECT-stmt thread reaches the sync point. 183SET DEBUG_SYNC="now WAIT_FOR parked3"; 184--echo ** 185--echo ** 186 187--echo ** Signal the DELETE thread, THD1, to continue. It will enter the mutex 188--echo ** lock and set query cache status to TABLE_FLUSH_IN_PROGRESS and then 189--echo ** unlock the mutex before stopping on the next debug hook. 190SET DEBUG_SYNC="now SIGNAL go1_1"; 191--echo ** Assert that we reach the next debug hook. 192SET DEBUG_SYNC="now WAIT_FOR parked1_2"; 193 194--echo ** 195--echo ** Signal the remaining debug hooks blocking THD2 and THD3. 196--echo ** The threads will grab the guard mutex enter the wait condition and 197--echo ** and finally release the mutex. The threads will continue to wait 198--echo ** until a broadcast signal reaches them causing both threads to 199--echo ** come alive and check the condition. 200SET DEBUG_SYNC="now SIGNAL go2"; 201SET DEBUG_SYNC="now SIGNAL go3"; 202 203--echo ** 204--echo ** Finally signal the DELETE statement on THD1 one last time. 205--echo ** The stmt will complete the query cache invalidation and return 206--echo ** cache status to NO_FLUSH_IN_PROGRESS. On the status change 207--echo ** One signal will be sent to the thread group waiting for executing 208--echo ** invalidations and a broadcast signal will be sent to the thread 209--echo ** group holding result set writers. 210SET DEBUG_SYNC="now SIGNAL go1_2"; 211 212--echo ** 213--echo ************************************************************************* 214--echo ** No tables should be locked 215connection thd2; 216reap; 217DELETE FROM t1; 218DELETE FROM t2; 219DELETE FROM t3; 220 221connection thd3; 222reap; 223DELETE FROM t4; 224DELETE FROM t5; 225 226connection thd1; 227reap; 228 229--echo ** Done. 230 231connection default; 232disconnect thd1; 233disconnect thd2; 234disconnect thd3; 235SET DEBUG_SYNC= 'RESET'; 236SET GLOBAL query_cache_size= 0; 237 238connection default; 239--echo # Restore defaults 240RESET QUERY CACHE; 241FLUSH STATUS; 242DROP TABLE t1,t2,t3,t4,t5; 243SET GLOBAL query_cache_size= DEFAULT; 244SET GLOBAL query_cache_type= DEFAULT; 245 246--echo # 247--echo # Bug#56822: Add a thread state for sessions waiting on the query cache lock 248--echo # 249 250--disable_warnings 251DROP TABLE IF EXISTS t1; 252--enable_warnings 253CREATE TABLE t1 (a INT); 254INSERT INTO t1 VALUES (1),(2),(3); 255 256SET GLOBAL concurrent_insert= 1; 257SET GLOBAL query_cache_size= 1024*512; 258SET GLOBAL query_cache_type= ON; 259 260connect(con1,localhost,root,,test,,); 261connect(con2,localhost,root,,test,,); 262 263connection con1; 264SET DEBUG_SYNC = "wait_in_query_cache_invalidate2 SIGNAL parked WAIT_FOR go"; 265--echo # Send INSERT, will wait in the query cache table invalidation 266--send INSERT INTO t1 VALUES (4); 267 268connection default; 269--echo # Wait for insert to reach the debug point 270SET DEBUG_SYNC = "now WAIT_FOR parked"; 271 272connection con2; 273--echo # Send a query that should wait on the query cache lock 274--send RESET QUERY CACHE 275 276connection default; 277--echo # Wait for the state to be reflected in the processlist 278let $wait_condition= 279 SELECT COUNT(*) = 1 FROM information_schema.processlist 280 WHERE state = "Waiting for query cache lock" AND info = "RESET QUERY CACHE"; 281--source include/wait_condition.inc 282--echo # Signal that the query cache can be unlocked 283SET DEBUG_SYNC="now SIGNAL go"; 284 285connection con1; 286--reap 287disconnect con1; 288 289connection con2; 290--reap 291disconnect con2; 292 293connection default; 294--echo # Restore defaults 295SET DEBUG_SYNC= 'RESET'; 296RESET QUERY CACHE; 297DROP TABLE t1; 298SET GLOBAL query_cache_size= @save_query_cache_size; 299SET GLOBAL query_cache_type= DEFAULT; 300 301--echo # 302--echo # MDEV-14526: MariaDB keeps crashing under load when 303--echo # query_cache_type is changed 304--echo # 305 306CREATE TABLE t1 ( 307 `id` int(10) NOT NULL AUTO_INCREMENT, 308 `k` int(10) default '0', 309 PRIMARY KEY (`id`)) 310ENGINE=MyISAM; 311 312INSERT IGNORE INTO t1 VALUES 313 (NULL,1),(NULL,8),(NULL,NULL),(NULL,NULL),(NULL,4),(NULL,9),(NULL,7), 314 (NULL,3),(NULL,NULL),(NULL,2),(NULL,3),(NULL,NULL),(NULL,2),(NULL,7), 315 (NULL,1),(NULL,2),(NULL,4),(NULL,NULL),(NULL,1),(NULL,1),(NULL,4); 316 317SET GLOBAL query_cache_size= 1024*1024; 318SET GLOBAL query_cache_type= 1; 319 320--connect (con2,localhost,root,,test) 321--connect (con1,localhost,root,,test) 322set debug_sync="wait_in_query_cache_store_query SIGNAL parked WAIT_FOR go"; 323--send SELECT DISTINCT id FROM t1 WHERE id BETWEEN 5603 AND 16218 ORDER BY k 324 325--connection default 326 327set debug_sync="now WAIT_FOR parked"; 328--connection con2 329--send SET GLOBAL query_cache_type= 0; 330 331--connection default 332set debug_sync="now SIGNAL go"; 333 334--connection con1 335--reap 336--connection con2 337--reap 338 339# Cleanup 340--disconnect con1 341--disconnect con2 342--connection default 343set debug_sync= 'RESET'; 344DROP TABLE t1; 345SET GLOBAL query_cache_size=@save_query_cache_size; 346SET GLOBAL query_cache_type= DEFAULT; 347SET @@GLOBAL.concurrent_insert=@save_concurrent_insert; 348 349--echo # End of 5.5 tests 350