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 SET @saved_debug_dbug = @@SESSION.debug_dbug;
6 SET DEBUG_DBUG='+d,create_index_metadata_fail';
7 ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);
8 ERROR HY000: The table 't1' is full
9 SET DEBUG_DBUG='+d,ib_create_table_fail_too_many_trx';
10 ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);
11 ERROR HY000: Too many active concurrent transactions
12 SET DEBUG_DBUG=@saved_debug_dbug;
13 ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);
14 SET DEBUG_DBUG='+d,dict_set_index_corrupted';
15 CHECK TABLE t1;
16 Table Op Msg_type Msg_text
17 test.t1 check Warning InnoDB: Index c2 is marked as corrupted
18 test.t1 check Warning InnoDB: Index c3 is marked as corrupted
19 test.t1 check error Corrupt
20 # restart
21 CHECK TABLE t1;
22 Table Op Msg_type Msg_text
23 test.t1 check Warning InnoDB: Index c2 is marked as corrupted
24 test.t1 check Warning InnoDB: Index c3 is marked as corrupted
25 test.t1 check error Corrupt
26 ALTER TABLE t1 DROP INDEX c2;
27 CHECK TABLE t1;
28 Table Op Msg_type Msg_text
29 test.t1 check Warning InnoDB: Index c3 is marked as corrupted
30 test.t1 check error Corrupt
31 ALTER TABLE t1 ADD INDEX (c2,c3);
32 ERROR HY000: Index c3 is corrupted
33 ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL;
34 CHECK TABLE t1;
35 Table Op Msg_type Msg_text
36 test.t1 check status OK
37 ALTER TABLE t1 ADD INDEX (c2,c3);
38 DROP TABLE t1;
39 #
40 # Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE
41 #
42 # -- Scenario 1:
43 # Crash the server in ha_innobase::commit_inplace_alter_table()
44 # just after committing the dictionary changes.
45 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb;
46 INSERT INTO t1 VALUES (1,2),(3,4);
47 SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';
48 ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
49 ERROR HY000: Lost connection to server during query
50 # Restart mysqld after the crash and reconnect.
51 # restart
52 SELECT * FROM information_schema.innodb_sys_tables
53 WHERE table_id = ID;
54 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
55 # Files in datadir after manual recovery.
56 db.opt
57 t1.frm
58 t1.ibd
59 SHOW TABLES;
60 Tables_in_test
61 t1
62 SHOW CREATE TABLE t1;
63 Table Create Table
64 t1 CREATE TABLE `t1` (
65 `f1` int(11) NOT NULL,
66 `f2` int(11) NOT NULL,
67 PRIMARY KEY (`f2`,`f1`)
68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
69 INSERT INTO t1 VALUES (5,6),(7,8);
70 SELECT * FROM t1;
71 f1 f2
72 1 2
73 3 4
74 5 6
75 7 8
76 DROP TABLE t1;
77 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
78 ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
79 DROP TABLE t1;
80 # -- Scenario 2:
81 # Crash the server in ha_innobase::commit_inplace_alter_table()
82 # just before committing the dictionary changes, but after
83 # writing the MLOG_FILE_RENAME records. As the mini-transaction
84 # is not committed, the renames will not be replayed.
85 CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB;
86 INSERT INTO t2 VALUES (1,2),(3,4);
87 SET DEBUG_DBUG='+d,innodb_alter_commit_crash_before_commit';
88 ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
89 ERROR HY000: Lost connection to server during query
90 # Startup the server after the crash
91 # restart
92 SELECT * FROM information_schema.innodb_sys_tables
93 WHERE name LIKE 'test/#sql-%';
94 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
95 SHOW TABLES;
96 Tables_in_test
97 t2
98 INSERT INTO t2 VALUES (5,6),(7,8);
99 SELECT * from t2;
100 f1 f2
101 1 2
102 3 4
103 5 6
104 7 8
105 SHOW CREATE TABLE t2;
106 Table Create Table
107 t2 CREATE TABLE `t2` (
108 `f1` int(11) NOT NULL,
109 `f2` int(11) NOT NULL
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
111 DROP TABLE t2;
112 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
113 ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
114 DROP TABLE t2;
115 db.opt
116 # -------------------------
117 # End of Testing Scenario 2
118 # -------------------------
119 #
120 # Bug#19330255 WL#7142 - CRASH DURING ALTER TABLE LEADS TO
121 # DATA DICTIONARY INCONSISTENCY
122 #
123 CREATE TABLE t1(a int PRIMARY KEY, b varchar(255), c int NOT NULL)
124 ENGINE=InnoDB;
125 INSERT INTO t1 SET a=1,c=2;
126 SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';
127 ALTER TABLE t1 ADD INDEX (b), CHANGE c d int, ALGORITHM=INPLACE;
128 ERROR HY000: Lost connection to server during query
129 # Restart mysqld after the crash and reconnect.
130 # restart
131 SELECT * FROM information_schema.innodb_sys_tables
132 WHERE table_id = ID;
133 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
134 db.opt
135 t1.frm
136 t1.ibd
137 SHOW TABLES;
138 Tables_in_test
139 t1
140 SHOW CREATE TABLE t1;
141 Table Create Table
142 t1 CREATE TABLE `t1` (
143 `a` int(11) NOT NULL,
144 `b` varchar(255) DEFAULT NULL,
145 `d` int(11) DEFAULT NULL,
146 PRIMARY KEY (`a`),
147 KEY `b` (`b`)
148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
149 UPDATE t1 SET d=NULL;
150 SELECT * FROM t1;
151 a b d
152 1 NULL NULL
153 DROP TABLE t1;
154 #
155 # MDEV-22637 Rollback of insert fails when column reorder happens
156 #
157 SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'STRICT_TRANS_TABLES', '');
158 SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'STRICT_ALL_TABLES', '');
159 CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(100),
160 f3 CHAR(100), f4 CHAR(100))ENGINE=InnoDB;
161 INSERT INTO t1 VALUES(1, "This is column2", "This is column3",
162 "This is column4");
163 set DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
164 ALTER TABLE t1 ADD COLUMN f6 int after f3, add primary key(f6, f4(3), f3(3));
165 connect con1,localhost,root,,;
166 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
167 BEGIN;
168 INSERT INTO t1(f1, f2) VALUES(2, "This is column2 value");
169 ROLLBACK;
170 set DEBUG_SYNC = 'now SIGNAL insert_done';
171 connection default;
172 SHOW CREATE TABLE t1;
173 Table Create Table
174 t1 CREATE TABLE `t1` (
175 `f1` int(11) NOT NULL,
176 `f2` char(100) DEFAULT NULL,
177 `f3` char(100) NOT NULL,
178 `f6` int(11) NOT NULL,
179 `f4` char(100) NOT NULL,
180 PRIMARY KEY (`f6`,`f4`(3),`f3`(3))
181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
182 SELECT COUNT(*) FROM t1;
183 COUNT(*)
184 1
185 disconnect con1;
186 DROP TABLE t1;
187 SET DEBUG_SYNC = 'RESET';
188 SET SQL_MODE=DEFAULT;
189 #
190 # MDEV-26936 Recovery crash on rolling back DELETE FROM SYS_INDEXES
191 #
192 CREATE TABLE t1(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
193 INSERT INTO t1 VALUES(1,1);
194 connect ddl, localhost, root;
195 SET DEBUG_SYNC = 'row_merge_after_scan SIGNAL scanned WAIT_FOR commit';
196 SET DEBUG_SYNC = 'before_commit_rollback_inplace SIGNAL c WAIT_FOR ever';
197 ALTER TABLE t1 ADD UNIQUE INDEX(b), ALGORITHM=INPLACE;
198 connection default;
199 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
200 BEGIN;
201 INSERT INTO t1 VALUES(2,1);
202 COMMIT;
203 SET DEBUG_SYNC = 'now SIGNAL commit';
204 SET DEBUG_SYNC = 'now WAIT_FOR c';
205 SET GLOBAL innodb_fil_make_page_dirty_debug=0;
206 # Kill the server
207 disconnect ddl;
208 # restart
209 CHECK TABLE t1;
210 Table Op Msg_type Msg_text
211 test.t1 check status OK
212 SHOW CREATE TABLE t1;
213 Table Create Table
214 t1 CREATE TABLE `t1` (
215 `a` int(11) NOT NULL,
216 `b` int(11) DEFAULT NULL,
217 PRIMARY KEY (`a`)
218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
219 SELECT * FROM t1;
220 a b
221 1 1
222 2 1
223 connect ddl, localhost, root;
224 SET DEBUG_SYNC = 'fil_ibd_create_logged SIGNAL stuck WAIT_FOR ever';
225 ALTER TABLE t1 FORCE;
226 connection default;
227 SET DEBUG_SYNC = 'now WAIT_FOR stuck';
228 # Kill the server
229 disconnect ddl;
230 # restart
231 CHECK TABLE t1;
232 Table Op Msg_type Msg_text
233 test.t1 check status OK
234 SHOW CREATE TABLE t1;
235 Table Create Table
236 t1 CREATE TABLE `t1` (
237 `a` int(11) NOT NULL,
238 `b` int(11) DEFAULT NULL,
239 PRIMARY KEY (`a`)
240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
241 SELECT * FROM t1;
242 a b
243 1 1
244 2 1
245 DROP TABLE t1;
246 #
247 # MDEV-38079 Crash recovery fails after ALTER TABLE…PAGE_COMPRESSED=1
248 #
249 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
250 ALTER TABLE t1 PAGE_COMPRESSED=1, ALGORITHM=INSTANT;
251 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE
252 DROP TABLE t1;
253 #
254 # MDEV-38928 Assertion undo_no <= 1 in reset_and_truncate_undo
255 # during ALTER IGNORE on temporary table
256 #
257 CREATE TEMPORARY TABLE t1 (f1 INT) ENGINE=INNODB;
258 INSERT INTO t1 (f1) VALUES (0),(0);
259 ALTER IGNORE TABLE t1 FORCE;
260 DROP TABLE t1;
261 #
262 # MDEV-38951 Assertion `0' upon ALTER IGNORE handling duplicate keys
263 #
264 CREATE TABLE t1(f1 INT)ENGINE=InnoDB;
265 INSERT INTO t1 VALUES(1), (1);
266 ALTER IGNORE TABLE t1 ADD UNIQUE(f1);
267 CHECK TABLE t1 EXTENDED;
268 Table Op Msg_type Msg_text
269 test.t1 check status OK
270 DROP TABLE t1;
271 #
272 # MDEV-38993 Assertion `trx->undo_no == 1' fails upon ALTER IGNORE
273 #
274 CREATE TABLE t (a INT) ENGINE=InnoDB;
275 INSERT INTO t VALUES (1),(2),(1),(2);
276 ALTER IGNORE TABLE t ADD UNIQUE(a);
277 DROP TABLE t;