| 1 |
--source include/have_innodb.inc |
| 2 |
--source include/have_sequence.inc |
| 3 |
--source include/test_db_charset_latin1.inc |
| 4 |
|
| 5 |
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent; |
| 6 |
SET GLOBAL innodb_stats_persistent = 0; |
| 7 |
|
| 8 |
--echo # |
| 9 |
--echo # MDEV-17821 Assertion `!page_rec_is_supremum(rec)' failed |
| 10 |
--echo # in btr_pcur_store_position |
| 11 |
--echo # |
| 12 |
|
| 13 |
CREATE TABLE t1 (pk INT PRIMARY KEY, c INT) ENGINE=InnoDB; |
| 14 |
INSERT INTO t1 VALUES (1,2); |
| 15 |
ALTER TABLE t1 ADD COLUMN f VARCHAR(255); |
| 16 |
ALTER TABLE t1 DROP COLUMN f; |
| 17 |
DELETE FROM t1; |
| 18 |
HANDLER t1 OPEN; |
| 19 |
HANDLER t1 READ `PRIMARY` <= (3); |
| 20 |
DROP TABLE t1; |
| 21 |
|
| 22 |
CREATE TABLE t1 ( |
| 23 |
pk INT AUTO_INCREMENT, |
| 24 |
f1 CHAR(32), |
| 25 |
f2 CHAR(32), |
| 26 |
f3 CHAR(32), |
| 27 |
f4 INT, |
| 28 |
f5 CHAR(32), |
| 29 |
f6 INT, |
| 30 |
f7 INT, |
| 31 |
f8 INT, |
| 32 |
PRIMARY KEY (pk), |
| 33 |
UNIQUE (f6) |
| 34 |
) ENGINE=InnoDB; |
| 35 |
|
| 36 |
INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES |
| 37 |
('reality', 'llt', 'within', -1996816384, 'j', 160, 7, -1822687232), |
| 38 |
('h', 'j', 'j', 251, 'civilian', NULL, 211, NULL), |
| 39 |
('ltq', 'b', 'mud', 111, 'v', 90, 0, NULL), |
| 40 |
('toxic', 'breakfast', 'series', 2, 'x', NULL, 118, 2), |
| 41 |
('h', 'n', 'vision', 84, 'n', NULL, 197, 103), |
| 42 |
('h', 'tq', 'q', 6, 'bet', -1927151616, -446038016, 3), |
| 43 |
('pocket', 'qjt', 'jtl', 0, 'blink', NULL, 12, 0), |
| 44 |
('k', 'uv', 'exist', 37, 'g', 149, -1610219520, NULL), |
| 45 |
('parent', 'motion', 'u', 70, 'promote', NULL, 178, NULL), |
| 46 |
('get', 'convict', 'liquid', -780337152, 'd', NULL, 4, NULL), |
| 47 |
('vp', 'px', 'xi', -631111680, 'support', NULL, 8, NULL), |
| 48 |
('ck', 'z', 'minority', 8, 'k', NULL, 864878592, NULL), |
| 49 |
('kxx', 'g', 'container', 1, 'cholesterol', NULL, 4, 1404436480), |
| 50 |
('xxv', 'rail', 'j', 219, 'serious', NULL, -816119808, 62), |
| 51 |
('x', 'v', 'vr', 146, 'm', 16, 170, -1765867520), |
| 52 |
('age', 'm', 'q', -1251278848, 'rte', 6, 224, NULL), |
| 53 |
('discrimination', 't', 'q', 31, 'exl', 0, 2, 244), |
| 54 |
('water', 'x', 'ldb', 98, 'r', 235, 4, 63), |
| 55 |
('d', 'db', 'p', 248, 'so-called', 102, -255524864, 198), |
| 56 |
('last', 'bz', 'us', 0, 'experienced', 137, 240, 134), |
| 57 |
('q', 'z', 'attract', 67, 'code', 67, 75, 1758920704), |
| 58 |
('yellow', 'c', 'u', 6, 'v', 1731985408, 528154624, 2), |
| 59 |
('cognitive', 'he', 'protective', 590020608, 'sentence', NULL, 4, 102), |
| 60 |
('eih', 'l', 'ih', 1266024448, 'traditionally', NULL, 190, NULL), |
| 61 |
('pine', 'i', 'y', 6, 'glimpse', 214, 7, -1486880768), |
| 62 |
('jo', 'everyone', 'ol', 0, 'lj', NULL, 1, 0), |
| 63 |
('blood', 'f', 'scientist', 54, 'j', 1341128704, 168, NULL), |
| 64 |
('z', 'brief', 'ambassador', 115, 'ygp', 82, 129, NULL), |
| 65 |
('gp', 'severe', 'consist', 7, 'p', -1829961728, 602669056, 154), |
| 66 |
('admit', 'poetry', 'x', 116, 'enemy', 174, -2128543744, -407764992), |
| 67 |
('s', 'norm', 'decide', 2055667712, 'rtz', NULL, 99, -1233715200), |
| 68 |
('tzg', 'f', 'beg', 2016280576, 'w', NULL, -643825664, 137), |
| 69 |
('zgg', 'x', 'f', 148, 'y', -987496448, -708116480, 8), |
| 70 |
('attorney', 'perfectly', 's', 49, 'z', -1865285632, 56, -1518534656), |
| 71 |
('concentrate', 's', 'k', -1028849664, 'tir', 83, -1592459264, 1820065792), |
| 72 |
('t', 'sacrifice', 'ir', -2143879168, 'recipe', 156, 217, NULL), |
| 73 |
('wdf', 'd', 'f', 137, 'empty', NULL, 188, NULL), |
| 74 |
('o', 'customer', 'qd', -2135293952, 'z', 1527840768, 227, -1174929408), |
| 75 |
('d', 'qow', 'o', 1472266240, 'whe', NULL, 7, 197), |
| 76 |
('deny', 'reputation', 'dutch', 59, 'v', 124, 2, 191), |
| 77 |
('m', 'liver', 'fv', 0, 'policy', 781582336, 198574080, 177), |
| 78 |
('vk', 'kx', 'immigrant', -1452736512, 'x', 163, 76, 6) , |
| 79 |
('j', 'ru', 'r', 67, 'joke', NULL, 3, NULL), |
| 80 |
('o', 'u', 'a', -569442304, 'uz', NULL, 933298176, NULL), |
| 81 |
('g', 'zb', 'c', -1694760960, 'fish', 18, -390463488, 1), |
| 82 |
('bb', 'o', 'b', 6, 'z', 9, 12, NULL), |
| 83 |
('compelling', 'xe', 'debut', 89, 'e', -734724096, 119, 175), |
| 84 |
('md', 'r', 'object', 1046478848, 'frequently', 915537920, 0, 1506410496), |
| 85 |
('dwn', 'wnz', 'x', 1767571456, 'nz', 241, -882180096, 9), |
| 86 |
('zvf', 'vfo', 'g', -844824576, 'w', NULL, 1, 9), |
| 87 |
('w', 'pose', 'r', 1029308416, 'a', -48627712, 1756168192, NULL), |
| 88 |
('o', 'jwp', 'patient', 172, 'i', 297140224, 45809664, 3), |
| 89 |
('w', 'p', 'american', 450297856, 'z', 20, 4, 186), |
| 90 |
('ridiculous', 'helpful', 'vy', -2022899712, 'conspiracy', NULL, 162, -264634368), |
| 91 |
('t', 'g', 'spite', 289931264, 'y', 4, 13, NULL), |
| 92 |
('performer', 'i', 'tomato', -1519386624, 'mz', 8, 87, 106), |
| 93 |
('m', 'z', 'hang', 3, 'crowded', -537919488, 1, 2), |
| 94 |
('fu', 'uot', 'j', 1, 'o', 179, 220, -2084569088), |
| 95 |
('ts', 'n', 'su', 1, 'o', 198, 9, 68), |
| 96 |
('ball', 'halfway', 'uf', 40, 'l', 145948672, 9, 149), |
| 97 |
('hunting', 'n', 'teenager', 0, 'neat', 209, 2044461056, 68), |
| 98 |
('independent', 'along', 'fpn', 5, 'pn', 3, 1353252864, 217), |
| 99 |
('p', 'presumably', 'n', -1977548800, 'balanced', 1909260288, 197, NULL), |
| 100 |
('pink', 'h', 'tear', 8, 'n', 254, 8, 1006305280), |
| 101 |
('tyy', 'n', 'yyr', 1107820544, 'yr', NULL, 0, 219), |
| 102 |
('u', 'retirement', 'thread', -2083192832, 'rx', -678232064, 209, 1048969216), |
| 103 |
('xk', 'kb', 'z', 9, 'ba', 218, 7, 8), |
| 104 |
('a', 'plenty', 'forget', 36, 'c', 215, 2027094016, NULL), |
| 105 |
('i', 'compromise', 'n', -1090256896, 'o', 10, 66, 1872887808), |
| 106 |
('x', 'disappointment', 'cognitive', 753860608, 'ua', 77, 123, 10), |
| 107 |
('e', 'added', 'aub', 2, 'u', NULL, 9, 92), |
| 108 |
('bc', 'h', 'n', 146, 'master', NULL, 1003945984, NULL), |
| 109 |
('execution', 'f', 'cgp', 574423040, 'gp', 2, -518782976, -1189085184), |
| 110 |
('pv', 'bad', 'v', 132, 'r', 195, 6, 5), |
| 111 |
('modify', 'participation', 'vol', 237, 'j', -842924032, 88, -747765760), |
| 112 |
('substantially', 'i', 'congressional', 2, 'edit', NULL, 1003159552, NULL), |
| 113 |
('tell', 'forty', 'v', -910098432, 'd', 43, 3, NULL), |
| 114 |
('crawl', 'ad', 'respect', -1851195392, 'p', 72, -1709047808, 1343225856), |
| 115 |
('w', 'reception', 'fiber', 56, 's', NULL, 2, -993787904), |
| 116 |
('successful', 'instruct', 'dug', 2, 'u', 7, -411500544, NULL), |
| 117 |
('appointment', 'pregnant', 'weird', 2, 'r', NULL, -897384448, 76), |
| 118 |
('g', 'j', 'thin', 663617536, 'oan', 1, 7, NULL), |
| 119 |
('secretary', 'a', 'o', 103, 'nj', 1977745408, -1291124736, -1314521088), |
| 120 |
('g', 'jq', 'q', 1875116032, 'blame', NULL, 1, 4), |
| 121 |
('oj', 'j', 'breast', 150, 'c', NULL, 3, NULL), |
| 122 |
('rd', 'm', 'comprehensive', 1723334656, 't', NULL, 155, -312344576) , |
| 123 |
('a', 'd', 'criminal', -1155137536, 'airplane', 242, -662896640, 1), |
| 124 |
('fast', 'i', 'k', -386662400, 'zxe', NULL, 7, 119), |
| 125 |
('xe', 'mouse', 'c', -205717504, 'ew', NULL, -729612288, 86), |
| 126 |
('hang', 'j', 'o', 3, 'hungry', NULL, 200, 49), |
| 127 |
('expense', 'z', 'sum', 2, 'gob', -472055808, -538181632, NULL), |
| 128 |
('nest', 'o', 'k', 116, 'weak', NULL, 223, NULL); |
| 129 |
--error ER_DUP_ENTRY |
| 130 |
INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES ('impact', 'b', 'h', 185, 'fj', 7, 7, 3); |
| 131 |
|
| 132 |
ALTER TABLE t1 ADD COLUMN filler VARCHAR(255) DEFAULT ''; |
| 133 |
SELECT * INTO OUTFILE 'load.data' FROM t1; |
| 134 |
UPDATE IGNORE t1 SET pk = 0; |
| 135 |
LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; |
| 136 |
HANDLER t1 OPEN AS h; |
| 137 |
HANDLER h READ `PRIMARY` PREV WHERE 0; |
| 138 |
|
| 139 |
# Cleanup |
| 140 |
HANDLER h CLOSE; |
| 141 |
DROP TABLE t1; |
| 142 |
|
| 143 |
--echo # |
| 144 |
--echo # MDEV-19630 ALTER TABLE ... ADD COLUMN damages foreign keys |
| 145 |
--echo # which are pointed to the table being altered |
| 146 |
--echo # |
| 147 |
CREATE TABLE t1(f1 int not null, primary key(f1))engine=innodb; |
| 148 |
CREATE TABLE t2(f1 INT AUTO_INCREMENT NOT NULL, f2 INT NOT NULL, |
| 149 |
status ENUM ('a', 'b', 'c'), INDEX idx1(f2), |
| 150 |
PRIMARY KEY(f1), |
| 151 |
FOREIGN KEY (f2) REFERENCES t1(f1))ENGINE=InnoDB; |
| 152 |
|
| 153 |
ALTER TABLE t1 CHANGE f1 f1_id INT NOT NULL, ADD f3 VARCHAR(255) DEFAULT NULL; |
| 154 |
ALTER TABLE t1 CHANGE f1_id f1 INT NOT NULL; |
| 155 |
|
| 156 |
SHOW CREATE TABLE t1; |
| 157 |
SHOW CREATE TABLE t2; |
| 158 |
|
| 159 |
ALTER TABLE t2 CHANGE status status VARCHAR(20) DEFAULT NULL; |
| 160 |
DROP TABLE t2, t1; |
| 161 |
|
| 162 |
--let $datadir= `select @@datadir` |
| 163 |
--remove_file $datadir/test/load.data |
| 164 |
|
| 165 |
--echo # |
| 166 |
--echo # MDEV-20938 Double free of dict_foreign_t during instant ALTER TABLE |
| 167 |
--echo # |
| 168 |
|
| 169 |
CREATE TABLE t1 (id INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB; |
| 170 |
CREATE TABLE t2 (a INT UNSIGNED PRIMARY KEY, b INT UNSIGNED UNIQUE, |
| 171 |
FOREIGN KEY fk1 (b) REFERENCES t1 (id)) ENGINE=InnoDB; |
| 172 |
ALTER TABLE t2 |
| 173 |
DROP FOREIGN KEY fk1, |
| 174 |
CHANGE b d INT UNSIGNED, |
| 175 |
ADD c INT; |
| 176 |
DROP TABLE t2, t1; |
| 177 |
|
| 178 |
--echo # |
| 179 |
--echo # MDEV-22446 InnoDB aborts while adding instant column |
| 180 |
--echo # for discarded tablespace |
| 181 |
--echo # |
| 182 |
|
| 183 |
let MYSQLD_DATADIR =`SELECT @@datadir`; |
| 184 |
CREATE TABLE t1(c1 INT NOT NULL, c2 INT NOT NULL DEFAULT 0)ENGINE=InnoDB; |
| 185 |
INSERT INTO t1(c1) VALUES(1); |
| 186 |
|
| 187 |
ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; |
| 188 |
--replace_regex /, .*\).*t1.cfg/, Bad file descriptor) t1.cfg/ |
| 189 |
FLUSH TABLES t1 FOR EXPORT; |
| 190 |
|
| 191 |
perl; |
| 192 |
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; |
| 193 |
ib_backup_tablespaces("test", "t1"); |
| 194 |
EOF |
| 195 |
UNLOCK TABLES; |
| 196 |
DROP TABLE t1; |
| 197 |
|
| 198 |
# Restore of instant table |
| 199 |
CREATE TABLE t1(c1 INT NOT NULL)Engine=InnoDB; |
| 200 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 201 |
FLUSH TABLES; |
| 202 |
ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL; |
| 203 |
ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; |
| 204 |
# Restore files |
| 205 |
perl; |
| 206 |
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; |
| 207 |
ib_restore_tablespaces("test", "t1"); |
| 208 |
EOF |
| 209 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 210 |
SHOW CREATE TABLE t1; |
| 211 |
SELECT * FROM t1; |
| 212 |
DROP TABLE t1; |
| 213 |
|
| 214 |
|
| 215 |
--echo # |
| 216 |
--echo # MDEV-23801 Assertion index->table->instant... failed |
| 217 |
--echo # in btr_pcur_store_position() |
| 218 |
--echo # |
| 219 |
|
| 220 |
CREATE TABLE t ( |
| 221 |
pk int auto_increment, |
| 222 |
c01 char(255) not null default repeat('a',255), |
| 223 |
c02 char(255) default repeat('a',255), |
| 224 |
c03 char(255) default repeat('a',255), |
| 225 |
c04 char(255) default repeat('a',255), |
| 226 |
c05 char(255) not null default repeat('a',255), |
| 227 |
c06 char(255) default repeat('a',255), |
| 228 |
c07 char(255) default repeat('a',255), |
| 229 |
c08 char(255) not null default repeat('a',255), |
| 230 |
c09 char(255) default repeat('a',255), |
| 231 |
c10 char(255) default repeat('a',255), |
| 232 |
c11 char(255) default repeat('a',255), |
| 233 |
c12 char(255) not null default repeat('a',255), |
| 234 |
primary key (pk) |
| 235 |
) ENGINE=InnoDB CHARACTER SET ucs2; |
| 236 |
|
| 237 |
INSERT INTO t () VALUES (); |
| 238 |
ALTER TABLE t ADD c INT; |
| 239 |
|
| 240 |
BEGIN; |
| 241 |
INSERT INTO t () VALUES (),(); |
| 242 |
ROLLBACK; |
| 243 |
DELETE FROM t; |
| 244 |
|
| 245 |
--source include/wait_all_purged.inc |
| 246 |
|
| 247 |
CREATE TABLE tt ENGINE=InnoDB AS SELECT c FROM t; |
| 248 |
DROP TABLE t, tt; |
| 249 |
|
| 250 |
--echo # End of 10.3 tests |
| 251 |
|
| 252 |
--disable_query_log |
| 253 |
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; |
| 254 |
SET GLOBAL innodb_read_only_compressed=OFF; |
| 255 |
--enable_query_log |
| 256 |
create table t ( |
| 257 |
a varchar(9), |
| 258 |
b int, |
| 259 |
c int, |
| 260 |
row_start bigint unsigned generated always as row start invisible, |
| 261 |
row_end bigint unsigned generated always as row end invisible, |
| 262 |
period for system_time (row_start, row_end) |
| 263 |
) engine=innodb row_format=compressed with system versioning; |
| 264 |
insert into t values (repeat('a', 9), 1, 1); |
| 265 |
set @@system_versioning_alter_history = keep; |
| 266 |
alter table t modify a varchar(10), algorithm=instant; |
| 267 |
alter table t change b bb int, algorithm=instant; |
| 268 |
alter table t modify c int without system versioning, algorithm=instant; |
| 269 |
set @@system_versioning_alter_history = error; |
| 270 |
--disable_query_log |
| 271 |
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; |
| 272 |
--enable_query_log |
| 273 |
check table t; |
| 274 |
drop table t; |
| 275 |
|
| 276 |
--echo # |
| 277 |
--echo # MDEV-18219 Assertion `index->n_core_null_bytes <= ...' failed |
| 278 |
--echo # in rec_init_offsets after instant DROP COLUMN |
| 279 |
--echo # |
| 280 |
CREATE TABLE t1 (a INT, b INT NOT NULL) ENGINE=InnoDB; |
| 281 |
INSERT INTO t1 VALUES |
| 282 |
(0,9),(2,7),(3,1),(3,4),(8,4),(3,7),(6,1),(3,8),(1,2),(4,1),(0,8),(5,3), |
| 283 |
(1,3),(1,6),(2,1),(8,7),(6,0),(1,9),(9,4),(0,6),(9,3),(0,9),(9,4),(2,4), |
| 284 |
(2,7),(7,8),(8,2),(2,5),(6,1),(4,5),(5,3),(6,8),(4,9),(5,7),(7,5),(5,1), |
| 285 |
(8,8),(5,7),(3,8),(0,1),(8,4),(8,3),(9,7),(4,8),(1,1),(0,4),(2,6),(8,5), |
| 286 |
(8,8),(8,7),(6,7),(1,7),(9,6),(3,6),(1,9),(0,3),(5,3),(2,4),(0,6),(2,0), |
| 287 |
(6,5),(1,6),(2,4),(9,1),(3,0),(6,4),(1,3),(0,8),(3,5),(3,1),(8,9),(9,9), |
| 288 |
(7,9),(4,5),(2,2),(3,8),(0,8),(7,1),(2,0),(1,5),(7,3),(4,4),(3,9),(7,2), |
| 289 |
(6,2),(0,4),(2,0),(1,5),(5,7),(4,5),(3,7),(6,0),(2,1),(5,0),(1,0),(2,0), |
| 290 |
(8,4),(5,7),(3,5),(0,5),(7,6),(5,9),(1,2),(4,2),(8,5),(8,7),(2,8),(1,8), |
| 291 |
(4,3),(1,6),(7,8),(3,7),(4,6),(1,1),(3,0),(1,6),(2,0),(3,4),(4,8),(3,9), |
| 292 |
(8,0),(4,9),(4,0),(3,9),(6,4),(7,4),(5,8),(4,7),(7,3),(5,9),(2,3),(7,3), |
| 293 |
(0,4),(5,9),(9,8),(4,2),(3,6),(2,6),(1,8),(7,0),(0,0),(2,3),(1,2),(3,3), |
| 294 |
(2,7),(6,0),(9,0),(6,9),(4,6),(9,8),(0,7),(9,1),(9,6),(4,3),(7,7),(7,7), |
| 295 |
(4,1),(4,7),(7,3),(2,8),(5,8),(8,9),(3,9),(7,7),(0,8),(4,9),(3,2),(5,0), |
| 296 |
(1,7),(0,3),(2,9),(9,7),(7,5),(6,9),(8,5),(3,6),(1,1),(2,8),(7,9),(4,9), |
| 297 |
(6,6),(5,9),(5,3),(9,8),(3,3),(5,6),(0,9),(3,9),(7,9),(7,3),(5,2),(1,4), |
| 298 |
(4,4),(8,2),(2,2),(8,3),(9,1),(4,9),(9,8),(1,8),(1,8),(9,1),(1,1),(3,0), |
| 299 |
(4,6),(9,3),(3,3),(5,2),(0,1),(3,4),(3,2),(1,3),(4,4),(7,0),(4,6),(7,2), |
| 300 |
(4,5),(8,7),(7,8),(8,1),(3,5),(0,6),(3,5),(2,1),(4,4),(3,4),(2,1),(4,1); |
| 301 |
INSERT INTO t1 SELECT * FROM t1; |
| 302 |
ALTER TABLE t1 DROP a; |
| 303 |
# Exploit MDEV-17468 to force the table definition to be reloaded |
| 304 |
ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; |
| 305 |
DROP TABLE t1; |
| 306 |
|
| 307 |
--echo # |
| 308 |
--echo # MDEV-19030 Assertion index->n_core_null_bytes <= ... failed |
| 309 |
--echo # in rec_init_offsets after instant DROP COLUMN |
| 310 |
--echo # |
| 311 |
CREATE TABLE t1 (a INT, b INT NOT NULL DEFAULT 0) ENGINE=InnoDB; |
| 312 |
INSERT INTO t1 () VALUES (),(),(),(); |
| 313 |
INSERT INTO t1 SELECT * FROM t1; |
| 314 |
INSERT INTO t1 SELECT * FROM t1; |
| 315 |
INSERT INTO t1 SELECT * FROM t1; |
| 316 |
INSERT INTO t1 SELECT * FROM t1; |
| 317 |
INSERT INTO t1 SELECT * FROM t1; |
| 318 |
ALTER TABLE t1 FORCE; |
| 319 |
INSERT INTO t1 SELECT * FROM t1; |
| 320 |
ALTER TABLE t1 DROP a, ADD a SMALLINT NOT NULL; |
| 321 |
INSERT INTO t1 SELECT * FROM t1; |
| 322 |
INSERT INTO t1 SELECT * FROM t1; |
| 323 |
# Exploit MDEV-17468 to force the table definition to be reloaded |
| 324 |
ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; |
| 325 |
DROP TABLE t1; |
| 326 |
|
| 327 |
--echo # |
| 328 |
--echo # MDEV-18623 Assertion after DROP FULLTEXT INDEX and removing NOT NULL |
| 329 |
--echo # |
| 330 |
CREATE TABLE t1 (c TEXT NOT NULL, FULLTEXT INDEX ftidx(c)) ENGINE=InnoDB |
| 331 |
ROW_FORMAT=REDUNDANT; |
| 332 |
ALTER TABLE t1 DROP INDEX ftidx; |
| 333 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED |
| 334 |
ALTER TABLE t1 MODIFY c TEXT NULL, ALGORITHM=INSTANT; |
| 335 |
ALTER TABLE t1 MODIFY c TEXT NULL; |
| 336 |
DROP TABLE t1; |
| 337 |
|
| 338 |
--echo # |
| 339 |
--echo # MDEV-20048 dtuple_get_nth_field(): Assertion 'n < tuple->n_fields' |
| 340 |
--echo # failed on ROLLBACK after instant DROP COLUMN |
| 341 |
--echo # |
| 342 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 343 |
INSERT INTO t1 VALUES (1); |
| 344 |
ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT; |
| 345 |
SET @b = REPEAT('b', @@innodb_page_size / 2 + 1); |
| 346 |
INSERT INTO t1 VALUES(2, @b), (3, @b); |
| 347 |
BEGIN; |
| 348 |
DELETE FROM t1 WHERE a=2; |
| 349 |
|
| 350 |
# Stop purge so that it doesn't remove the delete-marked entry. |
| 351 |
connect (purge_control,localhost,root); |
| 352 |
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
| 353 |
connection default; |
| 354 |
|
| 355 |
COMMIT; |
| 356 |
|
| 357 |
ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; |
| 358 |
BEGIN; |
| 359 |
INSERT INTO t1 VALUES (2); |
| 360 |
|
| 361 |
connection purge_control; |
| 362 |
SELECT * FROM t1; |
| 363 |
disconnect purge_control; |
| 364 |
connection default; |
| 365 |
ROLLBACK; |
| 366 |
|
| 367 |
SELECT * FROM t1; |
| 368 |
DROP TABLE t1; |
| 369 |
|
| 370 |
|
| 371 |
--echo # |
| 372 |
--echo # MDEV-20479: assertion failure in dict_table_get_nth_col() after INSTANT DROP COLUMN |
| 373 |
--echo # |
| 374 |
|
| 375 |
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; |
| 376 |
ALTER TABLE t1 ADD COLUMN (b INT, c INT, d INT, e INT NOT NULL DEFAULT 0); |
| 377 |
ALTER TABLE t1 ADD UNIQUE INDEX(e); |
| 378 |
ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e; |
| 379 |
DROP TABLE t1; |
| 380 |
|
| 381 |
--echo # |
| 382 |
--echo # MDEV-20565 Assertion on CHANGE COLUMN...SYSTEM VERSIONING |
| 383 |
--echo # |
| 384 |
set @@system_versioning_alter_history = keep; |
| 385 |
CREATE TABLE t (a INT WITHOUT SYSTEM VERSIONING, b INT) ENGINE=InnoDB |
| 386 |
WITH SYSTEM VERSIONING; |
| 387 |
ALTER TABLE t CHANGE COLUMN a alpha INT WITH SYSTEM VERSIONING, |
| 388 |
ALGORITHM=INSTANT; |
| 389 |
DROP TABLE t; |
| 390 |
|
| 391 |
CREATE TABLE t (alpha INT, b INT) ENGINE=InnoDB WITH SYSTEM VERSIONING; |
| 392 |
ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING, |
| 393 |
ALGORITHM=INSTANT; |
| 394 |
DROP TABLE t; |
| 395 |
set @@system_versioning_alter_history = error; |
| 396 |
|
| 397 |
--echo # |
| 398 |
--echo # MDEV-20117 Assertion 0 failed in row_sel_get_clust_rec_for_mysql |
| 399 |
--echo # |
| 400 |
|
| 401 |
# This is not repeating the bug itself, but demonstrating that both |
| 402 |
# parts of the fix are needed. |
| 403 |
# To repeat the original bug, we should be somehow able to empty |
| 404 |
# the table of user records while purgeable undo log records exist. |
| 405 |
CREATE TABLE t (b INT PRIMARY KEY) ENGINE=InnoDB; |
| 406 |
INSERT INTO t SET b=1; |
| 407 |
ALTER TABLE t ADD COLUMN a INT FIRST, ALGORITHM=INSTANT; |
| 408 |
DELETE FROM t; |
| 409 |
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; |
| 410 |
# If page_cur_delete_rec() emptied the page (and wrongly reset the |
| 411 |
# page type) during the previous ALTER TABLE, the following would hit |
| 412 |
# an assertion failure because of root page type mismatch. |
| 413 |
ALTER TABLE t DROP COLUMN c, ALGORITHM=INSTANT; |
| 414 |
SELECT * FROM t; |
| 415 |
DROP TABLE t; |
| 416 |
|
| 417 |
# The following is nondeterministically repeating the bug in a |
| 418 |
# different scenario: the table is empty at the time the ALTER TABLE |
| 419 |
# is invoked, apparently because purge already processed the records |
| 420 |
# for the DELETE, but not the record for the UPDATE. |
| 421 |
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT) ENGINE=InnoDB; |
| 422 |
INSERT INTO t1 SET a=1; |
| 423 |
INSERT INTO t1 SET a=2; |
| 424 |
BEGIN; |
| 425 |
UPDATE t1 SET b=1; |
| 426 |
DELETE FROM t1; |
| 427 |
COMMIT; |
| 428 |
|
| 429 |
ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e; |
| 430 |
--source include/wait_all_purged.inc |
| 431 |
SELECT * FROM t1; |
| 432 |
DROP TABLE t1; |
| 433 |
|
| 434 |
--echo # |
| 435 |
--echo # MDEV-20190 Instant operation fails when add column and collation |
| 436 |
--echo # change on non-indexed column |
| 437 |
--echo # |
| 438 |
|
| 439 |
CREATE TABLE t1 (a CHAR)ENGINE=INNODB; |
| 440 |
ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; |
| 441 |
ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; |
| 442 |
SHOW CREATE TABLE t1; |
| 443 |
DROP TABLE t1; |
| 444 |
|
| 445 |
CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; |
| 446 |
ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; |
| 447 |
ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; |
| 448 |
SHOW CREATE TABLE t1; |
| 449 |
DROP TABLE t1; |
| 450 |
|
| 451 |
CREATE TABLE t1 (a CHAR NOT NULL) CHARSET latin2 COLLATE latin2_bin |
| 452 |
ENGINE=InnoDB ROW_FORMAT=REDUNDANT; |
| 453 |
ALTER TABLE t1 DEFAULT COLLATE = latin2_general_ci; |
| 454 |
ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; |
| 455 |
SHOW CREATE TABLE t1; |
| 456 |
DROP TABLE t1; |
| 457 |
|
| 458 |
--echo # |
| 459 |
--echo # MDEV-21645 SIGSEGV in innobase_get_computed_value |
| 460 |
--echo # |
| 461 |
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, va INTEGER GENERATED ALWAYS AS (a)) |
| 462 |
ENGINE=InnoDB; |
| 463 |
INSERT INTO t1 SET a=1, b=NULL; |
| 464 |
ALTER TABLE t1 MODIFY COLUMN b INT FIRST; |
| 465 |
ALTER TABLE t1 ADD UNIQUE INDEX (va); |
| 466 |
DROP TABLE t1; |
| 467 |
|
| 468 |
--echo # |
| 469 |
--echo # MDEV-22651 Assertion dict_col_get_fixed_size... |
| 470 |
--echo # in dict_table_t::init_instant() |
| 471 |
--echo # |
| 472 |
--source include/have_innodb.inc |
| 473 |
CREATE TABLE t (i INT PRIMARY KEY) ENGINE=InnoDB; |
| 474 |
INSERT INTO t SET i=1; |
| 475 |
ALTER TABLE t ADD e CHAR(255) CHARACTER SET UTF32 FIRST, ALGORITHM=INSTANT; |
| 476 |
DROP TABLE t; |
| 477 |
|
| 478 |
--echo # |
| 479 |
--echo # MDEV-23499 Assertion c.same_type(*o) failed |
| 480 |
--echo # |
| 481 |
CREATE TABLE t (pk SERIAL, b TEXT CHARACTER SET utf8) ENGINE=InnoDB; |
| 482 |
ALTER TABLE t MODIFY b TEXT CHARACTER SET utf8mb4 FIRST; |
| 483 |
DROP TABLE t; |
| 484 |
|
| 485 |
--echo # |
| 486 |
--echo # MDEV-23672 Assertion `v.v_indexes.empty()' failed in dict_table_t::instant_column |
| 487 |
--echo # |
| 488 |
create table t1 ( |
| 489 |
col_int integer, |
| 490 |
col_text text not null, |
| 491 |
col_int_g integer generated always as (col_int) unique, |
| 492 |
col_text_g text generated always as (substr(col_text,1,499)) ) |
| 493 |
engine innodb row_format = redundant; |
| 494 |
insert into t1 (col_int,col_text) values (0, 'a'), (null, 'b'); |
| 495 |
alter table t1 modify column col_text text null, algorithm = instant; |
| 496 |
insert into t1 (col_int,col_text) values (1, null), (null, null); |
| 497 |
update t1 set col_text= 'd'; |
| 498 |
select * from t1; |
| 499 |
check table t1; |
| 500 |
drop table t1; |
| 501 |
|
| 502 |
--echo # |
| 503 |
--echo # MDEV-24072 Assertion 'ib_table.n_v_cols' failed |
| 504 |
--echo # in instant_alter_column_possible() |
| 505 |
--echo # |
| 506 |
CREATE TABLE t (a BLOB) ENGINE=InnoDB; |
| 507 |
INSERT INTO t VALUES ('a'); |
| 508 |
--error ER_TRUNCATED_WRONG_VALUE |
| 509 |
ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a+1) VIRTUAL, ADD KEY(c); |
| 510 |
--enable_info |
| 511 |
ALTER TABLE t ADD d INT; |
| 512 |
--disable_info |
| 513 |
DROP TABLE t; |
| 514 |
|
| 515 |
--echo # |
| 516 |
--echo # MDEV-28060 Online DDL fails while checking for instant |
| 517 |
--echo # alter condition |
| 518 |
--echo # |
| 519 |
CREATE TABLE t1(f1 CHAR(10) NOT NULL)ROW_FORMAT=REDUNDANT,ENGINE=InnoDB; |
| 520 |
ALTER TABLE t1 ADD COLUMN(f2 INT NOT NULL, f3 INT NOT NULL, |
| 521 |
f4 INT NOT NULL, f5 INT NOT NULL), |
| 522 |
CHANGE COLUMN f1 f1 CHAR(10) DEFAULT NULL; |
| 523 |
DROP TABLE t1; |
| 524 |
|
| 525 |
--echo # |
| 526 |
--echo # MDEV-26420 Buffer overflow on instant ADD/DROP of generated column |
| 527 |
--echo # |
| 528 |
CREATE TABLE t1 (i int AS (0) STORED, j INT) ENGINE=InnoDB; |
| 529 |
ALTER TABLE t1 ADD COLUMN i INT GENERATED ALWAYS AS (1), DROP COLUMN i; |
| 530 |
DROP TABLE t1; |
| 531 |
|
| 532 |
--echo # |
| 533 |
--echo # MDEV-18322 Assertion "wrong_page_type" on instant ALTER |
| 534 |
--echo # |
| 535 |
|
| 536 |
DELIMITER $$; |
| 537 |
BEGIN NOT ATOMIC |
| 538 |
DECLARE c TEXT |
| 539 |
DEFAULT(SELECT CONCAT('CREATE TABLE t1 (c', |
| 540 |
GROUP_CONCAT(seq SEPARATOR ' CHAR(200), c'), |
| 541 |
' CHAR(211)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT') |
| 542 |
FROM seq_1_to_40); |
| 543 |
EXECUTE IMMEDIATE c; |
| 544 |
END; |
| 545 |
$$ |
| 546 |
DELIMITER ;$$ |
| 547 |
INSERT INTO t1 SET c1=NULL; |
| 548 |
--error ER_TOO_BIG_ROWSIZE |
| 549 |
ALTER TABLE t1 ADD c41 INT FIRST; |
| 550 |
--error ER_TOO_BIG_ROWSIZE |
| 551 |
ALTER TABLE t1 ADD c41 INT FIRST; |
| 552 |
CHECK TABLE t1; |
| 553 |
SELECT COUNT(*) FROM t1; |
| 554 |
DROP TABLE t1; |
| 555 |
|
| 556 |
--echo # End of 10.4 tests |
| 557 |
|
| 558 |
--echo # |
| 559 |
--echo # MDEV-29010 Table cannot be loaded after instant ALTER |
| 560 |
--echo # |
| 561 |
CREATE TABLE t1 (a CHAR(255), b INT, |
| 562 |
c INT as (b) VIRTUAL)ENGINE=InnoDB CHARACTER SET utf32; |
| 563 |
ALTER TABLE t1 DROP COLUMN a; |
| 564 |
ALTER TABLE t1 DROP COLUMN c; |
| 565 |
DROP TABLE t1; |
| 566 |
|
| 567 |
--echo # |
| 568 |
--echo # MDEV-35122 Incorrect NULL value handling for instantly |
| 569 |
--echo # dropped BLOB columns |
| 570 |
--echo # |
| 571 |
CREATE TABLE t1 (c1 INT, c2 BLOB, c3 BLOB NOT NULL) ROW_FORMAT=REDUNDANT,ENGINE=InnoDB; |
| 572 |
ALTER TABLE t1 DROP c2; |
| 573 |
ALTER TABLE t1 DROP c3; |
| 574 |
INSERT INTO t1 VALUES(1); |
| 575 |
DROP TABLE t1; |
| 576 |
|
| 577 |
--echo # End of 10.5 tests |
| 578 |
|
| 579 |
--echo # End of 10.6 tests |
| 580 |
|
| 581 |
--source include/test_db_charset_restore.inc |
| 582 |
|
| 583 |
SET GLOBAL innodb_stats_persistent = @save_stats_persistent; |