MySQL Bugs: #81819: ALTER TABLE...LOCK=NONE is not allowed when FULLTEXT INDEX exists

Bug #81819	ALTER TABLE...LOCK=NONE is not allowed when FULLTEXT INDEX exists
Submitted:	12 Jun 2016 12:25	Modified:	14 Jun 2016 7:26
Reporter:	teo teo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It's unacceptable, in the 21st century, that adding a column to a huge table locks all writes on the whole table for all the duration of the ALTER operation.

That basically means a few hours of downtime if adding columns to a table with as few as a million rows.

There are answers on Stack Overflow on how to write a script that avoids that (by creating a new table with the additional column, copying the data, and ensuring the inserts done to the source table during the copy are not lost via triggers or something). There's even a Percona Toolkit tool that does that.

If a handwritten script or an external tool can do that, there's no reason the MySQL server shouldn't do that by itself transparently. You can't just assume that altering the schema is something that doesn't happen once an application is deployed. That's a narrow-minded design to say the least.

How to repeat:
It's unacceptable, in the 21st century, that adding a column to a huge table locks all writes on the whole table for all the duration of the ALTER operation.

That basically means a few hours of downtime if adding columns to a table with as few as a million rows.

There are answers on Stack Overflow on how to write a script that avoids that (by creating a new table with the additional column, copying the data, and ensuring the inserts done to the source table during the copy are not lost via triggers or something). There's even a Percona Toolkit tool that does that.

If a handwritten script or an external tool can do that, there's no reason the MySQL server shouldn't do that by itself transparently. You can't just assume that altering the schema is something that doesn't happen once an application is deployed. That's a narrow-minded design to say the least.

It should use IMPLACE as default according to 
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
(however note that the column added cannot be an AUTO_ICREMENT column)
  
This is an InnoDb specific feature and does not work the same with other storage engines like MyISAM

-- Peter
-- not a MySQL/Oracle person.

I don't know if it uses INPLACE, but what's sure is that all INSERT and UPDATE queries in the same table remain locked in the state "waiting for table metadata log" or something like that for the whole duration of the ALTER operation (while it is in "copying to tmp table")

Maybe you could start by showing the actual ALTER statement and the output of SHOW CREATE TABLE from either before or after the ALTER?

Hi !

Thank you for your bug report. However, first read what was written about AUTO-INC columns and, second, please send us all info as requested by my friend, Hartmut Holzgrafe..

Thanks.

I'm talking about regular columns, not auto-increment.

I don't see why you would need the "show create table" and the "alter" statement, they are pretty straight forward to make up from the report; however I'll attach them later if I have some spare time to waste.

And this is not a feature request. Being unable to do a small schema change (such as adding a column) without hours of downtime, when it could be easily avoided, is a bug, or a poor design which is the exact same thing.

"I don't see why you would need the "show create table" and the "alter" statement, they are pretty straight forward to make up from the report; however I'll attach them later if I have some spare time to waste."

