1
---- establish connection con1 (root) ----
2
---- switch to connection default ----
3
set @initial_query_cache_size = @@global.query_cache_size;
4
set @@global.query_cache_size=100000;
6
drop table if exists t1;
7
create table t1(c1 int);
8
insert into t1 values(1),(10),(100);
9
prepare stmt1 from "select * from t1 where c1=10";
10
show status like 'Qcache_hits';
16
show status like 'Qcache_hits';
22
show status like 'Qcache_hits';
28
show status like 'Qcache_hits';
31
prepare stmt2 from "select * from t1 where c1=10";
35
show status like 'Qcache_hits';
41
show status like 'Qcache_hits';
47
show status like 'Qcache_hits';
50
---- switch to connection con1 ----
51
prepare stmt3 from "select * from t1 where c1=10";
55
show status like 'Qcache_hits';
61
show status like 'Qcache_hits';
67
show status like 'Qcache_hits';
70
---- switch to connection default ----
71
prepare stmt10 from "SELECT * FROM t1 WHERE c1 = 100";
72
show status like 'Qcache_hits';
78
show status like 'Qcache_hits';
84
show status like 'Qcache_hits';
87
SELECT * FROM t1 WHERE c1 = 100;
90
show status like 'Qcache_hits';
93
---- switch to connection con1 ----
94
SELECT * FROM t1 WHERE c1 = 100;
97
show status like 'Qcache_hits';
100
---- switch to connection default ----
101
prepare stmt11 from "SELECT * FROM t1 WHERE c1 = 1";
102
---- switch to connection con1 ----
103
prepare stmt12 from "SELECT * FROM t1 WHERE c1 = 1";
104
---- switch to connection default ----
105
SELECT * FROM t1 WHERE c1 = 1;
108
show status like 'Qcache_hits';
111
SELECT * FROM t1 WHERE c1 = 1;
114
show status like 'Qcache_hits';
120
show status like 'Qcache_hits';
123
---- switch to connection con1 ----
127
show status like 'Qcache_hits';
130
---- switch to connection default ----
131
prepare stmt1 from "select * from t1 where c1=?";
132
show status like 'Qcache_hits';
136
execute stmt1 using @a;
139
show status like 'Qcache_hits';
142
execute stmt1 using @a;
145
show status like 'Qcache_hits';
148
---- switch to connection con1 ----
150
prepare stmt4 from "select * from t1 where c1=?";
151
execute stmt4 using @a;
154
show status like 'Qcache_hits';
157
prepare stmt4 from "select @a from t1 where c1=?";
158
execute stmt4 using @a;
161
show status like 'Qcache_hits';
164
execute stmt4 using @a;
167
show status like 'Qcache_hits';
170
---- switch to connection default ----
171
prepare stmt1 from "select * from t1 where c1=10";
172
set global query_cache_size=0;
173
show status like 'Qcache_hits';
179
show status like 'Qcache_hits';
185
show status like 'Qcache_hits';
191
show status like 'Qcache_hits';
194
---- switch to connection con1 ----
198
show status like 'Qcache_hits';
204
show status like 'Qcache_hits';
210
show status like 'Qcache_hits';
213
---- switch to connection default ----
214
set global query_cache_size=100000;
218
show status like 'Qcache_hits';
224
show status like 'Qcache_hits';
230
show status like 'Qcache_hits';
233
---- switch to connection con1 ----
237
show status like 'Qcache_hits';
243
show status like 'Qcache_hits';
249
show status like 'Qcache_hits';
252
---- switch to connection default ----
253
set global query_cache_size=0;
254
show status like 'Qcache_hits';
260
show status like 'Qcache_hits';
266
show status like 'Qcache_hits';
272
show status like 'Qcache_hits';
275
---- switch to connection con1 ----
279
show status like 'Qcache_hits';
285
show status like 'Qcache_hits';
291
show status like 'Qcache_hits';
294
---- switch to connection default ----
295
set global query_cache_size=0;
296
prepare stmt1 from "select * from t1 where c1=10";
297
---- switch to connection con1 ----
298
prepare stmt3 from "select * from t1 where c1=10";
299
---- switch to connection default ----
300
set global query_cache_size=100000;
301
show status like 'Qcache_hits';
307
show status like 'Qcache_hits';
313
show status like 'Qcache_hits';
319
show status like 'Qcache_hits';
322
---- switch to connection con1 ----
323
show status like 'Qcache_hits';
329
show status like 'Qcache_hits';
335
show status like 'Qcache_hits';
341
show status like 'Qcache_hits';
344
---- switch to connection default ----
345
set global query_cache_size=0;
346
prepare stmt1 from "select * from t1 where c1=?";
347
set global query_cache_size=100000;
348
show status like 'Qcache_hits';
352
execute stmt1 using @a;
355
show status like 'Qcache_hits';
359
execute stmt1 using @a;
362
show status like 'Qcache_hits';
366
execute stmt1 using @a;
369
show status like 'Qcache_hits';
373
---- disconnect connection con1 ----
374
########################################################################
376
# BUG#25843: Changing default database between PREPARE and EXECUTE of
377
# statement breaks binlog.
379
########################################################################
382
# Check that default database and its attributes are fixed at the
386
DROP DATABASE IF EXISTS mysqltest1;
387
DROP DATABASE IF EXISTS mysqltest2;
389
CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci;
390
CREATE DATABASE mysqltest2 COLLATE utf8_general_ci;
392
CREATE TABLE mysqltest1.t1(msg VARCHAR(255));
393
CREATE TABLE mysqltest2.t1(msg VARCHAR(255));
396
PREPARE stmt_a_1 FROM 'INSERT INTO t1 VALUES(DATABASE())';
397
PREPARE stmt_a_2 FROM 'INSERT INTO t1 VALUES(@@collation_database)';
406
SELECT * FROM mysqltest1.t1;
413
SELECT * FROM mysqltest2.t1;
416
DROP PREPARE stmt_a_1;
417
DROP PREPARE stmt_a_2;
420
# The Query Cache test case.
423
DELETE FROM mysqltest1.t1;
424
DELETE FROM mysqltest2.t1;
426
INSERT INTO mysqltest1.t1 VALUES('mysqltest1.t1');
427
INSERT INTO mysqltest2.t1 VALUES('mysqltest2.t1');
430
PREPARE stmt_b_1 FROM 'SELECT * FROM t1';
433
PREPARE stmt_b_2 FROM 'SELECT * FROM t1';
453
DROP PREPARE stmt_b_1;
454
DROP PREPARE stmt_b_2;
458
DROP DATABASE mysqltest1;
459
DROP DATABASE mysqltest2;
462
# Check that prepared statements work properly when there is no current
466
CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci;
467
CREATE DATABASE mysqltest2 COLLATE utf8_general_ci;
471
PREPARE stmt_c_1 FROM 'SELECT DATABASE(), @@collation_database';
475
PREPARE stmt_c_2 FROM 'SELECT DATABASE(), @@collation_database';
477
DROP DATABASE mysqltest2;
479
SELECT DATABASE(), @@collation_database;
480
DATABASE() @@collation_database
481
NULL latin1_swedish_ci
484
DATABASE() @@collation_database
485
mysqltest1 utf8_unicode_ci
487
SELECT DATABASE(), @@collation_database;
488
DATABASE() @@collation_database
489
NULL latin1_swedish_ci
492
DATABASE() @@collation_database
493
NULL latin1_swedish_ci
495
Note 1049 Unknown database 'mysqltest2'
497
SELECT DATABASE(), @@collation_database;
498
DATABASE() @@collation_database
499
NULL latin1_swedish_ci
501
PREPARE stmt_c_3 FROM 'SELECT DATABASE(), @@collation_database';
504
DATABASE() @@collation_database
505
NULL latin1_swedish_ci
510
DATABASE() @@collation_database
511
NULL latin1_swedish_ci
513
Note 1049 Unknown database 'mysqltest2'
515
SELECT DATABASE(), @@collation_database;
516
DATABASE() @@collation_database
517
mysqltest1 utf8_unicode_ci
520
DATABASE() @@collation_database
521
NULL latin1_swedish_ci
523
SELECT DATABASE(), @@collation_database;
524
DATABASE() @@collation_database
525
mysqltest1 utf8_unicode_ci
527
DROP DATABASE mysqltest1;
531
########################################################################
532
set @@global.query_cache_size=@initial_query_cache_size;