MySQL Bugs: #76012: If you have a fulltext index and drop it you can no longer perform online ddl

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:	None
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

Description:
See my original post on stackoverflow here:
http://stackoverflow.com/questions/28666643/what-does-the-mysql-5-6-error-innodb-presently...

---------

In MySQL 5.6.22 (specifically running on Amazon RDS) I once had a fulltext index on a table. I have since dropped that index in favor of using sphinxsearch. There are no more fulltext indexes on any of the tables on the database.

When I try to now perform an online DDL such as adding a tinyint column to a table with LOCK=NONE I get this error:

InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.

But, there are no fulltext indexes anywhere. I've even tried creating a read-replica in Amazon in hopes that the rebuilding of the database would fix that and I get the same error there.

How to repeat:
Here is a test case given by Rick James from that stackoverflow thread:

drop table IF EXISTS so28666643a;
CREATE TABLE so28666643a (ai INT AUTO_INCREMENT PRIMARY KEY,
    txt TEXT NOT NULL ) ENGINE=InnoDB;
ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=NONE;  -- works OK

drop table so28666643a;
CREATE TABLE so28666643a (ai INT AUTO_INCREMENT PRIMARY KEY,
    txt TEXT NOT NULL ) ENGINE=InnoDB; -- same
ALTER TABLE so28666643a ADD FULLTEXT(txt);
ALTER TABLE so28666643a DROP INDEX txt;
ALTER TABLE so28666643a ADD COLUMN x TINYINT, LOCK=NONE;  -- fails with strange message:

ERROR 1846 (0A000): LOCK=NONE is not supported.
Reason: InnoDB presently supports one FULLTEXT index creation at a time.
Try LOCK=SHARED.

Suggested fix:
I'm not sure what's thinking there is a fulltext index, but wherever that is it should reset if you've removed all fulltext indexes.

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

But if you used LOCK=SHARED wouldn't that block the table from any writes while the alter is occurring?

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.

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.

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.