summaryrefslogtreecommitdiff
path: root/mysql-test/suite/innodb/t/alter_crash.test
blob: b4fdfd2f2d5408474f3c8cc3082b144a20a0611a (plain)
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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
# Crash-safe InnoDB ALTER operations

--source include/not_valgrind.inc
--source include/not_embedded.inc
--source include/have_innodb.inc
--source include/have_debug.inc
--source include/not_crashrep.inc

--disable_query_log
call mtr.add_suppression('InnoDB: cannot find a free slot for an undo log');
call mtr.add_suppression('InnoDB: row_merge_rename_index_to_add failed with error 47');
call mtr.add_suppression('InnoDB: Flagged corruption of `c[23]`');
call mtr.add_suppression('InnoDB: Index `c[23]` .*is corrupted');
--enable_query_log

--echo #
--echo # Bug#20015132 ALTER TABLE FAILS TO CHECK IF TABLE IS CORRUPTED
--echo #

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(1), c3 INT UNSIGNED) ENGINE=InnoDB;
SET @saved_debug_dbug = @@SESSION.debug_dbug;
SET DEBUG_DBUG='+d,ib_create_table_fail_too_many_trx';
--error ER_TOO_MANY_CONCURRENT_TRXS
ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);

SET DEBUG_DBUG=@saved_debug_dbug;
ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3);
# Flag the secondary indexes corrupted.
SET DEBUG_DBUG='+d,dict_set_index_corrupted';
CHECK TABLE t1;

# Ensure that the corruption is permanent.
--source include/restart_mysqld.inc
CHECK TABLE t1;
ALTER TABLE t1 DROP INDEX c2;
CHECK TABLE t1;
# We refuse an ALTER TABLE that would modify the InnoDB data dictionary
# while leaving some of the table corrupted.
--error ER_INDEX_CORRUPT
ALTER TABLE t1 ADD INDEX (c2,c3);
# This will rebuild the table, uncorrupting all secondary indexes.
ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL;
CHECK TABLE t1;
ALTER TABLE t1 ADD INDEX (c2,c3);
DROP TABLE t1;

let $MYSQLD_DATADIR= `select @@datadir`;
let datadir= `select @@datadir`;

# These are from include/shutdown_mysqld.inc and allow to call start_mysqld.inc
--let $_server_id= `SELECT @@server_id`
--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect

--echo #
--echo # Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE
--echo #
--echo # -- Scenario 1:
--echo # Crash the server in ha_innobase::commit_inplace_alter_table()
--echo # just after committing the dictionary changes.

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb;
INSERT INTO t1 VALUES (1,2),(3,4);
SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';

let $orig_table_id = `SELECT table_id
	FROM information_schema.innodb_sys_tables
	WHERE name = 'test/t1'`;

# Write file to make mysql-test-run.pl expect crash
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

--error 2013
ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);

--echo # Restart mysqld after the crash and reconnect.
--source include/start_mysqld.inc

let $temp_table_name = `SELECT SUBSTR(name, 6)
	FROM information_schema.innodb_sys_tables
	WHERE table_id = $orig_table_id`;

--echo # Manual *.frm recovery begin.

--move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm

perl;
my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm";
my $t1_frm = "$ENV{'datadir'}/test/t1.frm";
rename($frm_file[0], $t1_frm);
EOF

--echo # Manual recovery end

FLUSH TABLES;

--echo # Drop the orphaned original table.
--disable_query_log
eval DROP TABLE `#mysql50#$temp_table_name`;
--enable_query_log

--echo # Files in datadir after manual recovery.
--list_files $MYSQLD_DATADIR/test

SHOW TABLES;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (5,6),(7,8);
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
DROP TABLE t1;

--echo # -- Scenario 2:
--echo # Crash the server in ha_innobase::commit_inplace_alter_table()
--echo # just before committing the dictionary changes, but after
--echo # writing the MLOG_FILE_RENAME records. As the mini-transaction
--echo # is not committed, the renames will not be replayed.

CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,2),(3,4);
SET DEBUG_DBUG='+d,innodb_alter_commit_crash_before_commit';

let $orig_table_id = `SELECT table_id
	FROM information_schema.innodb_sys_tables
	WHERE name = 'test/t2'`;

# Write file to make mysql-test-run.pl expect crash
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

--error 2013
ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);

--echo # Startup the server after the crash
--source include/start_mysqld.inc

--echo # Read and remember the temporary table name
let $temp_table_name = `SELECT SUBSTRING(name,6)
	FROM information_schema.innodb_sys_tables
	WHERE name LIKE "test/#sql-ib$orig_table_id%"`;

--echo # Manual *.frm recovery begin. The dictionary was not updated
--echo # and the files were not renamed. The rebuilt table
--echo # was left behind on purpose, to faciliate data recovery.

let TABLENAME_INC= $MYSQLTEST_VARDIR/tmp/tablename.inc;
perl;
die unless open OUT, ">$ENV{TABLENAME_INC}";
chdir "$ENV{'datadir'}/test";
my @frm_file = map { substr($_, 0, -4) } glob "#sql-*.frm";
print OUT 'let $tablename=', $frm_file[0], ';';
close OUT or die;
EOF
source $TABLENAME_INC;
remove_file $TABLENAME_INC;

--echo # Manual recovery end

--echo # Drop the orphaned rebuilt table.
--disable_query_log
eval DROP TABLE `#mysql50#$tablename`;
--enable_query_log

SHOW TABLES;
INSERT INTO t2 VALUES (5,6),(7,8);
SELECT * from t2;
SHOW CREATE TABLE t2;
DROP TABLE t2;

CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB;
ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
DROP TABLE t2;
--list_files $MYSQLD_DATADIR/test

--echo # -------------------------
--echo # End of Testing Scenario 2
--echo # -------------------------

--echo #
--echo # Bug#19330255 WL#7142 - CRASH DURING ALTER TABLE LEADS TO
--echo # DATA DICTIONARY INCONSISTENCY
--echo #

CREATE TABLE t1(a int PRIMARY KEY, b varchar(255), c int NOT NULL)
ENGINE=InnoDB;
INSERT INTO t1 SET a=1,c=2;
SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';

let $orig_table_id = `select table_id from
  information_schema.innodb_sys_tables where name = 'test/t1'`;

# Write file to make mysql-test-run.pl expect crash
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
#
--error 2013
ALTER TABLE t1 ADD INDEX (b), CHANGE c d int, ALGORITHM=INPLACE;

--echo # Restart mysqld after the crash and reconnect.
--source include/start_mysqld.inc

let $temp_table_name = `SELECT SUBSTR(name, 6)
	FROM information_schema.innodb_sys_tables
	WHERE table_id = $orig_table_id`;

--echo # Manual *.frm recovery begin.
--move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm

perl;
my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm";
my $t1_frm = "$ENV{'datadir'}/test/t1.frm";
rename($frm_file[0], $t1_frm);
EOF

--echo # Manual recovery end

FLUSH TABLES;

--echo # Drop the orphaned original table.
--disable_query_log
eval DROP TABLE `#mysql50#$temp_table_name`;
--enable_query_log

--echo # Files in datadir after manual recovery.
--list_files $MYSQLD_DATADIR/test

SHOW TABLES;
SHOW CREATE TABLE t1;
UPDATE t1 SET d=NULL;
SELECT * FROM t1;
DROP TABLE t1;