1
# transaction_log_alter.test
2
# test of various ALTER TABLE statements
3
# and how they are captured by the transaction log
5
# Ignore startup/shutdown events
7
--source ../plugin/transaction_log/tests/t/truncate_log.inc
10
--echo Testing RENAME table - positive
12
DROP TABLE IF EXISTS t1, t1_new_name;
15
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
17
ALTER TABLE t1 RENAME TO t1_new_name;
18
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
21
DROP TABLE t1_new_name;
23
# Truncate the log file to reset for the next test
24
--source ../plugin/transaction_log/tests/t/truncate_log.inc
27
--echo Testing RENAME table - negative
29
DROP TABLE IF EXISTS t1, t2;
32
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
34
CREATE TABLE t2 LIKE t1;
36
--ERROR ER_TABLE_EXISTS_ERROR
37
ALTER TABLE t1 RENAME TO t2;
38
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
43
# Truncate the log file to reset for the next test
44
--source ../plugin/transaction_log/tests/t/truncate_log.inc
47
--echo Testing RENAME table - negative
49
DROP TABLE IF EXISTS t1, t2;
52
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
54
CREATE TABLE t2 LIKE t1;
56
--ERROR ER_TABLE_EXISTS_ERROR
57
ALTER TABLE t1 RENAME TO t2;
58
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
63
# Truncate the log file to reset for the next test
64
--source ../plugin/transaction_log/tests/t/truncate_log.inc
67
--echo Testing RENAME Table with Foreign Key constraints
69
DROP TABLE IF EXISTS t1, t2, t1_new_name ;
72
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
74
CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b),
75
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);
77
ALTER TABLE t1 RENAME to t1_new_name;
78
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
83
DROP TABLE t2, t1_new_name;
85
# Truncate the log file to reset for the next test
86
--source ../plugin/transaction_log/tests/t/truncate_log.inc
89
--echo Testing ADD COLUMN simple
91
DROP TABLE IF EXISTS t1 ;
94
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
96
ALTER TABLE t1 ADD COLUMN c CHAR(100) NOT NULL;
97
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
102
# Truncate the log file to reset for the next test
103
--source ../plugin/transaction_log/tests/t/truncate_log.inc
106
--echo Testing ADD COLUMN simple negative
108
DROP TABLE IF EXISTS t1 ;
111
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
113
--ERROR ER_DUP_FIELDNAME
114
ALTER TABLE t1 ADD COLUMN b CHAR(100) NOT NULL;
115
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
120
# Truncate the log file to reset for the next test
121
--source ../plugin/transaction_log/tests/t/truncate_log.inc
124
--echo Testing ADD COLUMN AFTER <col_name>
126
DROP TABLE IF EXISTS t1 ;
129
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
131
ALTER TABLE t1 ADD COLUMN c CHAR(100) NOT NULL AFTER a;
132
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
137
# Truncate the log file to reset for the next test
138
--source ../plugin/transaction_log/tests/t/truncate_log.inc
141
--echo Testing ADD COLUMN FIRST
143
DROP TABLE IF EXISTS t1 ;
146
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
148
ALTER TABLE t1 ADD COLUMN c CHAR(100) NOT NULL FIRST ;
149
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
154
# Truncate the log file to reset for the next test
155
--source ../plugin/transaction_log/tests/t/truncate_log.inc
158
--echo Testing ADD COLUMN multiple columns
160
DROP TABLE IF EXISTS t1 ;
163
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
165
ALTER TABLE t1 ADD COLUMN c CHAR(100) NOT NULL FIRST, ADD COLUMN d BLOB AFTER a ;
166
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
171
# Truncate the log file to reset for the next test
172
--source ../plugin/transaction_log/tests/t/truncate_log.inc
175
--echo Testing DROP COLUMN simple
177
DROP TABLE IF EXISTS t1 ;
180
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
182
ALTER TABLE t1 DROP COLUMN b ;
183
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
188
# Truncate the log file to reset for the next test
189
--source ../plugin/transaction_log/tests/t/truncate_log.inc
192
--echo Testing DROP COLUMN multiple columns
194
DROP TABLE IF EXISTS t1 ;
197
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, c BLOB, d DATE, PRIMARY KEY(a));
199
ALTER TABLE t1 DROP COLUMN b, DROP COLUMN d ;
200
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
205
# Truncate the log file to reset for the next test
206
--source ../plugin/transaction_log/tests/t/truncate_log.inc
209
--echo Testing DROP COLUMN negative
211
DROP TABLE IF EXISTS t1 ;
214
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
216
--ERROR ER_CANT_REMOVE_ALL_FIELDS
217
ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b ;
218
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
223
# Truncate the log file to reset for the next test
224
--source ../plugin/transaction_log/tests/t/truncate_log.inc
227
--echo Testing DISABLE/ENABLE KEYS
229
DROP TABLE IF EXISTS t1 ;
232
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key (b,a));
234
ALTER TABLE t1 DISABLE KEYS ;
235
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
238
ALTER TABLE t1 ENABLE KEYS ;
239
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
245
# Truncate the log file to reset for the next test
246
--source ../plugin/transaction_log/tests/t/truncate_log.inc
249
--echo Testing ADD / DROP INDEX
251
DROP TABLE IF EXISTS t1 ;
254
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
256
ALTER TABLE t1 ADD INDEX b_key (b,a) ;
257
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
260
ALTER TABLE t1 DROP INDEX b_key;
261
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
266
# Truncate the log file to reset for the next test
267
--source ../plugin/transaction_log/tests/t/truncate_log.inc
270
--echo Testing ADD / DROP INDEX2
272
DROP TABLE IF EXISTS t1 ;
275
CREATE TABLE t1(a INT NOT NULL, b CHAR(50) NOT NULL, PRIMARY KEY(a));
277
ALTER TABLE t1 ADD INDEX b_key (b(10),a) ;
278
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
281
ALTER TABLE t1 DROP INDEX b_key;
282
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
287
# Truncate the log file to reset for the next test
288
--source ../plugin/transaction_log/tests/t/truncate_log.inc
292
--echo Testing ADD INDEX negative
294
DROP TABLE IF EXISTS t1 ;
297
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key(b));
299
--ERROR ER_DUP_KEYNAME
300
ALTER TABLE t1 ADD INDEX b_key (b,a) ;
301
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
306
# Truncate the log file to reset for the next test
307
--source ../plugin/transaction_log/tests/t/truncate_log.inc
310
--echo Testing DROP INDEX negative1
312
DROP TABLE IF EXISTS t1 ;
315
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
317
--ERROR ER_CANT_DROP_FIELD_OR_KEY
318
ALTER TABLE t1 DROP INDEX i_dont_exist ;
319
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
324
# Truncate the log file to reset for the next test
325
--source ../plugin/transaction_log/tests/t/truncate_log.inc
328
--echo Testing ALTER COLUMN
330
DROP TABLE IF EXISTS t1 ;
333
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
335
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 999 ;
336
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
339
SHOW CREATE TABLE t1;
341
ALTER TABLE t1 ALTER COLUMN b DROP DEFAULT ;
342
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
345
SHOW CREATE TABLE t1;
349
# Truncate the log file to reset for the next test
350
--source ../plugin/transaction_log/tests/t/truncate_log.inc
353
--echo Testing CHANGE COLUMN
355
DROP TABLE IF EXISTS t1 ;
358
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a));
360
ALTER TABLE t1 CHANGE COLUMN b new_b_name CHAR(500) DEFAULT 'I am not an int now' FIRST ;
361
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
364
SHOW CREATE TABLE t1;
368
# Truncate the log file to reset for the next test
369
--source ../plugin/transaction_log/tests/t/truncate_log.inc
372
--echo Testing MODIFY COLUMN1
374
DROP TABLE IF EXISTS t1 ;
377
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, c DATE, PRIMARY KEY(a));
379
ALTER TABLE t1 MODIFY COLUMN b CHAR(50) DEFAULT 'I am now a CHAR field' AFTER c ;
380
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
383
SHOW CREATE TABLE t1;
387
# Truncate the log file to reset for the next test
388
--source ../plugin/transaction_log/tests/t/truncate_log.inc
391
--echo Testing MODIFY COLUMN2
393
DROP TABLE IF EXISTS t1 ;
396
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, c DATE, PRIMARY KEY(a));
398
--ERROR ER_BAD_FIELD_ERROR
399
ALTER TABLE t1 MODIFY COLUMN b CHAR(50) DEFAULT 'I am now a CHAR field' AFTER b ;
400
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
403
SHOW CREATE TABLE t1;
407
# Truncate the log file to reset for the next test
408
--source ../plugin/transaction_log/tests/t/truncate_log.inc
411
--echo Testing MODIFY COLUMN3
413
DROP TABLE IF EXISTS t1 ;
416
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, c DATE, PRIMARY KEY(a));
418
ALTER TABLE t1 MODIFY COLUMN b INT NOT NULL ;
419
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
422
SHOW CREATE TABLE t1;
426
# Truncate the log file to reset for the next test
427
--source ../plugin/transaction_log/tests/t/truncate_log.inc
430
--echo Testing ADD/DROP Foreign Key constraints
432
DROP TABLE IF EXISTS t1, t2;
435
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
436
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
439
CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b));
441
ALTER TABLE t2 ADD CONSTRAINT fk_constraint_t2 FOREIGN KEY(b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE;
442
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
445
ALTER TABLE t2 DROP FOREIGN KEY fk_constraint_t2 ;
446
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
454
# Truncate the log file to reset for the next test
455
--source ../plugin/transaction_log/tests/t/truncate_log.inc