1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
# Test query cache with many tables
set @save_query_cache_size=@@global.query_cache_size;
set @save_query_cache_type=@@global.query_cache_type;
SET GLOBAL query_cache_type=ON;
SET LOCAL query_cache_type=ON;
--source include/have_query_cache.inc
let $LIMIT=`SHOW VARIABLES LIKE 'open_files_limit'`;
let $MIN_LIMIT=600;
let $MAX_LIMIT=65536;
--source include/check_var_limit.inc
SET @@global.query_cache_size=1355776;
#
# more then 255 (257) merged tables test
#
flush status;
disable_query_log;
--disable_warnings
let $1 = 257;
while ($1)
{
eval drop table if exists t$1;
eval create table t$1(a int) ENGINE=MyISAM;
eval insert into t$1 values (1),(2);
dec $1;
}
--enable_warnings
#
# In order for the test to pass in --ps-protocol, we must
# set table_definition_cache size to at least 258 elements.
# Otherwise table versions are bound to change between
# prepare and execute, and we will get a constant validation
# error. See WL#4165 for details.
#
--disable_ps2_protocol
--disable_view_protocol
set @save_table_definition_cache= @@global.table_definition_cache;
set @@global.table_definition_cache=512;
create table t00 (a int) engine=MERGE UNION=(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257) INSERT_METHOD=FIRST;
enable_query_log;
select count(*) from t00;
select count(*) from t00;
show status like "Qcache_queries_in_cache";
show status like "Qcache_hits";
delete from t256;
show status like "Qcache_queries_in_cache";
drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257,t00;
SET @@global.query_cache_size=0;
set @@global.table_definition_cache=@save_table_definition_cache;
--enable_view_protocol
--enable_ps2_protocol
# End of 4.1 tests
#
# Bug#33362: Query cache invalidation (truncate) may hang if cached query uses many tables
#
SET @save_table_definition_cache = @@global.table_definition_cache;
SET @@global.table_definition_cache = 512;
let $c= 255;
while ($c)
{
eval CREATE TABLE t$c (a INT);
eval INSERT INTO t$c VALUES ($c);
dec $c;
}
let $c= 254;
let $str= t255;
while ($c)
{
let $str= t$c,$str;
dec $c;
}
eval CREATE TABLE t0 (a INT) ENGINE=MERGE UNION($str);
SET GLOBAL query_cache_size = 1048576;
FLUSH STATUS;
--disable_view_protocol
SELECT a FROM t0 WHERE a = 1;
SHOW STATUS LIKE "Qcache_queries_in_cache";
--enable_view_protocol
let $c= 255;
let $i= 1;
while ($c)
{
eval TRUNCATE TABLE t$c;
eval SELECT a FROM t$i;
dec $c;
inc $i;
}
SELECT a FROM t0;
DROP TABLE t0;
let $c= 255;
while ($c)
{
eval DROP TABLE t$c;
dec $c;
}
SET @@global.query_cache_size = @save_query_cache_size;
SET @@global.table_definition_cache = @save_table_definition_cache;
SET @@global.query_cache_type = @save_query_cache_type;
--echo End of 5.1 tests
|
