Debian Codegraph @

mysql-test/suite/innodb/r/alter_crash.result

raw
1 #
2 # Bug#20015132 ALTER TABLE FAILS TO CHECK IF TABLE IS CORRUPTED
3 #
4 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(1), c3 INT UNSIGNED) ENGINE=InnoDB;
5 ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);
6 SET DEBUG='+d,dict_set_index_corrupted';
7 CHECK TABLE t1;
8 Table Op Msg_type Msg_text
9 test.t1 check Warning InnoDB: The B-tree of index c2 is corrupted.
10 test.t1 check Warning InnoDB: The B-tree of index c3 is corrupted.
11 test.t1 check error Corrupt
12 # restart
13 CHECK TABLE t1;
14 Table Op Msg_type Msg_text
15 test.t1 check Warning InnoDB: Index c2 is marked as corrupted
16 test.t1 check Warning InnoDB: Index c3 is marked as corrupted
17 test.t1 check error Corrupt
18 ALTER TABLE t1 DROP INDEX c2;
19 CHECK TABLE t1;
20 Table Op Msg_type Msg_text
21 test.t1 check Warning InnoDB: Index c3 is marked as corrupted
22 test.t1 check error Corrupt
23 ALTER TABLE t1 ADD INDEX (c2,c3);
24 ERROR 42000: Can't open table
25 ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL;
26 CHECK TABLE t1;
27 Table Op Msg_type Msg_text
28 test.t1 check status OK
29 ALTER TABLE t1 ADD INDEX (c2,c3);
30 DROP TABLE t1;
31 #
32 # Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE
33 #
34 # -- Scenario 1:
35 # Crash the server in ha_innobase::commit_inplace_alter_table()
36 # just after committing the dictionary changes.
37 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb;
38 INSERT INTO t1 VALUES (1,2),(3,4);
39 SET DEBUG='d,innodb_alter_commit_crash_after_commit';
40 ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
41 ERROR HY000: Lost connection to MySQL server during query
42 # Restart mysqld after the crash and reconnect.
43 # restart
44 # Files in datadir after manual recovery.
45 t1.ibd
46 SHOW TABLES;
47 Tables_in_test
48 t1
49 SHOW CREATE TABLE t1;
50 Table Create Table
51 t1 CREATE TABLE `t1` (
52 `f1` int NOT NULL,
53 `f2` int NOT NULL
54 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
55 INSERT INTO t1 VALUES (5,6),(7,8);
56 SELECT * FROM t1;
57 f1 f2
58 1 2
59 3 4
60 5 6
61 7 8
62 DROP TABLE t1;
63 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
64 ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
65 DROP TABLE t1;
66 # -- Scenario 2:
67 # Crash the server in ha_innobase::commit_inplace_alter_table()
68 # just before committing the dictionary changes, but after
69 # writing the MLOG_FILE_RENAME records. As the mini-transaction
70 # is not committed, the renames will not be replayed.
71 CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB;
72 INSERT INTO t2 VALUES (1,2),(3,4);
73 SET DEBUG='d,innodb_alter_commit_crash_before_commit';
74 ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
75 ERROR HY000: Lost connection to MySQL server during query
76 # Startup the server after the crash
77 # restart
78 SHOW TABLES;
79 Tables_in_test
80 t2
81 INSERT INTO t2 VALUES (5,6),(7,8);
82 SELECT * from t2;
83 f1 f2
84 1 2
85 3 4
86 5 6
87 7 8
88 SHOW CREATE TABLE t2;
89 Table Create Table
90 t2 CREATE TABLE `t2` (
91 `f1` int NOT NULL,
92 `f2` int NOT NULL
93 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
94 DROP TABLE t2;
95 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
96 ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
97 DROP TABLE t2;
98 # -------------------------
99 # End of Testing Scenario 2
100 # -------------------------
101 #
102 # Bug#19330255 CRASH DURING ALTER TABLE LEADS TO
103 # DATA DICTIONARY INCONSISTENCY
104 #
105 CREATE TABLE t1(a int PRIMARY KEY, b varchar(150), c int NOT NULL);
106 INSERT INTO t1 SET a=1,c=2;
107 SET DEBUG='d,innodb_alter_commit_crash_after_commit';
108 ALTER TABLE t1 ADD INDEX (b), CHANGE c d int;
109 ERROR HY000: Lost connection to MySQL server during query
110 # Restart mysqld after the crash and reconnect.
111 # restart
112 # Files in datadir after manual recovery.
113 t1.ibd
114 SHOW TABLES;
115 Tables_in_test
116 t1
117 SHOW CREATE TABLE t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `a` int NOT NULL,
121 `b` varchar(150) DEFAULT NULL,
122 `c` int NOT NULL,
123 PRIMARY KEY (`a`)
124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
125 SELECT * FROM t1;
126 a b c
127 1 NULL 2
128 DROP TABLE t1;