| 1 |
** Setup ** |
| 2 |
|
| 3 |
SET @global_query_cache_limit = @@global.query_cache_limit; |
| 4 |
SET @global_query_cache_size = @@global.query_cache_size; |
| 5 |
SET @global_query_cache_type = @@global.query_cache_type; |
| 6 |
SET GLOBAL query_cache_type= ON; |
| 7 |
SET LOCAL query_cache_type= ON; |
| 8 |
** warnings ** |
| 9 |
DROP TABLE IF EXISTS t; |
| 10 |
** creating table ** |
| 11 |
CREATE TABLE t |
| 12 |
( |
| 13 |
id INT AUTO_INCREMENT PRIMARY KEY, |
| 14 |
c TEXT(30) |
| 15 |
); |
| 16 |
**inserting value ** |
| 17 |
INSERT INTO t set c = repeat('x',29); |
| 18 |
INSERT INTO t set c = concat(repeat('x',28),'r','x'); |
| 19 |
INSERT INTO t set c = concat(repeat('x',28),'s','y'); |
| 20 |
INSERT INTO t set c = concat(repeat('x',28),'g','w'); |
| 21 |
** Reset cache values ** |
| 22 |
FLUSH GLOBAL STATUS; |
| 23 |
RESET QUERY CACHE; |
| 24 |
** On query_cache_type ** |
| 25 |
SET GLOBAL query_cache_type = ON; |
| 26 |
** Allocating cache size ** |
| 27 |
SET GLOBAL query_cache_size = 131072; |
| 28 |
** Reset values |
| 29 |
SET GLOBAL query_cache_size = 0; |
| 30 |
SET GLOBAL query_cache_size = 131072; |
| 31 |
SET GLOBAL query_cache_type = ON; |
| 32 |
'#---------------------FN_DYNVARS_132_01----------------------#' |
| 33 |
** Reset cache values ** |
| 34 |
FLUSH GLOBAL STATUS; |
| 35 |
RESET QUERY CACHE; |
| 36 |
** fetching results ** |
| 37 |
SELECT * FROM t; |
| 38 |
id c |
| 39 |
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 40 |
2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx |
| 41 |
3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy |
| 42 |
4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw |
| 43 |
** check status on not setting query_cache_limit value ** |
| 44 |
SHOW STATUS LIKE 'Qcache_not_cached'; |
| 45 |
Variable_name Value |
| 46 |
Qcache_not_cached 0 |
| 47 |
SHOW STATUS LIKE 'Qcache_queries_in_cache'; |
| 48 |
Variable_name Value |
| 49 |
Qcache_queries_in_cache 1 |
| 50 |
'#---------------------FN_DYNVARS_132_02----------------------#' |
| 51 |
** Reset cache values ** |
| 52 |
FLUSH GLOBAL STATUS; |
| 53 |
RESET QUERY CACHE; |
| 54 |
** set cache limit ** |
| 55 |
SET @@GLOBAL.query_cache_limit = 0; |
| 56 |
** fetching results ** |
| 57 |
SELECT * FROM t; |
| 58 |
id c |
| 59 |
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 60 |
2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx |
| 61 |
3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy |
| 62 |
4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw |
| 63 |
** Check status after setting value ** |
| 64 |
SHOW STATUS LIKE 'Qcache_not_cached'; |
| 65 |
Variable_name Value |
| 66 |
Qcache_not_cached 1 |
| 67 |
1 Expected |
| 68 |
SHOW STATUS LIKE 'Qcache_queries_in_cache'; |
| 69 |
Variable_name Value |
| 70 |
Qcache_queries_in_cache 0 |
| 71 |
0 Expected |
| 72 |
'#---------------------FN_DYNVARS_132_03----------------------#' |
| 73 |
** set cache limit ** |
| 74 |
SET @@GLOBAL.query_cache_limit = DEFAULT; |
| 75 |
** Reset cache values ** |
| 76 |
FLUSH GLOBAL STATUS; |
| 77 |
RESET QUERY CACHE; |
| 78 |
** fetching results ** |
| 79 |
SELECT * FROM t; |
| 80 |
id c |
| 81 |
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 82 |
2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx |
| 83 |
3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy |
| 84 |
4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw |
| 85 |
SHOW STATUS LIKE 'Qcache_not_cached'; |
| 86 |
Variable_name Value |
| 87 |
Qcache_not_cached 0 |
| 88 |
0 Expected |
| 89 |
SHOW STATUS LIKE 'Qcache_queries_in_cache'; |
| 90 |
Variable_name Value |
| 91 |
Qcache_queries_in_cache 1 |
| 92 |
1 Expected |
| 93 |
SET @@GLOBAL.query_cache_limit = 0; |
| 94 |
SHOW STATUS LIKE 'Qcache_not_cached'; |
| 95 |
Variable_name Value |
| 96 |
Qcache_not_cached 0 |
| 97 |
0 Expected |
| 98 |
SHOW STATUS LIKE 'Qcache_queries_in_cache'; |
| 99 |
Variable_name Value |
| 100 |
Qcache_queries_in_cache 1 |
| 101 |
1 Expected |
| 102 |
** fetching results ** |
| 103 |
SELECT * FROM t; |
| 104 |
id c |
| 105 |
1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 106 |
2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx |
| 107 |
3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy |
| 108 |
4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw |
| 109 |
** Check status after setting value ** |
| 110 |
SHOW STATUS LIKE 'Qcache_not_cached'; |
| 111 |
Variable_name Value |
| 112 |
Qcache_not_cached 0 |
| 113 |
0 Expected |
| 114 |
SHOW STATUS LIKE 'Qcache_queries_in_cache'; |
| 115 |
Variable_name Value |
| 116 |
Qcache_queries_in_cache 1 |
| 117 |
1 Expected |
| 118 |
SET @@GLOBAL.query_cache_limit = @global_query_cache_limit; |
| 119 |
SET GLOBAL query_cache_size = @global_query_cache_size; |
| 120 |
SET GLOBAL query_cache_type = @global_query_cache_type; |
| 121 |
DROP TABLE IF EXISTS t; |