~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

insert into t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

connect (bug30887con1, localhost, root, ,test);

connect (bug30887con2, localhost, root, ,test);

connection bug30887con1;

--echo Activate debug hook and attempt to retrieve the statement from the cache.

set session debug='+d,wait_in_query_cache_insert';

--send select SQL_CACHE * from t1;

connection default;

let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'wait_in_query_cache_insert';

--source include/wait_condition.inc

connection bug30887con2;

--echo On a second connection; clear the query cache.

show status like 'Qcache_queries_in_cache';

set global query_cache_size= 0;

connection default;

--echo Signal the debug hook to release the lock.

select id from information_schema.processlist where state='wait_in_query_cache_insert' into @thread_id;

kill query @thread_id;

--echo Show query cache status.

show status like 'Qcache_queries_in_cache';

disconnect bug30887con1;

disconnect bug30887con2;

set global query_cache_size= 0;

use test;

drop table t1;

# Bug#41098: Query Cache returns wrong result with concurrent insert

SET @old_concurrent_insert= @@GLOBAL.concurrent_insert;

SET @old_query_cache_size= @@GLOBAL.query_cache_size;

--disable_warnings

DROP TABLE IF EXISTS t1, t2;

--enable_warnings

CREATE TABLE t1 (a INT);

CREATE TABLE t2 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

SET GLOBAL concurrent_insert= 1;

SET GLOBAL query_cache_size= 1024*512;

SET GLOBAL query_cache_type= ON;

connect(con1,localhost,root,,test,,);

connect(con2,localhost,root,,test,,);

connection con1;

--echo # Switch to connection con1

SET SESSION debug='+d,wait_after_query_cache_invalidate';

--echo # Send concurrent insert, will wait in the query cache table invalidate

--send INSERT INTO t1 VALUES (4)

connection default;

--echo # Switch to connection default

--echo # Wait for concurrent insert to reach the debug point

let $wait_condition=

SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE STATE = "wait_after_query_cache_invalidate" AND

INFO = "INSERT INTO t1 VALUES (4)";

--source include/wait_condition.inc

connection con2;

--echo # Switch to connection con2

--echo # Send SELECT that shouldn't be cached

SELECT * FROM t1;

connection default;

--echo # Switch to connection default

--echo # Notify the concurrent insert to proceed

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE STATE = 'wait_after_query_cache_invalidate' INTO @thread_id;

KILL QUERY @thread_id;

connection con1;

--echo # Switch to connection con1

--echo # Gather insert result

--reap

SHOW STATUS LIKE "Qcache_queries_in_cache";

100

--echo # Test that it's cacheable

101

SELECT * FROM t1;

102

SHOW STATUS LIKE "Qcache_queries_in_cache";

103

104

--echo # Disconnect

105

disconnect con1;

106

disconnect con2;

107

108

connection default;

109

--echo # Restore defaults

110

RESET QUERY CACHE;

111

DROP TABLE t1,t2;

112

SET GLOBAL concurrent_insert= DEFAULT;

113

SET GLOBAL query_cache_size= DEFAULT;

114

SET GLOBAL query_cache_type= DEFAULT;

115

116

117

--echo #

118

--echo # Bug43758 Query cache can lock up threads in 'freeing items' state

119

--echo #

120

FLUSH STATUS;

121

SET GLOBAL query_cache_type=DEMAND;

122

SET GLOBAL query_cache_size= 1024*768;

123

--disable_warnings

124

DROP TABLE IF EXISTS t1,t2,t3,t4,t5;

125

--enable_warnings

126

CREATE TABLE t1 (a VARCHAR(100));

127

CREATE TABLE t2 (a VARCHAR(100));

128

CREATE TABLE t3 (a VARCHAR(100));

129

CREATE TABLE t4 (a VARCHAR(100));

130

CREATE TABLE t5 (a VARCHAR(100));

131

132

INSERT INTO t1 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

133

INSERT INTO t2 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

134

INSERT INTO t3 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

135

INSERT INTO t4 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

136

INSERT INTO t5 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

137

138

connect (thd2, localhost, root, ,test);

139

connect (thd3, localhost, root, ,test);

140

connect (thd1, localhost, root, ,test);

141

142

connection thd1;

143

--echo =================================== Connection thd1

144

--echo **

145

--echo ** Load Query Cache with a result set and one table.

146

--echo **

147

SELECT SQL_CACHE * FROM t1;

148

--echo *************************************************************************

149

--echo ** We want to accomplish the following state:

150

--echo ** - Query cache status: TABLE_FLUSH_IN_PROGRESS

