| 1 |
########################################################################## |
| 2 |
# Test script to test ALTER TABLE ADD/DROP COLUMN ... ALGORITHM=INSTANT |
| 3 |
# when INSTANT ADD causes max row size to go beyond permissible limit |
| 4 |
# |
| 5 |
# This test is to be run in debug mode only and with debug flag |
| 6 |
########################################################################## |
| 7 |
|
| 8 |
--disable_query_log |
| 9 |
call mtr.add_suppression("Trying to add or drop column.*ADD or DROP column with.*INSTANT is not allowed."); |
| 10 |
call mtr.add_suppression("Cannot add.*field\\(s\\) in table.*because after adding it.*"); |
| 11 |
--enable_query_log |
| 12 |
SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode; |
| 13 |
|
| 14 |
--echo # ------------------------------------------------------------ |
| 15 |
--echo # Create a table with 3 columns. [id, c2, c3] |
| 16 |
--echo # ------------------------------------------------------------ |
| 17 |
--eval CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000)) ROW_FORMAT=$row_format; |
| 18 |
|
| 19 |
--let $table_name=t1 |
| 20 |
--source suite/innodb/include/print_instant_metadata.inc |
| 21 |
|
| 22 |
SET SESSION innodb_strict_mode=OFF; |
| 23 |
|
| 24 |
--echo # ------------------------------------------------------------ |
| 25 |
--echo # Add a new column. It causes max_row_size > permissible size |
| 26 |
--echo # ------------------------------------------------------------ |
| 27 |
# Error with ALGORITHM=INSTANT |
| 28 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 29 |
ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT; |
| 30 |
|
| 31 |
# Pass with DEFAULT ALGORITHM |
| 32 |
ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'; |
| 33 |
|
| 34 |
--let $table_name=t1 |
| 35 |
--source suite/innodb/include/print_instant_metadata.inc |
| 36 |
|
| 37 |
# This INSERT results in error as the row size goes beyond permissible |
| 38 |
--error ER_TOO_BIG_ROWSIZE |
| 39 |
INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4000), repeat('b', 4000)); |
| 40 |
|
| 41 |
# This INSERT succeeds |
| 42 |
INSERT INTO t1 (id, c1, c2) VALUES(1, repeat('a', 4), repeat('b', 4)); |
| 43 |
SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1 ORDER BY id; |
| 44 |
|
| 45 |
# Now this table is already in a state where a possible row could go beyond |
| 46 |
# permissible size. So no new column could be added with ALGORITHM=INSTANT |
| 47 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 48 |
ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT; |
| 49 |
|
| 50 |
# Now this table is already in a state where a possible row could go beyond |
| 51 |
# permissible size. So no column could be dropped with ALGORITHM=INSTANT |
| 52 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 53 |
ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; |
| 54 |
|
| 55 |
--echo ########### |
| 56 |
--echo # CLEANUP # |
| 57 |
--echo ########### |
| 58 |
DROP TABLE t1; |
| 59 |
SET SESSION innodb_strict_mode=@saved_innodb_strict_mode; |
| 60 |
|
| 61 |
--echo # ------------------------------------------------------------ |
| 62 |
--echo # Create a partition table with 3 columns. [c1, c2, c3, c4] |
| 63 |
--echo # ------------------------------------------------------------ |
| 64 |
--eval CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(c1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 65 |
|
| 66 |
--let $table_name=t1 |
| 67 |
--source suite/innodb/include/print_instant_part_metadata.inc |
| 68 |
|
| 69 |
SET SESSION innodb_strict_mode=OFF; |
| 70 |
|
| 71 |
--echo # ------------------------------------------------------------ |
| 72 |
--echo # Add new columns. It causes max_row_size > permissible size |
| 73 |
--echo # ------------------------------------------------------------ |
| 74 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 75 |
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT, ALGORITHM=INSTANT; |
| 76 |
|
| 77 |
# Pass with DEFAULT ALGORITHM |
| 78 |
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ADD COLUMN c6 TEXT; |
| 79 |
|
| 80 |
--let $table_name=t1 |
| 81 |
--source suite/innodb/include/print_instant_part_metadata.inc |
| 82 |
|
| 83 |
# This INSERT results in error as the row size goes beyond permissible |
| 84 |
--error ER_TOO_BIG_ROWSIZE |
| 85 |
INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, repeat('a', 1000), repeat('c', 1000)); |
| 86 |
|
| 87 |
# This INSERT succeeds |
| 88 |
INSERT INTO t1 (c1, c2, c3, c4, c6) VALUES(1, 2, 3, 'abcdefg', 'hijklmnop'); |
| 89 |
SELECT c1, c2, c3, c4, LEFT(c5 , 10), c6 from t1; |
| 90 |
|
| 91 |
# Now this table is already in a state where a possible row could go beyond |
| 92 |
# permissible size. So no new column could be added with ALGORITHM=INSTANT |
| 93 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 94 |
ALTER TABLE t1 ADD COLUMN c7 INT, ALGORITHM=INSTANT; |
| 95 |
|
| 96 |
# Now this table is already in a state where a possible row could go beyond |
| 97 |
# permissible size. So no column could be dropped with ALGORITHM=INSTANT |
| 98 |
--error ER_INNODB_INSTANT_ADD_DROP_NOT_SUPPORTED_MAX_SIZE |
| 99 |
ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT; |
| 100 |
|
| 101 |
--echo ########### |
| 102 |
--echo # CLEANUP # |
| 103 |
--echo ########### |
| 104 |
DROP TABLE t1; |
| 105 |
SET SESSION innodb_strict_mode=@saved_innodb_strict_mode; |