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;
|