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,
  `referrer_client` char(50) DEFAULT NULL,
  `referrer_from` char(255) DEFAULT NULL,
  `referrer_type` char(50) DEFAULT NULL,
  `referrer_network` char(50) DEFAULT NULL,
  `referrer_link` char(255) DEFAULT NULL,
  `campaign_source` char(50) DEFAULT NULL,
  `campaign_medium` char(32) DEFAULT NULL,
  `campaign_campaign` char(75) DEFAULT NULL,
  `url` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `keywords` char(100) CHARACTER SET latin1 DEFAULT NULL,
  `source` char(128) CHARACTER SET latin1 DEFAULT NULL,
  `os` char(30) CHARACTER SET latin1 DEFAULT NULL,
  `browser` char(30) CHARACTER SET latin1 DEFAULT NULL,
  `user_agent` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `agentFamily` char(50) DEFAULT NULL,
  `is_phone` tinyint(1) DEFAULT '0',
  `is_tablet` tinyint(1) DEFAULT '0',
  `is_desktop` tinyint(1) DEFAULT '0',
  `tags` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `device` char(65) DEFAULT NULL,
  `last_event_time` datetime DEFAULT NULL,
  `view_width` int(11) DEFAULT NULL,
  `view_height` int(11) DEFAULT NULL,
  `browser_width` int(11) DEFAULT NULL,
  `browser_height` int(11) DEFAULT NULL,
  `optimizely_experiments` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `optimizely_variations` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `is_paid` tinyint(1) DEFAULT '0',
  `typed` tinyint(1) DEFAULT '0',
  `organization` char(255) CHARACTER SET latin1 DEFAULT NULL,
  `country` char(75) CHARACTER SET latin1 DEFAULT NULL,
  `city` char(128) CHARACTER SET latin1 DEFAULT NULL,
  `region` char(10) DEFAULT NULL,
  `regionName` char(64) DEFAULT NULL,
  `country_code2` char(2) CHARACTER SET latin1 DEFAULT NULL,
  `postal` char(32) CHARACTER SET latin1 DEFAULT NULL,
  `latitude` char(30) DEFAULT NULL,
  `longitude` char(30) DEFAULT NULL,
  `custom_data` text CHARACTER SET latin1,
  `notes` text CHARACTER SET latin1,
  `title` char(255) DEFAULT NULL,
  `has_dom_tree` tinyint(1) DEFAULT '0',
  `time` int(11) DEFAULT NULL,
  PRIMARY KEY (`auto_id`),
  UNIQUE KEY `pageid` (`ID`),
  KEY `siteID` (`siteID`),
  KEY `dateCreated` (`dateCreated`),
  KEY `recordingID` (`recordingID`),
  KEY `siteID_url` (`siteID`,`url`)
) ENGINE=InnoDB AUTO_INCREMENT=90042803 DEFAULT CHARSET=utf8

Simple Alter

ALTER TABLE `pages`   
  ADD COLUMN `is_ppc` TINYINT(1) , LOCK=NONE

Error

Error Code: 1846
LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.

Answer 1

Found better workaround (solution),

I changed the table engine from InnoDB to MyISAM i.e.

ALTER TABLE `tablename` ENGINE='MyISAM';

and then I ran alter table to add fulltext column index:

ALTER TABLE `tablename` ADD FULLTEXT `asset_number` (`asset_number`);

After that, I changed back the table engine to InnoDB.

ALTER TABLE `tablename` ENGINE='InnoDB';

Answer 2

Here's what I did, In my case I had 5 full text indexes in the table. And I wanted to run an ALTER query on few of the columns in the table.

First, Remove the full text indexes from the table.

Second, Run your ALTER query.

Third, Just add the full text indexes back again.

Example:

/*FIRST QUERY*/
DROP INDEX fulltext_index1 ON table_name;
DROP INDEX fulltext_index2 ON table_name;

/*SECOND QUERY*/
ALTER TABLE table_name 
 CHANGE `price` `price` DECIMAL(10,2) DEFAULT 0.00  NOT NULL;

/*THIRD QUERY*/
ALTER TABLE table_name ADD FULLTEXT fulltext_index1(column_name1);
ALTER TABLE table_name ADD FULLTEXT fulltext_index2(column_name2);

So this way you don't have to remove column or do any extra work to retain data. But remember, this is only a one time job per table. In my case I never had to drop and add the indexes back again to the table. Thereafter the ALTER queries were running without errors.

Answer 3

There is no fix. The two workarounds include:

1. rebuild the table, or
2. 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.