The thing is that right now you are wasting our time as in general adding a column online without write locking the tabele *is* possilble on 5.6+ (it wasn't in earlier versions).

It only works if the underlying storage engine also supports the feature.

This is the case for e.g. InnoDB and ndbcluster tables, but not for MyISAM.

That's the reason why I asked for the SHOW CREATE TABLE output and the actual ALTER you are trying to perform:

To see whether you are using a storage engine that doesn't support the feature yet, or whether you're hitting one of the other edge cases where INPLACE ALTER isn't possible.

As long as you don't provide proper "how to reproduce" information we're indeed all wasting our time here :(

Ok, so below I attach the information you requested.

However, 
> The thing is [...] in general adding a column online without write locking 
> the tabele *is* possilble on 5.6+ (it wasn't in earlier versions).
> [...] This is the case for e.g. InnoDB 
[which is the one I'm using]

That contraddicts the documentation at http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

"""
While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are ***STALLED UNTIL THE NEW TABLE IS READY***, then are automatically redirected to the new table without any failed updates.
"""
(emphasys added)

"""
For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:
[adding a column is not listed among those]
"""

Anyway, here is the ALTER statement:

ALTER TABLE `post` ADD `vbmpw_has_phone` tinyint(1) NOT NULL default '0'

And here is SHOW CREATE TABLE after it (the SHOW CREATE TABLE before it was the same without the added column).

CREATE TABLE `post` (
 `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `threadid` int(10) unsigned NOT NULL DEFAULT '0',
 `parentid` int(10) unsigned NOT NULL DEFAULT '0',
 `username` varchar(100) NOT NULL DEFAULT '',
 `userid` int(10) unsigned NOT NULL DEFAULT '0',
 `title` varchar(250) NOT NULL DEFAULT '',
 `dateline` int(10) unsigned NOT NULL DEFAULT '0',
 `pagetext` mediumtext,
 `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
 `showsignature` smallint(6) NOT NULL DEFAULT '0',
 `ipaddress` varchar(15) NOT NULL DEFAULT '',
 `proxyip` varchar(20) NOT NULL DEFAULT '',
 `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
 `visible` smallint(6) NOT NULL DEFAULT '0',
 `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
 `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
 `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
 `post_thanks_amount` int(10) unsigned NOT NULL DEFAULT '0',
 `ame_flag` tinyint(4) NOT NULL DEFAULT '0',
 `post_groan_amount` int(10) unsigned NOT NULL DEFAULT '0',
 `nominate_topic_amount` int(10) unsigned NOT NULL DEFAULT '0',
 `nominate_topic_award` int(10) unsigned NOT NULL DEFAULT '0',
 `nominate_topic_award_rank` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `contestid` int(11) unsigned NOT NULL DEFAULT '0',
 `vbpsmt_lastattachments_pending` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `vbmpw_has_phone` tinyint(1) NOT NULL DEFAULT '0',
 `vbmpw_phone_pending` tinyint(1) NOT NULL DEFAULT '0',
 `vbmpw_partner_links` mediumtext,
 PRIMARY KEY (`postid`),
 KEY `userid` (`userid`),
 KEY `dateline` (`dateline`),
 KEY `threadid_userid` (`threadid`,`userid`),
 KEY `threadid` (`threadid`),
 KEY `reportthreadid` (`reportthreadid`),
 KEY `iconid` (`iconid`),
 KEY `nominate_topic_amount` (`nominate_topic_amount`),
 KEY `nominate_amount_per_contest` (`contestid`,`nominate_topic_amount`),
 KEY `ipaddress` (`ipaddress`),
 KEY `post_thanks_amount` (`post_thanks_amount`),
 KEY `vbpsmt_lastattachments_pending` (`vbpsmt_lastattachments_pending`),
 KEY `vbmpw_phone_pending` (`vbmpw_phone_pending`),
 FULLTEXT KEY `title` (`title`,`pagetext`)
) ENGINE=InnoDB AUTO_INCREMENT=1802754 DEFAULT CHARSET=utf8

This is a duplicate of #76012.

Hence, not a bug ....

I don't see how this is a duplicate of that one.

That was about hidden columns/indexes related to fulltext indexes not being dropped after the fulltext index is removed.

In my case I do have a fulltext index on some columns of the table.

So, I guess what you are telling me that adding a column online without blocking writes during the alter operation cannot be done if the table has a fulltext index?? Besides the fact that this is not documented, I don't see how this can be acceptable.

I can repeat this bug on the latest MySQL development code
(5.6.32 and 5.7.14) as follows:

CREATE TABLE post (title varchar(250), FULLTEXT KEY(title)) ENGINE=InnoDB;
ALTER TABLE post ADD c tinyint(1) NOT NULL default '0', LOCK=NONE;
DROP TABLE post;

This will report a misleading error:

mysqltest: At line 2: query 'ALTER TABLE post ADD c tinyint(1) NOT NULL default '0', LOCK=NONE' failed: 1846: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.

The message is misleading, because the table only contains one FULLTEXT INDEX.

However, LOCK=NONE is never supported when a FULLTEXT INDEX exists
on the table. Similarly, LOCK=NONE is not supported when SPATIAL INDEX
(introduced in MySQL 5.7) exist. Speaking as the author of WL#6255 which
implemented ALTER TABLE...LOCK=NONE for InnoDB B-tree indexes in MySQL 5.6,
I share the bug reporter's disappointment.