MySQL Bugs: #84414: Tablespace of partitions altered after adding generatd virtual column

Bug #84414	Tablespace of partitions altered after adding generatd virtual column
Submitted:	4 Jan 2017 21:28	Modified:	11 Apr 2017 6:54
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

Description:
Hi dear all,

Here is the description of problem:

The sample table:

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.30 sec)

Altering to general tablespace:

alter table sbtest4 tablespace=partition_test;
Query OK, 0 rows affected (2.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

optimize table sbtest4;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| dbtest.sbtest4 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| dbtest.sbtest4 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.73 sec)

Adding virtual column:

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

The result of table:

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 '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL
) /*!50100 TABLESPACE `partition_test` */ ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) TABLESPACE = `partition_test` ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) TABLESPACE = `partition_test` ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) TABLESPACE = `partition_test` ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) TABLESPACE = `partition_test` ENGINE = InnoDB) */

So the question is -> why, "Virtual" column required to alter tablespace of partitions?

How to repeat:
See description

Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
This page has been revised:
https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

"An ALTER TABLE tbl_name TABLESPACE [=] tablespace_name operation on a partitioned table only modifies the table's default tablespace. It does not move the table partitions. However, after changing the default tablespace, an operation that rebuilds the table, such as an ALTER TABLE operation that uses ALGORITHM=COPY, moves the partitions to the default tablespace if another tablespace is not defined explicitly using the TABLESPACE [=] tablespace_name clause." 

Thank you for the bug report.

Example:

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,
  partition p5 values less than MAXVALUE) */;

mysql> SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
    ->  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES A
    ->  LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES B
    ->  ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%sbtest4%' ORDER BY A.NAME;
+-------------------+------------+-------------------+
| partition_name    | space_type | space_name        |
+-------------------+------------+-------------------+
| test/sbtest4#P#p1 | Single     | test/sbtest4#P#p1 |
| test/sbtest4#P#p2 | Single     | test/sbtest4#P#p2 |
| test/sbtest4#P#p3 | Single     | test/sbtest4#P#p3 |
| test/sbtest4#P#p4 | Single     | test/sbtest4#P#p4 |
| test/sbtest4#P#p5 | Single     | test/sbtest4#P#p5 |
+-------------------+------------+-------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLESPACE `t1` ADD DATAFILE 't1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE sbtest4 TABLESPACE=t1;
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name
    ->  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES A
    ->  LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES B
    ->  ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%sbtest4%' ORDER BY A.NAME;
+-------------------+------------+-------------------+
| partition_name    | space_type | space_name        |
+-------------------+------------+-------------------+
| test/sbtest4#P#p1 | Single     | test/sbtest4#P#p1 |
| test/sbtest4#P#p2 | Single     | test/sbtest4#P#p2 |
| test/sbtest4#P#p3 | Single     | test/sbtest4#P#p3 |
| test/sbtest4#P#p4 | Single     | test/sbtest4#P#p4 |
| test/sbtest4#P#p5 | Single     | test/sbtest4#P#p5 |
+-------------------+------------+-------------------+
5 rows in set (0.00 sec)

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

mysql> SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as space_name  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES A  LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES B  ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%sbtest4%' ORDER BY A.NAME;
+-------------------+------------+------------+
| partition_name    | space_type | space_name |
+-------------------+------------+------------+
| test/sbtest4#P#p1 | General    | t1         |
| test/sbtest4#P#p2 | General    | t1         |
| test/sbtest4#P#p3 | General    | t1         |
| test/sbtest4#P#p4 | General    | t1         |
| test/sbtest4#P#p5 | General    | t1         |
+-------------------+------------+------------+
5 rows in set (0.00 sec)

@Daniel thanks for final clarification but, did you notice the difference between alters?

Mine:

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

Yours:

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

Did you use an empty table there? :) If yes, then it is OK.