Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_max_row_size.inc

raw
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;