151

--echo ** - THD1: invalidate_table_internal (iterating query blocks)

152

--echo ** - THD2: query_cache_insert (cond_wait)

153

--echo ** - THD3: query_cache_insert (cond_wait)

154

--echo ** - No thread should be holding the structure_guard_mutex.

155

--echo **

156

--echo ** First step is to place a DELETE-statement on the debug hook just

157

--echo ** before the mutex lock in invalidate_table_internal.

158

--echo ** This will allow new result sets to be written into the QC.

159

--echo **

160

SET SESSION debug='+d,wait_in_query_cache_invalidate1';

161

SET SESSION debug='+d,wait_in_query_cache_invalidate2';

162

--send DELETE FROM t1 WHERE a like '%a%';

163

164

connection default;

165

--echo =================================== Connection default

166

--echo ** Assert that the expect process status is obtained.

167

LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'wait_in_query_cache_invalidate1';

168

--source include/wait_condition.inc

169

-- echo **

170

171

connection thd2;

172

--echo =================================== Connection thd2

173

--echo ** On THD2: Insert a result into the cache. This attempt will be blocked

174

--echo ** because of a debug hook placed just before the mutex lock after which

175

--echo ** the first part of the result set is written.

176

SET SESSION debug='+d,wait_in_query_cache_insert';

177

--send SELECT SQL_CACHE * FROM t2 UNION SELECT * FROM t3

178

179

connection thd3;

180

--echo =================================== Connection thd3

181

--echo ** On THD3: Insert another result into the cache and block on the same

182

--echo ** debug hook.

183

SET SESSION debug='+d,wait_in_query_cache_insert';

184

--send SELECT SQL_CACHE * FROM t4 UNION SELECT * FROM t5;

185

186

connection default;

187

--echo =================================== Connection default

188

--echo ** Assert that the two SELECT-stmt threads to reach the hook.

189

LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 2 FROM information_schema.processlist WHERE state='wait_in_query_cache_insert';

190

--source include/wait_condition.inc

191

--echo **

192

--echo **

193

194

--echo ** Signal the DELETE thread, THD1, to continue. It will enter the mutex

195

--echo ** lock and set query cache status to TABLE_FLUSH_IN_PROGRESS and then

196

--echo ** unlock the mutex before stopping on the next debug hook.

197

SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate1' LIMIT 1 INTO @flush_thread_id;

198

KILL QUERY @flush_thread_id;

199

--echo ** Assert that we reach the next debug hook.

200

LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 1 FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2';

201

--source include/wait_condition.inc

202

203

--echo **

204

--echo ** Signal the remaining debug hooks blocking THD2 and THD3.

205

--echo ** The threads will grab the guard mutex enter the wait condition and

206

--echo ** and finally release the mutex. The threads will continue to wait

207

--echo ** until a broadcast signal reaches them causing both threads to

208

--echo ** come alive and check the condition.

209

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;

210

KILL QUERY @thread_id;

211

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;

212

KILL QUERY @thread_id;

213

214

--echo **

215

--echo ** Finally signal the DELETE statement on THD1 one last time.

216

--echo ** The stmt will complete the query cache invalidation and return

217

--echo ** cache status to NO_FLUSH_IN_PROGRESS. On the status change

218

--echo ** One signal will be sent to the thread group waiting for executing

219

--echo ** invalidations and a broadcast signal will be sent to the thread

220

--echo ** group holding result set writers.

221

SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2' LIMIT 1 INTO @flush_thread_id;

222

KILL QUERY @flush_thread_id;

223

224

--echo **

225

--echo *************************************************************************

226

--echo ** No tables should be locked

227

connection thd2;

228

--echo =================================== Connection thd2

229

reap;

230

DELETE FROM t1;

231

DELETE FROM t2;

232

DELETE FROM t3;

233

234

connection thd3;

235

--echo =================================== Connection thd3

236

reap;

237

DELETE FROM t4;

238

DELETE FROM t5;

239

240

connection thd1;

241

--echo =================================== Connection thd1

242

reap;

243

244

--echo ** Done.

245

246

connection default;

247

disconnect thd1;

248

disconnect thd2;

249

disconnect thd3;

250

SET GLOBAL query_cache_size= 0;

251

252

connection default;

253

--echo # Restore defaults

254

RESET QUERY CACHE;

255

FLUSH STATUS;

256

DROP TABLE t1,t2,t3,t4,t5;

257

SET GLOBAL query_cache_size= DEFAULT;

258

SET GLOBAL query_cache_type= DEFAULT;

259

exit;

Older »