What does the MySQL 5.6 error "InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED" mean?

Question

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. Am I missing something obvious? SHOW CREATE TABLE: ``` CREATE TABLE `pages` ( `ID` char(64) CHARACTER SET latin1 NOT NULL, `auto_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `siteID` bigint(20) unsigned NOT NULL, `recordingID` char(64) DEFAULT NULL, `dateCreated` datetime DEFAULT NULL, `ip` char(46) CHARACTER SET latin1 DEFAULT NULL, `referrer` text CHARACTER SET latin1, `refer…

Accepted Answer

There is no fix. The two workarounds include:

- rebuild the table, or

- `LOCK = SHARED`

-- neither of which meet the criterion of "no locks".

I would recommend filing a report at the MySQL bug tracker. In the meantime, here is a test case that fails for me too:

```
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.
```

Edit: thanks for the bug report.

What does the MySQL 5.6 error "InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED" mean?

Solution

Problem