Description:
General Tablespace names must be unique :)
mysql> select * from information_schema.innodb_sys_tablespaces where name like 'sbtest2';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 29 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
| 70 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
| 75 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.00 sec)
Here is simple test case:
mysql> show create table dbtest.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=2123911 DEFAULT CHARSET=latin1 COMPRESSION='zlib'
1 row in set (0.01 sec)
Create general tablespace:
create tablespace sbtest2 add datafile 'sbtest2.ibd' engine=innodb;
Try to alter table's tablespace(kill process while doing):
mysql> alter table dbtest.sbtest1 tablespace=sbtest2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
You will not be able to drop tablespace anymore:
mysql> drop tablespace sbtest2;
ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest2
Then manually remove sbtest2 from /var/lib/mysql + Restart Server and try to create General Tablespace Again:
mysql> create tablespace sbtest2 add datafile 'sbtest2.ibd' engine=innodb;
Query OK, 0 rows affected (0.07 sec)
The interesting thing here is if size of 'sbtest2' is increased it will show that all 3 same tablespace sizes increased, so they are equal sized:
mysql> select * from information_schema.innodb_sys_tablespaces where name like 'sbtest2';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 29 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 |
| 70 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 |
| 75 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.00 sec)
How to repeat:
See description
Suggested fix:
Seems to be related to some previous "Tablespace" issues. But with different idea.
Description: General Tablespace names must be unique :) mysql> select * from information_schema.innodb_sys_tablespaces where name like 'sbtest2'; +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | 29 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 | | 70 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 | | 75 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 | +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ 3 rows in set (0.00 sec) Here is simple test case: mysql> show create table dbtest.sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=2123911 DEFAULT CHARSET=latin1 COMPRESSION='zlib' 1 row in set (0.01 sec) Create general tablespace: create tablespace sbtest2 add datafile 'sbtest2.ibd' engine=innodb; Try to alter table's tablespace(kill process while doing): mysql> alter table dbtest.sbtest1 tablespace=sbtest2; ERROR 2013 (HY000): Lost connection to MySQL server during query You will not be able to drop tablespace anymore: mysql> drop tablespace sbtest2; ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest2 Then manually remove sbtest2 from /var/lib/mysql + Restart Server and try to create General Tablespace Again: mysql> create tablespace sbtest2 add datafile 'sbtest2.ibd' engine=innodb; Query OK, 0 rows affected (0.07 sec) The interesting thing here is if size of 'sbtest2' is increased it will show that all 3 same tablespace sizes increased, so they are equal sized: mysql> select * from information_schema.innodb_sys_tablespaces where name like 'sbtest2'; +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | 29 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 | | 70 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 | | 75 | sbtest2 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 591396864 | 591400960 | +-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ 3 rows in set (0.00 sec) How to repeat: See description Suggested fix: Seems to be related to some previous "Tablespace" issues. But with different idea.