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
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
|
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
#
# 1.5) ALTER TABLE RENAME which fails at the late stage for SEs
# supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 RENAME TO t3;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
SET @old_lock_wait_timeout= @@lock_wait_timeout;
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME TO t4;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
# But metadata lock on old name can be still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# New name should not be locked.
SELECT * FROM t4;
i
UNLOCK TABLES;
DROP TABLES t1, t4;
#
# 2.5) ALTER TABLE INPLACE with RENAME clause fails at the late stage
# for SEs supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t3, ALGORITHM=INPLACE;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME COLUMN i TO j, RENAME TO t4, ALGORITHM=INPLACE;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
# But metadata lock on old name can be still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# New name should not be locked.
SELECT * FROM t4;
j
UNLOCK TABLES;
DROP TABLES t1, t4;
#
# 3.5) ALTER TABLE COPY with RENAME clause fails at the late stage
# for SEs supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
CREATE DATABASE mysqltest;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t3, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
DROP TABLE t1;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME COLUMN i TO j, RENAME TO t4, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
# Metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
UNLOCK TABLES;
# Now test SE not supporting atomic DDL and different schema
# to improve code coverage.
LOCK TABLE t4 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t4 RENAME COLUMN j TO i, RENAME TO mysqltest.t4, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
SELECT * FROM mysqltest.t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
# Metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM mysqltest.t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Also IX lock on new schema should be kept.
ALTER DATABASE mysqltest CHARACTER SET latin1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
UNLOCK TABLES;
DROP DATABASE mysqltest;
#
# 3.6) Special case ALTER TABLE COPY with RENAME clause which
# non-atomic, adds foreign keys and fails at the late stage.
#
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (fk INT) ENGINE=MyISAM;
LOCK TABLES t2 WRITE, t1 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 ADD FOREIGN KEY (fk) REFERENCES t1(pk), ENGINE=InnoDB, RENAME TO t3, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# Table should be removed from locked tables list and new
# table name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
# However, metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# And delete from parent table is possible and doesn't cause asserts.
DELETE FROM t1;
UNLOCK TABLES;
DROP TABLES t3, t1;
#
# Bug#24786075 FIND A WAY TO LIST #SQL... TABLE LEFT IN
# DATA DICTIONARY IN CASE ALTER FAILS.
# Test that we can see hidden temporary tables using ALTER TABLE.
# Test that we can delete the hidden temporary tables that were
# left by ALTER TABLE table failures in rare situations.
#
CREATE TABLE t1(a INT) ENGINE=MyISAM;
SET debug="+d,exit_after_alter_table_before_rename";
ALTER TABLE t1 modify column a varchar(30);
ERROR HY000: Unknown error
SET debug="-d,exit_after_alter_table_before_rename";
# Verify that #sql... tables are not seen by I_S and SHOW
SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test' AND TABLE_NAME like '#sql%';
COUNT(TABLE_NAME)
0
SHOW TABLES FROM test;
Tables_in_test
t1
# The SHOW EXTENDED [FULL] syntax should show the hidden table.
SHOW EXTENDED TABLES FROM test;
Tables_in_test
#sql-xxxxx
t1
SHOW EXTENDED FULL TABLES FROM test;
Tables_in_test Table_type
#sql-xxxxx BASE TABLE
t1 BASE TABLE
# Dropping the temporary table.
DROP TABLE `#sql-xxxxx;
# Verify that the temporary table is dropped.
SHOW EXTENDED TABLES FROM test;
Tables_in_test
t1
SHOW EXTENDED FULL TABLES FROM test;
Tables_in_test Table_type
t1 BASE TABLE
# clean-up
DROP TABLE t1;
drop table if exists t1, t2;
set debug_sync='RESET';
create table t1 (n1 int, n2 int, n3 int,
key (n1, n2, n3),
key (n2, n3, n1),
key (n3, n1, n2));
create table t2 (i int) engine=innodb;
alter table t1 disable keys;
insert into t1 values (1, 2, 3);
reset master;
set debug_sync='alter_table_enable_indexes SIGNAL parked WAIT_FOR go';
alter table t1 enable keys;;
set debug_sync='now WAIT_FOR parked';
insert into t2 values (1);
insert into t1 values (1, 1, 1);;
set debug_sync='now SIGNAL go';
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; insert into t2 values (1)
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # use `test`; alter table t1 enable keys
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; insert into t1 values (1, 1, 1)
binlog.000001 # Query # # COMMIT
drop tables t1, t2;
set debug_sync='RESET';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
|
