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
|
CREATE DATABASE unlocked;
CREATE TABLE unlocked.t1(a INT PRIMARY KEY) ENGINE=INNODB STATS_PERSISTENT=0;
CREATE DATABASE locked;
CREATE TABLE locked.t1(a INT PRIMARY KEY) ENGINE=INNODB STATS_PERSISTENT=1;
CREATE TABLE innodb_stats_drop_locked (c INT, KEY c_key (c))
ENGINE=INNODB STATS_PERSISTENT=1;
ANALYZE TABLE innodb_stats_drop_locked;
Table Op Msg_type Msg_text
test.innodb_stats_drop_locked analyze status Engine-independent statistics collected
test.innodb_stats_drop_locked analyze status OK
BEGIN;
SELECT table_name FROM mysql.innodb_table_stats
WHERE table_name='innodb_stats_drop_locked'
FOR UPDATE;
table_name
innodb_stats_drop_locked
SELECT table_name FROM mysql.innodb_index_stats
WHERE table_name='innodb_stats_drop_locked'
FOR UPDATE;
table_name
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
connect con1,localhost,root,,;
SET innodb_lock_wait_timeout=1;
ALTER TABLE innodb_stats_drop_locked DROP INDEX c_key;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE innodb_stats_drop_locked;
Table Create Table
innodb_stats_drop_locked CREATE TABLE `innodb_stats_drop_locked` (
`c` int(11) DEFAULT NULL,
KEY `c_key` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1
DROP TABLE innodb_stats_drop_locked;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DROP DATABASE unlocked;
DROP DATABASE locked;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
disconnect con1;
connection default;
COMMIT;
DROP DATABASE locked;
SELECT table_name FROM mysql.innodb_table_stats
WHERE table_name='innodb_stats_drop_locked';
table_name
innodb_stats_drop_locked
SELECT table_name FROM mysql.innodb_index_stats
WHERE table_name='innodb_stats_drop_locked';
table_name
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
ALTER TABLE innodb_stats_drop_locked DROP INDEX c_key;
SELECT table_name FROM mysql.innodb_index_stats
WHERE table_name='innodb_stats_drop_locked';
table_name
innodb_stats_drop_locked
innodb_stats_drop_locked
innodb_stats_drop_locked
DROP TABLE innodb_stats_drop_locked;
SELECT table_name FROM mysql.innodb_table_stats
WHERE table_name='innodb_stats_drop_locked';
table_name
SELECT table_name FROM mysql.innodb_index_stats
WHERE table_name='innodb_stats_drop_locked';
table_name
|
