Bug #76012 | If you have a fulltext index and drop it you can no longer perform online ddl | ||
---|---|---|---|
Submitted: | 24 Feb 2015 4:52 | Modified: | 16 Jun 2015 14:33 |
Reporter: | Brian Gruber | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.6.22, 5.6.24, 5.7.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | fulltext innodb online ddl |
[24 Feb 2015 4:52]
Brian Gruber
[24 Feb 2015 6:29]
MySQL Verification Team
Hello Brian Gruber, Thank you for the report and test case. Also, observed this on 5.6.24 and 5.7.7 builds. Workaround I see is to use LOCK=SHARED... mysql> ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED. mysql> ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=SHARED; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table so28666643a\G *************************** 1. row *************************** Table: so28666643a Create Table: CREATE TABLE `so28666643a` ( `ai` int(11) NOT NULL AUTO_INCREMENT, `txt` text NOT NULL, `x` tinyint(4) DEFAULT NULL, PRIMARY KEY (`ai`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Thanks, Umesh
[24 Feb 2015 6:50]
Brian Gruber
But if you used LOCK=SHARED wouldn't that block the table from any writes while the alter is occurring?
[24 Feb 2015 8:21]
Marko Mäkelä
Posted by developer: Creating a fulltext index will also create a hidden FTS_DOC_ID column and a unique index FTS_DOC_ID_INDEX(FTS_DOC_ID), unless these were explicitly created by the user. These hidden columns cannot be removed, unless the table is rebuilt. By default, we want DROP INDEX to be fast even when it is dropping the last remaining fulltext index. A table rebuild could be requested by adding a FORCE qualifier. That said, I can verify that the current behaviour could be improved. I tried modifying the test as follows on our current development version of MySQL 5.7: CREATE TABLE so28666643a (ai INT AUTO_INCREMENT PRIMARY KEY, txt TEXT NOT NULL ) ENGINE=InnoDB; ALTER TABLE so28666643a ADD FULLTEXT(txt); ALTER TABLE so28666643a DROP INDEX txt, force, lock=none; ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=NONE; DROP TABLE so28666643a; But, also the DROP INDEX was refused due to the same reason: query 'ALTER TABLE so28666643a DROP INDEX txt, force, lock=none' failed: 1846: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED. Then, I added some instrumentation: CREATE TABLE so28666643a (ai INT AUTO_INCREMENT PRIMARY KEY, txt TEXT NOT NULL ) ENGINE=InnoDB; ALTER TABLE so28666643a ADD FULLTEXT(txt); ALTER TABLE so28666643a DROP INDEX txt; use information_schema; SELECT c.name FROM innodb_sys_columns c INNER JOIN innodb_sys_tables t ON c.table_id=t.table_id WHERE t.name='test/so28666643a'; SELECT i.name FROM innodb_sys_indexes i INNER JOIN innodb_sys_tables t ON i.table_id=t.table_id WHERE t.name='test/so28666643a'; use test; ALTER TABLE so28666643a force, lock=none; ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=NONE; DROP TABLE so28666643a; The output of running the above test shows that the hidden FTS_DOC_ID_INDEX(FTS_DOC_ID) were not dropped, even though FORCE was specified to request a rebuild. They should be safe to drop when the table is rebuilt. Here is the output: CREATE TABLE so28666643a (ai INT AUTO_INCREMENT PRIMARY KEY, txt TEXT NOT NULL ) ENGINE=InnoDB; ALTER TABLE so28666643a ADD FULLTEXT(txt); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID ALTER TABLE so28666643a DROP INDEX txt; use information_schema; SELECT c.name FROM innodb_sys_columns c INNER JOIN innodb_sys_tables t ON c.table_id=t.table_id WHERE t.name='test/so28666643a'; name ai txt FTS_DOC_ID SELECT i.name FROM innodb_sys_indexes i INNER JOIN innodb_sys_tables t ON i.table_id=t.table_id WHERE t.name='test/so28666643a'; name PRIMARY FTS_DOC_ID_INDEX use test; innodb.f [ fail ] Test ended at 2015-02-24 10:12:51 CURRENT_TEST: innodb.f mysqltest: At line 21: query 'ALTER TABLE so28666643a force, lock=none' failed: 1846: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.
[16 Jun 2015 14:28]
Daniel Price
Posted by developer: commit 436f1c531feaa621f89a69697b3349df5c445e42 Author: Thirunarayanan Balathandayuthapani <thirunarayanan.balathandayuth@oracle Date: Mon Jun 15 19:00:05 2015 +0530 Bug #20590013 IF YOU HAVE A FULLTEXT INDEX AND DROP IT YOU CAN NO LONGER PERFORM ONLINE DDL Problem: ======= After dropping all FTS indexes, FTS_DOC_ID and FTS_DOC_ID_INDEX will be part of the table. Online DDL is not allowed when FTS_DOC_ID and FTS_DOC_ID_INDEX is a part of the table. Solution: ======== InnoDB allows the online DDL to happen when there is only hidden FTS_DOC_ID and FTS_DOC_ID_INDEX are present. Hidden column FTS_DOC_Id and FTS_DOC_ID_INDEX will be part of the table till the rebuild DDL invokes.
[16 Jun 2015 14:33]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.6.26, 5.7.8, 5.8.0 releases, and here's the changelog entry: After dropping a full-text search index, the hidden FTS_DOC_ID and FTS_DOC_ID_INDEX columns prevented online DDL operations. Thank you for the bug report.