Bug #73208 | Can't convert to BLACKHOLE if tablespace has been discarded | ||
---|---|---|---|
Submitted: | 5 Jul 2014 18:14 | Modified: | 24 Jan 2015 13:14 |
Reporter: | Federico Razzoli | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
Version: | 5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | Tablespace |
[5 Jul 2014 18:14]
Federico Razzoli
[7 Jul 2014 5:45]
MySQL Verification Team
I do see your point from a usability perspective. But, what you're asking for is that InnoDB gives special treatment to blackhole engine. IMHO, one engine shouldn't make any assumptions about another engine's capabilities, except what is provided by the SE API.. The documentation does say: "This deletes the current .ibd file, so be sure that you have a backup first. Attempting to modify the table contents while the tablespace file is discarded results in an error." mysql> create table t1(a serial)engine=innodb; Query OK, 0 rows affected (0.06 sec) mysql> alter table t1 discard tablespace; Query OK, 0 rows affected (0.11 sec) mysql> alter table t1 engine=blackhole; ERROR 1814 (HY000): Tablespace has been discarded for table 't1' mysql> select * from t1; ERROR 1814 (HY000): Tablespace has been discarded for table 't1' mysql> truncate t1; ERROR 1814 (HY000): Tablespace has been discarded for table 't1' mysql> insert into t1 values (1); ERROR 1814 (HY000): Tablespace has been discarded for table 't1' mysql> alter table t1 engine=innodb; Query OK, 0 rows affected, 1 warning (0.10 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1814 | InnoDB: Tablespace has been discarded for table 't1' | +---------+------+------------------------------------------------------+ 1 row in set (0.01 sec) I'm unsure if the Feature request here makes sense to implement?
[8 Jul 2014 10:44]
MySQL Verification Team
After discarding the tablespace, the data is gone. All that remains is the structure, which can resurrect to a new table: SHOW CREATE TABLE t1; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ENGINE=BLACKHOLE; So, we can make a FR out of this;
[24 Jan 2015 13:05]
Federico Razzoli
Note that this is a real worlds case, even if it may seem weird. I wanted to backup data before converting the table to BLACKHOLE, and be sure that no insertion could occur in the meanwhile.
[24 Jan 2015 13:14]
Federico Razzoli
Sorry, incomplete explanation, it could look even more weird. The reason is that the table had a trigger, and I didn't want it to be fired after I took the backup and before erasing all data.