MySQL 5.6错误"InnoDB目前支持一次创建一个FULLTEXT索引是什么?尝试LOCK = SHARED";意思是? [英] What does the MySQL 5.6 error "InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED" mean?
问题描述
在MySQL 5.6.22(特别是在Amazon RDS中运行)中,我曾经在一个表上有一个全文索引.从那以后,我放弃了该索引,转而使用sphinxsearch.数据库上的任何表上都没有全文索引.
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.
当我现在尝试执行在线DDL(例如,将tinyint列添加到具有LOCK = NONE的表中)时,出现此错误:
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当前一次支持一次创建FULLTEXT索引.尝试LOCK = SHARED.
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.
我缺少明显的东西吗?
显示创建表:
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
简单更改
ALTER TABLE `pages`
ADD COLUMN `is_ppc` TINYINT(1) , LOCK=NONE
错误
Error Code: 1846
LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.
推荐答案
没有修复.两种解决方法包括:
There is no fix. The two workarounds include:
- 重建表格,或
-
LOCK = SHARED
-都不符合无锁"标准.
-- neither of which meet the criterion of "no locks".
我建议在 MySQL错误跟踪器上提交报告.同时,这是一个对我来说也失败的测试用例:
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.
感谢您的错误报告.
这篇关于MySQL 5.6错误"InnoDB目前支持一次创建一个FULLTEXT索引是什么?尝试LOCK = SHARED";意思是?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!