1
############### include/query_cache_sql_prepare.inc ################
3
# This is to see how statements prepared via the PREPARE SQL command
4
# go into the query cache.
5
# Query cache is abbreviated as "QC"
9
# - Add test coverage for automatic statement reprepare
11
# 2007-05-03 ML - Move t/query_cache_sql_prepare.test
12
# to include/query_cache_sql_prepare.inc
13
# - Create two toplevel tests sourcing this routine
14
# - Add tests checking that
15
# - another connection gets the same amount of QC hits
16
# - statements running via ps-protocol do not hit QC results
17
# of preceding sql EXECUTEs
20
--source include/have_query_cache.inc
21
# embedded can't make more than one connection, which this test needs
22
-- source include/not_embedded.inc
24
--echo ---- establish connection con1 (root) ----
25
connect (con1,localhost,root,,test,$MASTER_MYPORT,);
26
--echo ---- switch to connection default ----
29
set @initial_query_cache_size = @@global.query_cache_size;
30
set @@global.query_cache_size=102400;
33
drop table if exists t1;
35
create table t1(c1 int);
36
insert into t1 values(1),(10),(100);
38
# First, prepared statements with no parameters
39
prepare stmt1 from "select * from t1 where c1=10";
40
show status like 'Qcache_hits';
42
show status like 'Qcache_hits';
44
show status like 'Qcache_hits';
46
show status like 'Qcache_hits';
47
# Another prepared statement (same text, same connection), should hit the QC
48
prepare stmt2 from "select * from t1 where c1=10";
50
show status like 'Qcache_hits';
52
show status like 'Qcache_hits';
54
show status like 'Qcache_hits';
55
# Another prepared statement (same text, other connection), should hit the QC
56
--echo ---- switch to connection con1 ----
58
prepare stmt3 from "select * from t1 where c1=10";
60
show status like 'Qcache_hits';
62
show status like 'Qcache_hits';
64
show status like 'Qcache_hits';
65
--echo ---- switch to connection default ----
68
# Mixup tests, where statements without PREPARE.../EXECUTE.... meet statements
69
# with PREPARE.../EXECUTE.... (text protocol). Both statements have the
70
# same text. QC hits occur only when both statements use the same protocol.
71
# The outcome of the test depends on the mysqltest startup options
72
# - with "--ps-protocol"
73
# Statements without PREPARE.../EXECUTE.... run as prepared statements
74
# with binary protocol. Expect to get no QC hits.
75
# - without any "--<whatever>-protocol"
76
# Statements without PREPARE.../EXECUTE run as non prepared statements
77
# with text protocol. Expect to get QC hits.
78
############################################################################
80
# Statement with PREPARE.../EXECUTE.... first
81
let $my_stmt= SELECT * FROM t1 WHERE c1 = 100;
82
eval prepare stmt10 from "$my_stmt";
83
show status like 'Qcache_hits';
85
show status like 'Qcache_hits';
87
show status like 'Qcache_hits';
89
show status like 'Qcache_hits';
90
--echo ---- switch to connection con1 ----
93
show status like 'Qcache_hits';
94
--echo ---- switch to connection default ----
97
# Statement without PREPARE.../EXECUTE.... first
98
let $my_stmt= SELECT * FROM t1 WHERE c1 = 1;
99
eval prepare stmt11 from "$my_stmt";
100
--echo ---- switch to connection con1 ----
102
eval prepare stmt12 from "$my_stmt";
103
--echo ---- switch to connection default ----
106
show status like 'Qcache_hits';
108
show status like 'Qcache_hits';
110
show status like 'Qcache_hits';
111
--echo ---- switch to connection con1 ----
114
show status like 'Qcache_hits';
115
--echo ---- switch to connection default ----
118
# Query caching also works when statement has parameters
119
# (BUG#29318 Statements prepared with PREPARE and with one parameter don't use
121
prepare stmt1 from "select * from t1 where c1=?";
122
show status like 'Qcache_hits';
124
execute stmt1 using @a;
125
show status like 'Qcache_hits';
126
execute stmt1 using @a;
127
show status like 'Qcache_hits';
128
--echo ---- switch to connection con1 ----
131
prepare stmt4 from "select * from t1 where c1=?";
132
execute stmt4 using @a;
133
show status like 'Qcache_hits';
134
# verify that presence of user variables forbids caching
135
prepare stmt4 from "select @a from t1 where c1=?";
136
execute stmt4 using @a;
137
show status like 'Qcache_hits';
138
execute stmt4 using @a;
139
show status like 'Qcache_hits';
140
--echo ---- switch to connection default ----
143
# See if enabling/disabling the query cache between PREPARE and
144
# EXECUTE is an issue; the expected result is that the query cache
146
# Indeed, decision to read/write the query cache is taken at PREPARE
147
# time, so if the query cache was disabled at PREPARE time then no
148
# execution of the statement will read/write the query cache.
149
# If the query cache was enabled at PREPARE time, but disabled at
150
# EXECUTE time, at EXECUTE time the query cache internal functions do
151
# nothing so again the query cache is not read/written. But if the
152
# query cache is re-enabled before another execution then that
153
# execution will read/write the query cache.
155
# QC is enabled at PREPARE
156
prepare stmt1 from "select * from t1 where c1=10";
157
# then QC is disabled at EXECUTE
158
# Expect to see no additional Qcache_hits.
159
set global query_cache_size=0;
160
show status like 'Qcache_hits';
162
show status like 'Qcache_hits';
164
show status like 'Qcache_hits';
166
show status like 'Qcache_hits';
167
# The QC is global = affects also other connections.
168
# Expect to see no additional Qcache_hits.
169
--echo ---- switch to connection con1 ----
172
show status like 'Qcache_hits';
174
show status like 'Qcache_hits';
176
show status like 'Qcache_hits';
178
# then QC is re-enabled for more EXECUTE.
179
--echo ---- switch to connection default ----
181
set global query_cache_size=102400;
182
# Expect to see additional Qcache_hits.
183
# The fact that the QC was temporary disabled should have no affect
184
# except that the first execute will not hit results from the
185
# beginning of the test (because QC has been emptied meanwhile by
186
# setting its size to 0).
188
show status like 'Qcache_hits';
190
show status like 'Qcache_hits';
192
show status like 'Qcache_hits';
193
# The QC is global = affects also other connections.
194
--echo ---- switch to connection con1 ----
197
show status like 'Qcache_hits';
199
show status like 'Qcache_hits';
201
show status like 'Qcache_hits';
202
--echo ---- switch to connection default ----
205
# then QC is re-disabled for more EXECUTE.
206
# Expect to see no additional Qcache_hits.
207
# The fact that the QC was temporary enabled should have no affect.
208
set global query_cache_size=0;
209
show status like 'Qcache_hits';
211
show status like 'Qcache_hits';
213
show status like 'Qcache_hits';
215
show status like 'Qcache_hits';
216
# The QC is global = affects also other connections.
217
--echo ---- switch to connection con1 ----
220
show status like 'Qcache_hits';
222
show status like 'Qcache_hits';
224
show status like 'Qcache_hits';
227
--echo ---- switch to connection default ----
229
# QC is disabled at PREPARE
230
set global query_cache_size=0;
231
prepare stmt1 from "select * from t1 where c1=10";
232
--echo ---- switch to connection con1 ----
234
prepare stmt3 from "select * from t1 where c1=10";
235
--echo ---- switch to connection default ----
237
# then QC is enabled at EXECUTE
238
set global query_cache_size=102400;
239
show status like 'Qcache_hits';
241
show status like 'Qcache_hits';
243
show status like 'Qcache_hits';
245
show status like 'Qcache_hits';
246
# The QC is global = affects also other connections.
247
--echo ---- switch to connection con1 ----
249
show status like 'Qcache_hits';
251
show status like 'Qcache_hits';
253
show status like 'Qcache_hits';
255
show status like 'Qcache_hits';
256
--echo ---- switch to connection default ----
259
# QC is disabled at PREPARE
260
set global query_cache_size=0;
261
prepare stmt1 from "select * from t1 where c1=?";
262
# then QC is enabled at EXECUTE
263
set global query_cache_size=102400;
264
show status like 'Qcache_hits';
266
execute stmt1 using @a;
267
show status like 'Qcache_hits';
269
execute stmt1 using @a;
270
show status like 'Qcache_hits';
272
execute stmt1 using @a;
273
show status like 'Qcache_hits';
277
--echo ---- disconnect connection con1 ----
281
# Bug #25843 Changing default database between PREPARE and EXECUTE of statement
284
# There were actually two problems discovered by this bug:
286
# 1. Default (current) database is not fixed at the creation time.
287
# That leads to wrong output of DATABASE() function.
289
# 2. Database attributes (@@collation_database) are not fixed at the creation
290
# time. That leads to wrong resultset.
292
# Binlog breakage and Query Cache wrong output happened because of the first
296
--echo ########################################################################
298
--echo # BUG#25843: Changing default database between PREPARE and EXECUTE of
299
--echo # statement breaks binlog.
301
--echo ########################################################################
303
###############################################################################
307
--echo # Check that default database and its attributes are fixed at the
308
--echo # creation time.
311
# Prepare data structures.
315
DROP DATABASE IF EXISTS mysqltest1;
316
DROP DATABASE IF EXISTS mysqltest2;
320
CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci;
321
CREATE DATABASE mysqltest2 COLLATE utf8_general_ci;
324
CREATE TABLE mysqltest1.t1(msg VARCHAR(255));
325
CREATE TABLE mysqltest2.t1(msg VARCHAR(255));
327
# - Create a prepared statement with mysqltest1 as default database;
333
PREPARE stmt_a_1 FROM 'INSERT INTO t1 VALUES(DATABASE())';
334
PREPARE stmt_a_2 FROM 'INSERT INTO t1 VALUES(@@collation_database)';
336
# - Execute on mysqltest1.
343
# - Execute on mysqltest2.
352
# - Check the results;
355
SELECT * FROM mysqltest1.t1;
358
SELECT * FROM mysqltest2.t1;
360
# - Drop prepared statements.
363
DROP PREPARE stmt_a_1;
364
DROP PREPARE stmt_a_2;
366
###############################################################################
370
--echo # The Query Cache test case.
374
DELETE FROM mysqltest1.t1;
375
DELETE FROM mysqltest2.t1;
378
INSERT INTO mysqltest1.t1 VALUES('mysqltest1.t1');
379
INSERT INTO mysqltest2.t1 VALUES('mysqltest2.t1');
383
PREPARE stmt_b_1 FROM 'SELECT * FROM t1';
387
PREPARE stmt_b_2 FROM 'SELECT * FROM t1';
405
DROP PREPARE stmt_b_1;
406
DROP PREPARE stmt_b_2;
414
DROP DATABASE mysqltest1;
415
DROP DATABASE mysqltest2;
417
###############################################################################
421
--echo # Check that prepared statements work properly when there is no current
426
CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci;
427
CREATE DATABASE mysqltest2 COLLATE utf8_general_ci;
433
PREPARE stmt_c_1 FROM 'SELECT DATABASE(), @@collation_database';
439
PREPARE stmt_c_2 FROM 'SELECT DATABASE(), @@collation_database';
442
DROP DATABASE mysqltest2;
445
SELECT DATABASE(), @@collation_database;
447
# -- Here we have: current db: NULL; stmt db: mysqltest1;
452
SELECT DATABASE(), @@collation_database;
454
# -- Here we have: current db: NULL; stmt db: mysqltest2 (non-existent);
459
SELECT DATABASE(), @@collation_database;
461
# -- Create prepared statement, which has no current database.
464
PREPARE stmt_c_3 FROM 'SELECT DATABASE(), @@collation_database';
466
# -- Here we have: current db: NULL; stmt db: NULL;
473
# -- Here we have: current db: mysqltest1; stmt db: mysqltest2 (non-existent);
478
SELECT DATABASE(), @@collation_database;
480
# -- Here we have: current db: mysqltest1; stmt db: NULL;
485
SELECT DATABASE(), @@collation_database;
488
DROP DATABASE mysqltest1;
494
--echo ########################################################################
496
--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed
497
--echo # after PREPARE
498
--echo # Check the effect of automatic reprepare on query cache
500
--echo ########################################################################
502
drop table if exists t1;
504
create table t1 (a varchar(255));
505
insert into t1 (a) values ("Pack my box with five dozen liquor jugs.");
507
prepare stmt from "select a from t1";
509
set @@global.query_cache_size=0;
510
alter table t1 add column b int;
512
set @@global.query_cache_size=102400;
516
--echo # Sic: ALTER TABLE caused an automatic reprepare
517
--echo # of the prepared statement. Since the query cache was disabled
518
--echo # at the time of reprepare, the new prepared statement doesn't
519
--echo # work with it.
521
show status like 'Qcache_hits';
522
show status like 'Qcache_queries_in_cache';
524
deallocate prepare stmt;
527
###############################################################################
529
set @@global.query_cache_size=@initial_query_cache_size;
530
flush status; # reset Qcache status variables for next tests