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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
# Information schema dynamic metadata caching in mysql.table_stats
# and mysql.index_stats tables.
--source include/have_debug.inc
SET SESSION debug= '+d,skip_dd_table_access_check';
CREATE TABLE t1(a int,KEY(a));
# Select from I_S.tables should create entry in mysql.table_stats
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t1';
SELECT table_name, table_rows, avg_row_length, data_length, max_data_length,
index_length, data_free, auto_increment from mysql.table_stats where table_name='t1';
# Select from I_S.statistics should create entry in mysql.index_stats
SELECT TABLE_NAME,COLUMN_NAME, INDEX_NAME, CARDINALITY from
information_schema.statistics where table_name='t1' ORDER BY COLUMN_NAME;
SELECT table_name, column_name, index_name, cardinality from mysql.index_stats
where table_name='t1' ORDER bY column_name;
SET SESSION information_schema_stats_expiry=1;
INSERT INTO t1 VALUES(3);
SELECT SLEEP(2);
# Select from I_S.tables should update entry in mysql.table_stats
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t1';
SELECT table_name, table_rows, avg_row_length, data_length, max_data_length,
index_length, data_free, auto_increment from mysql.table_stats where table_name='t1';
# Select from I_S.statistics should update entry in mysql.index_stats
SELECT TABLE_NAME,COLUMN_NAME, INDEX_NAME, CARDINALITY from
information_schema.statistics where table_name='t1' ORDER BY COLUMN_NAME;
SELECT table_name, column_name, index_name, cardinality from mysql.index_stats
where table_name='t1' ORDER BY column_name;
DELETE FROM mysql.table_stats;
DELETE FROM mysql.index_stats;
DROP TABLE t1;
CREATE TABLE t2(a int,KEY(a));
CREATE TABLE t3(b int, KEY(b));
# Test with information_schema_stats_expiry=0
SET SESSION information_schema_stats_expiry=0;
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t2';
# Value should not be in mysql.table_stats
SELECT table_name, table_rows, avg_row_length, data_length, max_data_length,
index_length, data_free, auto_increment from mysql.table_stats where table_name='t2';
SET SESSION information_schema_stats_expiry=default;
# Tests with User Transaction ON AND COMMIT/ROLLBACK
# Rollback
START TRANSACTION;
INSERT INTO t2 VALUES(1);
# Select should write to mysql.table_stats but not commit change to t2
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t2';
ROLLBACK;
# Select should write to mysql.table_stats but not commit change to t2
SELECT * FROM t2;
SELECT table_name, table_rows, avg_row_length, data_length, max_data_length,
index_length, data_free, auto_increment from mysql.table_stats where table_name='t2';
# Commit
START TRANSACTION;
INSERT INTO t3 VALUES(1);
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t2';
COMMIT;
# Select should write to mysql.table_stats and t3
SELECT * FROM t3;
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT from
information_schema.tables WHERE table_name='t3';
DELETE FROM mysql.table_stats;
DELETE FROM mysql.index_stats;
DROP TABLE t2,t3;
SET SESSION information_schema_stats_expiry= default;
--echo # Bug#32338335 STOP UPDATING I_S DYNAMIC STATS CACHE
--echo # IN MULTI-STATEMENT TRANSACTIONS
# Test cases
# Case 1: Make sure I_S query under START TRANSACTION doesn't write to
# statistics cache.
# Case 2: Make sure I_S query under AUTOCOMMIT=0 doesn't write to
# statistics cache.
CREATE TABLE t1 (a INT);
--echo # CASE 1: Use START TRANSACTION
START TRANSACTION;
INSERT INTO t1 VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
--echo # Make sure that we see latest dynamic statistics and do not cache it.
SELECT table_rows FROM information_schema.tables WHERE table_name = "t1";
COMMIT;
--echo # Make sure that there is no dynamic statistics written
--echo # by previous I_S query.
SELECT schema_name, table_name, table_rows
FROM mysql.table_stats WHERE table_name="t1";
--echo # CASE 2: Use AUTOCOMMIT=0
SET AUTOCOMMIT=0;
INSERT INTO t1 VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
--echo # Make sure that we see latest dynamic statistics and do not cache it.
SELECT table_rows FROM information_schema.tables WHERE table_name = "t1";
COMMIT;
SET AUTOCOMMIT=1;
--echo # Make sure that there is no dynamic statistics written
--echo # by previous I_S query.
SELECT schema_name, table_name, table_rows
FROM mysql.table_stats WHERE table_name="t1";
--echo # Make sure that we see latest dynamic statistics and also cache it.
SELECT table_rows FROM information_schema.tables WHERE table_name = "t1";
--echo # Make sure that there is dynamic statistics written
--echo # by previous I_S query.
SELECT schema_name, table_name, table_rows
FROM mysql.table_stats WHERE table_name="t1";
DROP table t1;
set global debug=RESET;
|
