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
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