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