How to lose General Tablespace in MySQL 5.7.10

By | January 29, 2016

If you have not be careful with General Tablespaces in MySQL 5.7.10, you must be at least from now.
I have reported several bugs related to General Tablespaces for eg.:

* #77814 -> Altering table twice,with same tablespace will increase general tablespace size (verfied)

* #77556 -> Segmentation fault while creating general tablespace in disk full Linux (fixed/closed)

* #77830 -> “ALGORITHM=INPLACE is not supported” no error with tablespace alter (open)

* #80070 -> allocated_size and file_size differ if create general tablespace outside datadir (verified)

* #80181 -> Abandoned .ibd files after each crash recovery,killing while altering tablespace (verified)

* #80182 -> Unsupported extension error for new features while ‘create table like’ (verified)

But the most critical one is to lose tablespace after crash recovery:

* ##80183 -> ALTER TABLESPACE is not crash resistant (verified)

Reproduce steps are:
Create General Tablespace:

mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:

mysql> alter table sbtest1 tablespace ts1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Then start and look at table if it was altered:

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` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

No it is in innodb_file_per_table as usual.
But:

mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

From now you can’t drop this tablespace.
If you have encountered any other scenario report it 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *