Bug #83557 Can't use LOCK=NONE to drop columns in table with virtual columns
| Submitted: | 27 Oct 2016 3:08 | Modified: | 21 Apr 11:14 |
|---|---|---|---|
| Reporter: | monty solomon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.7.16 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any |
[27 Oct 2016 3:08]
monty solomon
[27 Oct 2016 9:02]
MySQL Verification Team
Hello Monty, Thank you for the report and test case. Thanks, Umesh
[27 Oct 2016 9:03]
MySQL Verification Team
-- 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>
[21 Apr 11:14]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 8.4.9 and 9.7.0 release notes: It was not possible to drop columns, on a table with virtual columns, using LOCK=NONE.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.