MySQL Bugs: #83557: Can't use LOCK=NONE to drop columns in table with virtual columns

Bug #83557	Can't use LOCK=NONE to drop columns in table with virtual columns
Submitted:	27 Oct 2016 3:08	Modified:	27 Oct 2016 9:02
Reporter:	monty solomon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.16	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
When trying to drop columns using LOCK=NONE from a table that contains a virtual column the following error is generated even though the virtual columns are not being added or dropped.

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try LOCK=SHARED.

When trying to drop columns using ALGORITHM=INPLACE from a table that contains a virtual column the following error is generated even though the virtual columns are not being added or dropped.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.

The documentation states the following but there are no operations being performed on the virtual column in the ALTER statement.

https://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.html

ADD COLUMN and DROP COLUMN are in-place operations for virtual columns. However, adding or dropping a virtual column cannot be performed in combination with other ALTER TABLE operations.

There are no limitations described in the online DDL documentation.

https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

How to repeat:
mysql> CREATE TABLE `vtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `e` varchar(120) NOT NULL,
  `p` char(40) NOT NULL DEFAULT '',
  `s` char(10) DEFAULT NULL,
  `ed` varchar(120) GENERATED ALWAYS AS (substring_index(`e`,'@',-(1))) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_e` (`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> ALTER TABLE vtest DROP COLUMN p,LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try LOCK=SHARED.

mysql> ALTER TABLE vtest DROP COLUMN p,ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.

Suggested fix:
Permit ALTER statements using ALGORITHM INPLACE or LOCK NONE to work on tables containing virtual columns.

To accomplish non-blocking maintenance for a large table we need to execute the following statements but any other statements that reference the virtual column will fail between the first ALTER statement and the completion of the last ALTER statement.

ALTER TABLE vtest DROP COLUMN ed,LOCK=NONE;
ALTER TABLE vtest DROP COLUMN p,LOCK=NONE;
ALTER TABLE vtest ADD COLUMN `ed` varchar(120) GENERATED ALWAYS AS (substring_index(`e`,'@',-(1))) VIRTUAL NOT NULL, LOCK=NONE;

Hello Monty,

Thank you for the report and test case.

Thanks,
Umesh

-- 5.7.16

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `vtest` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `e` varchar(120) NOT NULL,
    ->   `p` char(40) NOT NULL DEFAULT '',
    ->   `s` char(10) DEFAULT NULL,
    ->   `ed` varchar(120) GENERATED ALWAYS AS (substring_index(`e`,'@',-(1))) VIRTUAL NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uniq_e` (`e`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE vtest DROP COLUMN p,LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try LOCK=SHARED.
mysql>  ALTER TABLE vtest DROP COLUMN p,ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
mysql>