| 1 |
########################################################################## |
| 2 |
# Test script to test ROLLBACK and RECOVERY for table having INSTANT |
| 3 |
# ADD/DROP columns. |
| 4 |
# |
| 5 |
# $row_format is to be set to the row_format on which test is to be run. |
| 6 |
########################################################################## |
| 7 |
|
| 8 |
--echo # ------------------------------------------------------------ |
| 9 |
--echo # Create a table with 3 columns. [id, c1, c2, c3] |
| 10 |
--echo # ------------------------------------------------------------ |
| 11 |
--eval CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=$row_format; |
| 12 |
--let $table_name=t1 |
| 13 |
--source suite/innodb/include/print_instant_metadata.inc |
| 14 |
|
| 15 |
INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1)); |
| 16 |
SELECT id, length(c1), length(c2), length(c3) FROM t1; |
| 17 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1; |
| 18 |
|
| 19 |
--echo # INSTANT ADD a column |
| 20 |
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT; |
| 21 |
--let $table_name=t1 |
| 22 |
--source suite/innodb/include/print_instant_metadata.inc |
| 23 |
|
| 24 |
INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100)); |
| 25 |
|
| 26 |
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; |
| 27 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; |
| 28 |
|
| 29 |
START TRANSACTION; |
| 30 |
UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; |
| 31 |
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; |
| 32 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; |
| 33 |
ROLLBACK; |
| 34 |
|
| 35 |
SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1; |
| 36 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1; |
| 37 |
|
| 38 |
--echo # INSTANT DROP a column |
| 39 |
ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; |
| 40 |
--let $table_name=t1 |
| 41 |
--source suite/innodb/include/print_instant_metadata.inc |
| 42 |
|
| 43 |
SELECT id, length(c1), length(c2), length(c4) FROM t1; |
| 44 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; |
| 45 |
|
| 46 |
START TRANSACTION; |
| 47 |
UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1; |
| 48 |
SELECT id, length(c1), length(c2), length(c4) FROM t1; |
| 49 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; |
| 50 |
ROLLBACK; |
| 51 |
|
| 52 |
SELECT id, length(c1), length(c2), length(c4) FROM t1; |
| 53 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1; |
| 54 |
|
| 55 |
--echo ########### |
| 56 |
--echo # CLEANUP # |
| 57 |
--echo ########### |
| 58 |
DROP TABLE t1; |