Bug #84411 Table rebuild with partitioned table while creating VIRTUAL column
Submitted: 4 Jan 2017 20:23 Modified: 9 Jan 2017 17:19
Reporter: Shahriyar Rzayev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[4 Jan 2017 20:23] Shahriyar Rzayev
Description:
Hi dear all,

Sample table is:

CREATE TABLE `sbtest4` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */

select count(*) from sbtest4;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+
1 row in set (0.34 sec)

Trying to add generated column:
With STORED:

alter table sbtest4 add column json_test_s json generated always as (json_array(k,c,pad)) stored;
Query OK, 40000 rows affected (4.89 sec)
Records: 40000  Duplicates: 0  Warnings: 0

Wit VIRTUAL:

alter table sbtest4 add column json_test_v json generated always as (json_array(k,c,pad)) virtual;
Query OK, 40000 rows affected (5.32 sec)
Records: 40000  Duplicates: 0  Warnings: 0

With ordinary table:

alter table sbtest1 add column json_test_v json generated always as (json_array(k,c,pad)) virtual;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

The situation is getting serious when I tried to drop virtual column for partitioned table:

alter table sbtest4 drop column json_test_v;
Query OK, 40000 rows affected (5.23 sec)
Records: 40000  Duplicates: 0  Warnings: 0

So it takes same amount of time to drop virtual column as for "usual" column with partitioned table.
Again same thing will not affected by ordinary table:

alter table sbtest1 drop column `json_test_v`;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
See description
[5 Jan 2017 7:06] MySQL Verification Team
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[9 Jan 2017 17:19] Daniel Price
Posted by developer:
 
The following documentation has been updated:
https://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.html

"For partitioned tables, ADD COLUMN and DROP COLUMN are not in-place
operations for virtual columns."

The change should appear online soon.

Thank you for the bug report.