Bug #84360 İt is possible partitoned table to alter to non-existing general tablespace
Submitted: 28 Dec 2016 13:36 Modified: 28 Dec 2016 15:04
Reporter: Yusif Yusifov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.7.17 OS:Ubuntu (16.04)
Assigned to: CPU Architecture:Any
Tags: General Tablespace, innodb, Partitons table

[28 Dec 2016 13:36] Yusif Yusifov
Description:
Hi Dear All.

After creating partitions table, if you try to move table to non existing general tablespace it will work like a charm. But with regular tables it will give error like below. 
ERROR 1812 (HY000): InnoDB: A general tablespace named `innod` cannot be found.

How to repeat:
Partitons table

mysql> create table test_tab5(a int,b int) engine = innodb
    -> partition by range (a)
    -> (PARTITION p1 VALUES LESS THAN (100),
    -> PARTITION p2 VALUES LESS THAN (1000),
    -> PARTITION p3 VALUES LESS THAN (10000),
    -> PARTITION p4 VALUES LESS THAN (10001)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> alter table test_tab5 tablespace=missing;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_tab5;
+-----------+-----------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------+-----------------------------+
| test_tab5 | CREATE TABLE `test_tab5` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `missing` */ ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (a)
(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) */ |
+-----------+---------------------------------------------+
1 row in set (0.00 sec)

With regular table 

mysql> create table test_regular(id int(11),test_text varchar(100)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test_regular;
+--------------+-----------------------+
| Table        | Create Table                                                                                                                            |
+--------------+-----------------------+
| test_regular | CREATE TABLE `test_regular` (
  `id` int(11) DEFAULT NULL,
  `test_text` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-----------------------+
1 row in set (0.01 sec)

mysql> alter table test_regular tablespace=missing;
ERROR 1812 (HY000): InnoDB: A general tablespace named `missing` cannot be found.

Suggested fix:
No Idea
[28 Dec 2016 15:04] Bogdan Kecman
Hi,

In case of non-partitioned table you can't move to non existing table space so you get error as you expect.

On the other case, where you have partitioned table, this alter is just setting a default value for the next partitions to be created, the default value can be anything hence no error (would be cool to get a warning there but..). if you then try to create a partition that will fail with error as tablespace does not exist.

In any case setting this to verified as it is a potential disaster.