1SET NAMES utf8mb4; 2# 3# MDEV-11233 CREATE FULLTEXT INDEX with a token 4# longer than 127 bytes crashes server 5# 6CREATE TABLE t(t TEXT CHARACTER SET utf8mb3) ENGINE=InnoDB; 7INSERT INTO t SET t=REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5); 8INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84); 9INSERT INTO t SET t=REPEAT('befor',17); 10INSERT INTO t SET t='BeforeTheIndexCreation'; 11CREATE FULLTEXT INDEX ft ON t(t); 12INSERT INTO t SET t='this was inserted after creating the index'; 13INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84); 14INSERT INTO t SET t=REPEAT('after',17); 15INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15); 16# The data below is not 3-byte UTF-8, but 4-byte chars. 17INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); 18Warnings: 19Warning 1366 Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column `test`.`t`.`t` at row 1 20INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); 21Warnings: 22Warning 1366 Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column `test`.`t`.`t` at row 1 23SELECT COUNT(*) FROM t WHERE MATCH t AGAINST 24(REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5)); 25COUNT(*) 261 27SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation'); 28COUNT(*) 291 30SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17)); 31COUNT(*) 320 33SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after'); 34COUNT(*) 351 36SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17)); 37COUNT(*) 380 39SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83)); 40COUNT(*) 410 42SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84)); 43COUNT(*) 441 45SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85)); 46COUNT(*) 470 48SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83)); 49COUNT(*) 500 51SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84)); 52COUNT(*) 531 54SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85)); 55COUNT(*) 560 57SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83)); 58COUNT(*) 590 60SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84)); 61COUNT(*) 620 63SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84)); 64COUNT(*) 650 66SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85)); 67COUNT(*) 680 69SELECT * FROM t; 70t 71็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้ 72００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００ 73beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor 74BeforeTheIndexCreation 75this was inserted after creating the index 76１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１ 77afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter 78甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文 79???????????????????????????????????????????????????????????????????????????????????? 80????????????????????????????????????????????????????????????????????????????????????? 81SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len; 82len COUNT(*) 83252 6 84DROP TABLE t; 85CREATE TABLE t(t TEXT CHARACTER SET utf8mb4) ENGINE=InnoDB; 86INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15); 87INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84); 88INSERT INTO t SET t=REPEAT('befor',17); 89INSERT INTO t SET t='BeforeTheIndexCreation'; 90CREATE FULLTEXT INDEX ft ON t(t); 91INSERT INTO t SET t='this was inserted after creating the index'; 92INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84); 93INSERT INTO t SET t=REPEAT('after',17); 94INSERT INTO t SET t=REPEAT(concat(repeat(_utf8mb3 0xE0B987, 4), repeat(_utf8mb3 0xE0B989, 5)), 5); 95INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); 96# The token below exceeds the 84-character limit. 97INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); 98SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15)); 99COUNT(*) 1001 101SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation'); 102COUNT(*) 1031 104SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17)); 105COUNT(*) 1060 107SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after'); 108COUNT(*) 1091 110SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17)); 111COUNT(*) 1120 113SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83)); 114COUNT(*) 1150 116SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84)); 117COUNT(*) 1181 119SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85)); 120COUNT(*) 1210 122SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83)); 123COUNT(*) 1240 125SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84)); 126COUNT(*) 1271 128SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85)); 129COUNT(*) 1300 131SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83)); 132COUNT(*) 1330 134SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84)); 135COUNT(*) 1360 137SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84)); 138COUNT(*) 1390 140SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85)); 141COUNT(*) 1420 143SELECT * FROM t; 144t 145甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文 146００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００ 147beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor 148BeforeTheIndexCreation 149this was inserted after creating the index 150１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１ 151afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter 152็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้ 153������������������������������������������������������������������������������������������������������������������������������������������������������������������������ 154�������������������������������������������������������������������������������������������������������������������������������������������������������������������������� 155SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len; 156len COUNT(*) 157336 6 158DROP TABLE t; 159CREATE TABLE t(t TEXT CHARACTER SET latin1, FULLTEXT INDEX(t)) 160ENGINE=InnoDB; 161SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len; 162len COUNT(*) 16384 6 164DROP TABLE t; 165# 166# MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in 167# trx_undo_page_report_modify upon optimizing table 168# under innodb_optimize_fulltext_only 169# 170CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB; 171INSERT INTO t1 (f1) VALUES ('foo'),('bar'); 172DELETE FROM t1 LIMIT 1; 173ALTER TABLE t1 ADD FULLTEXT KEY (f1); 174SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only; 175SET GLOBAL innodb_optimize_fulltext_only= 1; 176OPTIMIZE TABLE t1; 177Table Op Msg_type Msg_text 178test.t1 optimize status OK 179DROP TABLE t1; 180SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save; 181# 182# MDEV-24403 Segfault on CREATE TABLE with explicit FTS_DOC_ID_INDEX by multiple fields 183# 184create table t1 ( 185f1 int, f2 text, 186FTS_DOC_ID bigint unsigned not null, 187unique key FTS_DOC_ID_INDEX(FTS_DOC_ID, f1), 188fulltext (f2)) 189engine=innodb; 190ERROR 42000: Incorrect index name 'FTS_DOC_ID_INDEX' 191