Edit File: fts_compatibility.result
# Bug #18285007 COPY OF TABLES WITH INNODB FTS # FROM WINDOWS TO LINUX CAUSES CRASH # Simulating old non-windows(< 5.6.16) data directory SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a, b))engine=innodb; INSERT INTO t1 VALUES('TEST1', 'TEST2'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE DROP TABLE t1; # Simulating old windows (< 5.6.16) data directory SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; SET GLOBAL DEBUG='+d,innodb_test_wrong_windows_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a, b))engine=innodb; INSERT INTO t1 VALUES('TEST1', 'TEST2'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE DROP TABLE t1; # Simulation current non windows data directory CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a, b))engine=innodb; INSERT INTO t1 VALUES('TEST1', 'TEST2'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b TEST1 TEST2 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_INDEX_1 FTS_0 test/FTS_AUX_INDEX_2 FTS_0 test/FTS_AUX_INDEX_3 FTS_0 test/FTS_AUX_INDEX_4 FTS_0 test/FTS_AUX_INDEX_5 FTS_0 test/FTS_AUX_INDEX_6 FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE DROP TABLE t1; # Non windows aux table flag failure and rebuild the corrupted index. call mtr.add_suppression("\\[Warning\\] InnoDB: Parent table of FTS auxiliary table .* not found."); call mtr.add_suppression("\\[ERROR] InnoDB: Flagged corruption of .* in table .* in DROP ORPHANED TABLE"); call mtr.add_suppression("\\[Warning\\] InnoDB: Setting aux table .* to hex format failed."); SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, c TEXT, FULLTEXT `AB` (a, b), FULLTEXT `C1` (c)); INSERT INTO t1 VALUES('TEST1', 'TEST2', 'TEXT3'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2', 'TEXT5'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 SET GLOBAL DEBUG='-d,innodb_test_wrong_fts_sys_table_name'; SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); ERROR HY000: The table does not have FULLTEXT index to support this query SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); ERROR HY000: The table does not have FULLTEXT index to support this query ALTER TABLE t1 ADD FULLTEXT(b); ERROR HY000: Index corrupt: Fulltext index 'AB' is corrupt. you should drop this index first. # Restart the server SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); ERROR HY000: The table does not have FULLTEXT index to support this query SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); ERROR HY000: The table does not have FULLTEXT index to support this query # Drop the corrupted index. ALTER TABLE t1 DROP INDEX `AB`; ALTER TABLE t1 DROP INDEX `C1`; # Rebuild the index. ALTER TABLE t1 ADD FULLTEXT(a, b); ALTER TABLE t1 ADD FULLTEXT(c); # Read the record using fts index. SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 DROP TABLE t1; # Non windows parent table flag failure. SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, c TEXT, FULLTEXT(a, b), FULLTEXT(c)); INSERT INTO t1 VALUES('TEST1', 'TEST2', 'TEXT3'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2', 'TEXT5'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 SET GLOBAL DEBUG='-d,innodb_test_wrong_fts_sys_table_name'; SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 DROP TABLE t1; # Drop FTS table and rename the common tables. SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; SET GLOBAL DEBUG='+d,innodb_test_wrong_windows_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, c TEXT, FULLTEXT f1(a, b)); INSERT INTO t1 VALUES('TEST1', 'TEST2', 'TEXT3'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2', 'TEXT5'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 alter table t1 drop index f1; SET SESSION debug='-d,innodb_test_wrong_fts_sys_table_name'; SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name FTS_0 test/FTS_AUX_BEING_DELETED FTS_0 test/FTS_AUX_BEING_DELETED_CACHE FTS_0 test/FTS_AUX_CONFIG FTS_0 test/FTS_AUX_DELETED FTS_0 test/FTS_AUX_DELETED_CACHE SELECT * FROM t1; a b c TEST1 TEST2 TEXT3 TEXT1 TEXT2 TEXT5 DROP TABLE t1; # Rename failure for old windows data directory and rebuild the # corrupted index call mtr.add_suppression("\\[Warning\\] InnoDB: Failed to rename one aux table .* will revert all successful rename operations."); call mtr.add_suppression("\\[Warning\\] InnoDB: Rollback operations on all aux tables of table .* All the fts index associated with the table are marked as corrupted. Please rebuild the index again."); call mtr.add_suppression("\\[ERROR\\] InnoDB: Flagged corruption of .* in table .* in DROP ORPHANED TABLE"); SET GLOBAL DEBUG='+d,innodb_test_wrong_fts_aux_table_name'; SET GLOBAL DEBUG='+d,innodb_test_wrong_windows_fts_aux_table_name'; CREATE TABLE t1(a TEXT, b TEXT, c TEXT, FULLTEXT `AB`(a, b), FULLTEXT `C1`(c)); INSERT INTO t1 VALUES('TEST1', 'TEST2', 'TEXT3'); INSERT INTO t1 VALUES('TEXT1', 'TEXT2', 'TEXT5'); SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 SET SESSION debug='-d,innodb_test_wrong_fts_sys_table_name'; # Restart server. ALTER TABLE t1 ADD FULLTEXT(b); ERROR HY000: Upgrade index name failed, please use create index(alter table) algorithm copy to rebuild index. SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); ERROR HY000: The table does not have FULLTEXT index to support this query # Restart server. SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); ERROR HY000: The table does not have FULLTEXT index to support this query SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); ERROR HY000: The table does not have FULLTEXT index to support this query # Drop the corrupted index. ALTER TABLE t1 DROP INDEX `AB`; ALTER TABLE t1 DROP INDEX `C1`; # Rebuild the index. ALTER TABLE t1 ADD FULLTEXT(a, b); ALTER TABLE t1 ADD FULLTEXT(c); # Read the record using fts index. SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2'); a b c TEST1 TEST2 TEXT3 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5'); a b c TEXT1 TEXT2 TEXT5 TRUNCATE TABLE t1; DROP TABLE t1;