Debian Codegraph @

mysql-test/suite/innodb/t/default_row_format_alter.test

raw
1 --source include/have_innodb.inc
2 --source include/innodb_row_format.inc
3
4 SET @row_format = @@GLOBAL.innodb_default_row_format;
5
6 --echo ####################################
7 --echo # Check if table rebuilding alter isn't affect if table is created
8 --echo # with explicit row_format
9 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB;
10 INSERT INTO t1 VALUES (1, 'abc');
11 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
12 SHOW TABLE STATUS LIKE 't1';
13
14 SET GLOBAL innodb_default_row_format=DYNAMIC;
15 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
16
17 --echo # Here we expect COMPACT because it was explicitly specified at CREATE
18 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
19 SHOW TABLE STATUS LIKE 't1';
20 DROP TABLE t1;
21
22 --echo ####################################
23 --echo # Check if table rebuilding alter is affected when there is no
24 --echo # row_format specified at CREATE TABLE.
25 SET GLOBAL innodb_default_row_format = COMPACT;
26 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
27 INSERT INTO t1 VALUES (1, 'abc');
28
29 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
30 SHOW TABLE STATUS LIKE 't1';
31
32 CREATE TABLE t2 (b VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB;
33
34 SET GLOBAL innodb_default_row_format = DYNAMIC;
35 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
36
37 --echo # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the
38 --echo # default_row_format is changed to DYNAMIC just before ALTER
39 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
40 SHOW TABLE STATUS LIKE 't1';
41 DROP TABLE t1;
42
43 --error ER_INDEX_COLUMN_TOO_LONG
44 ALTER TABLE t2 ADD INDEX(b);
45 ALTER TABLE t2 FORCE, ADD INDEX(b);
46 DROP TABLE t2;
47
48 --echo ####################################
49 --echo # Check the row_format effect on ALTER, ALGORITHM=COPY
50 SET GLOBAL innodb_default_row_format = REDUNDANT;
51 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
52 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
53
54 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
55 SHOW TABLE STATUS LIKE 't1';
56
57 CREATE TABLE t2 (b VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB;
58
59 SET GLOBAL innoDB_default_row_format = COMPACT;
60 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
61
62 --echo # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't
63 --echo # created with explicit row_format, so we expect ROW_FORMAT=COMPACT
64 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
65 SHOW TABLE STATUS LIKE 't1';
66 DROP TABLE t1;
67
68 --error ER_INDEX_COLUMN_TOO_LONG
69 ALTER TABLE t2 ADD INDEX(b);
70 --error ER_INDEX_COLUMN_TOO_LONG
71 ALTER TABLE t2 FORCE, ADD INDEX(b);
72
73 SET GLOBAL innodb_default_row_format = DYNAMIC;
74 --error ER_INDEX_COLUMN_TOO_LONG
75 ALTER TABLE t2 ADD INDEX(b);
76 ALTER TABLE t2 FORCE, ADD INDEX(b);
77
78 DROP TABLE t2;
79
80 --echo
81 --echo ###################################
82 --echo # Check the row_format effect on ALTER, ALGORITHM=COPY on
83 --echo # create table with explicit row_format
84 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB;
85 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
86
87 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
88 SHOW TABLE STATUS LIKE 't1';
89
90 SET GLOBAL innoDB_default_row_format = COMPACT;
91 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
92
93 --echo # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is
94 --echo # created with explicit row_format, so we expect original
95 --echo # ROW_FORMAT=REDUNDANT
96 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
97 SHOW TABLE STATUS LIKE 't1';
98 DROP TABLE t1;
99
100 --echo
101 --echo ##################################
102 --echo # Check row_format on ALTER ALGORITHM=INPLACE
103 SET GLOBAL innodb_default_row_format=COMPACT;
104
105 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB;
106 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
107
108 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
109 SHOW TABLE STATUS LIKE 't1';
110
111 SET GLOBAL innodb_default_row_format=DYNAMIC;
112 ALTER TABLE t1 DROP INDEX k1;
113
114 --echo # Because it is in-place operation, there is no rebuild, so the
115 --echo # original format has to be retained.
116 --replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
117 SHOW TABLE STATUS LIKE 't1';
118 DROP TABLE t1;
119
120 --echo #
121 --echo # MDEV-24758 heap-use-after-poison in innobase_add_instant_try/rec_copy
122 --echo #
123
124 CREATE TABLE t1 (pk INT PRIMARY KEY) CHARACTER SET utf8 ENGINE=InnoDB;
125 INSERT INTO t1 VALUES (1);
126 SET GLOBAL innodb_default_row_format = REDUNDANT;
127 ALTER TABLE t1 ADD a CHAR(8) DEFAULT '';
128 DROP TABLE t1;
129 SET GLOBAL innodb_default_row_format = @row_format;
130
131 --echo # End of 10.3 tests
132
133 --echo #
134 --echo # MDEV-23295 Assertion fields[i].same(instant.fields[i]) failed
135 --echo #
136 SET GLOBAL innodb_default_row_format = @row_format;
137 CREATE TABLE t1 (a char(8)) ENGINE=InnoDB DEFAULT CHARSET utf8;
138 SET GLOBAL innodb_default_row_format= COMPACT;
139 ALTER TABLE t1 ADD b INT;
140 SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
141 DROP TABLE t1;
142
143 --echo #
144 --echo # MDEV-23345 Assertion not_redundant() == old.not_redundant() failed
145 --echo #
146 SET GLOBAL innodb_default_row_format = @row_format;
147 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)) ENGINE=InnoDB;
148 SET GLOBAL innodb_default_row_format= COMPACT;
149 ALTER TABLE t1 DROP b;
150 SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
151 DROP TABLE t1;
152
153 --echo #
154 --echo # MDEV-31025 Redundant table alter fails when fixed column
155 --echo # stored externally
156 --echo #
157 set @old_sql_mode = @@sql_mode;
158 SET @@sql_mode='';
159 CREATE TABLE t1(pk INT,c CHAR(255),c2 CHAR(255),c3 CHAR(255),
160 c4 char(255), c5 char(255), c6 char(255),
161 c7 char(255), c8 char(255), primary key(pk)
162 )Engine=InnoDB character set utf32 ROW_FORMAT=REDUNDANT;
163 INSERT INTO t1(pk, c) VALUES (1, repeat('a', 255));
164 ALTER TABLE t1 FORCE;
165 CHECK TABLE t1;
166 SELECT LENGTH(c) FROM t1;
167 DROP TABLE t1;
168 set @@sql_mode = @old_sql_mode;
169
170 --echo # End of 10.4 tests
171
172 SET GLOBAL innodb_default_row_format = @row_format;