71
71
SET GLOBAL concurrent_insert= DEFAULT;
72
72
SET GLOBAL query_cache_size= DEFAULT;
73
73
SET GLOBAL query_cache_type= DEFAULT;
75
# Bug43758 Query cache can lock up threads in 'freeing items' state
78
SET GLOBAL query_cache_type=DEMAND;
79
SET GLOBAL query_cache_size= 1024*768;
80
DROP TABLE IF EXISTS t1,t2,t3,t4,t5;
81
CREATE TABLE t1 (a VARCHAR(100));
82
CREATE TABLE t2 (a VARCHAR(100));
83
CREATE TABLE t3 (a VARCHAR(100));
84
CREATE TABLE t4 (a VARCHAR(100));
85
CREATE TABLE t5 (a VARCHAR(100));
86
INSERT INTO t1 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
87
INSERT INTO t2 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
88
INSERT INTO t3 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
89
INSERT INTO t4 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
90
INSERT INTO t5 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
91
=================================== Connection thd1
93
** Load Query Cache with a result set and one table.
95
SELECT SQL_CACHE * FROM t1;
97
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
98
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
99
*************************************************************************
100
** We want to accomplish the following state:
101
** - Query cache status: TABLE_FLUSH_IN_PROGRESS
102
** - THD1: invalidate_table_internal (iterating query blocks)
103
** - THD2: query_cache_insert (cond_wait)
104
** - THD3: query_cache_insert (cond_wait)
105
** - No thread should be holding the structure_guard_mutex.
107
** First step is to place a DELETE-statement on the debug hook just
108
** before the mutex lock in invalidate_table_internal.
109
** This will allow new result sets to be written into the QC.
111
SET SESSION debug='+d,wait_in_query_cache_invalidate1';
112
SET SESSION debug='+d,wait_in_query_cache_invalidate2';
113
DELETE FROM t1 WHERE a like '%a%';;
114
=================================== Connection default
115
** Assert that the expect process status is obtained.
117
=================================== Connection thd2
118
** On THD2: Insert a result into the cache. This attempt will be blocked
119
** because of a debug hook placed just before the mutex lock after which
120
** the first part of the result set is written.
121
SET SESSION debug='+d,wait_in_query_cache_insert';
122
SELECT SQL_CACHE * FROM t2 UNION SELECT * FROM t3;
123
=================================== Connection thd3
124
** On THD3: Insert another result into the cache and block on the same
126
SET SESSION debug='+d,wait_in_query_cache_insert';
127
SELECT SQL_CACHE * FROM t4 UNION SELECT * FROM t5;;
128
=================================== Connection default
129
** Assert that the two SELECT-stmt threads to reach the hook.
132
** Signal the DELETE thread, THD1, to continue. It will enter the mutex
133
** lock and set query cache status to TABLE_FLUSH_IN_PROGRESS and then
134
** unlock the mutex before stopping on the next debug hook.
135
SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate1' LIMIT 1 INTO @flush_thread_id;
136
KILL QUERY @flush_thread_id;
137
** Assert that we reach the next debug hook.
139
** Signal the remaining debug hooks blocking THD2 and THD3.
140
** The threads will grab the guard mutex enter the wait condition and
141
** and finally release the mutex. The threads will continue to wait
142
** until a broadcast signal reaches them causing both threads to
143
** come alive and check the condition.
144
SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id ASC LIMIT 1 INTO @thread_id;
145
KILL QUERY @thread_id;
146
SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id DESC LIMIT 1 INTO @thread_id;
147
KILL QUERY @thread_id;
149
** Finally signal the DELETE statement on THD1 one last time.
150
** The stmt will complete the query cache invalidation and return
151
** cache status to NO_FLUSH_IN_PROGRESS. On the status change
152
** One signal will be sent to the thread group waiting for executing
153
** invalidations and a broadcast signal will be sent to the thread
154
** group holding result set writers.
155
SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2' LIMIT 1 INTO @flush_thread_id;
156
KILL QUERY @flush_thread_id;
158
*************************************************************************
159
** No tables should be locked
160
=================================== Connection thd2
162
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
163
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
167
=================================== Connection thd3
169
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
170
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
173
=================================== Connection thd1
175
SET GLOBAL query_cache_size= 0;
179
DROP TABLE t1,t2,t3,t4,t5;
180
SET GLOBAL query_cache_size= DEFAULT;
181
SET GLOBAL query_cache_type= DEFAULT;