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
|
CREATE TABLE stats_rename1 (a INT PRIMARY KEY, b INT UNIQUE)
ENGINE=INNODB STATS_PERSISTENT=1;
BEGIN;
INSERT INTO mysql.innodb_table_stats
SELECT
database_name,
'stats_rename2' AS table_name,
last_update,
123 AS n_rows,
clustered_index_size,
sum_of_other_index_sizes
FROM mysql.innodb_table_stats
WHERE table_name = 'stats_rename1';
INSERT INTO mysql.innodb_index_stats
SELECT
database_name,
'stats_rename2' AS table_name,
index_name,
last_update,
stat_name,
567 AS stat_value,
sample_size,
stat_description
FROM mysql.innodb_index_stats
WHERE table_name = 'stats_rename1';
COMMIT;
SELECT table_name, n_rows
FROM mysql.innodb_table_stats
WHERE table_name IN ('stats_rename1', 'stats_rename2');
table_name stats_rename1
n_rows 0
table_name stats_rename2
n_rows 123
SELECT table_name, index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE table_name IN ('stats_rename1', 'stats_rename2');
table_name stats_rename1
index_name PRIMARY
stat_name n_diff_pfx01
stat_value 0
table_name stats_rename1
index_name PRIMARY
stat_name n_leaf_pages
stat_value 1
table_name stats_rename1
index_name PRIMARY
stat_name size
stat_value 1
table_name stats_rename1
index_name b
stat_name n_diff_pfx01
stat_value 0
table_name stats_rename1
index_name b
stat_name n_leaf_pages
stat_value 1
table_name stats_rename1
index_name b
stat_name size
stat_value 1
table_name stats_rename2
index_name PRIMARY
stat_name n_diff_pfx01
stat_value 567
table_name stats_rename2
index_name PRIMARY
stat_name n_leaf_pages
stat_value 567
table_name stats_rename2
index_name PRIMARY
stat_name size
stat_value 567
table_name stats_rename2
index_name b
stat_name n_diff_pfx01
stat_value 567
table_name stats_rename2
index_name b
stat_name n_leaf_pages
stat_value 567
table_name stats_rename2
index_name b
stat_name size
stat_value 567
RENAME TABLE stats_rename1 TO stats_rename2;
ERROR 23000: Can't write; duplicate key in table 'mysql.innodb_table_stats'
BEGIN;
DELETE FROM mysql.innodb_table_stats WHERE table_name='stats_rename2';
DELETE FROM mysql.innodb_index_stats WHERE table_name='stats_rename2';
COMMIT;
RENAME TABLE stats_rename1 TO stats_rename2;
UPDATE mysql.innodb_index_stats SET index_name='c'
WHERE table_name='stats_rename2' AND index_name='PRIMARY';
ALTER TABLE stats_rename2 CHANGE b d INT, RENAME INDEX b TO c;
ERROR 23000: Can't write; duplicate key in table 'mysql.innodb_index_stats'
UPDATE mysql.innodb_index_stats SET index_name='PRIMARY'
WHERE table_name='stats_rename2' AND index_name='c';
ALTER TABLE stats_rename2 CHANGE b d INT, RENAME INDEX b TO c;
SELECT table_name, n_rows
FROM mysql.innodb_table_stats
WHERE table_name IN ('stats_rename1', 'stats_rename2');
table_name stats_rename2
n_rows 0
SELECT table_name, index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE table_name IN ('stats_rename1', 'stats_rename2');
table_name stats_rename2
index_name PRIMARY
stat_name n_diff_pfx01
stat_value 0
table_name stats_rename2
index_name PRIMARY
stat_name n_leaf_pages
stat_value 1
table_name stats_rename2
index_name PRIMARY
stat_name size
stat_value 1
table_name stats_rename2
index_name c
stat_name n_diff_pfx01
stat_value 0
table_name stats_rename2
index_name c
stat_name n_leaf_pages
stat_value 1
table_name stats_rename2
index_name c
stat_name size
stat_value 1
DROP TABLE stats_rename2;
|
