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:
None 
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
Description:
If I

1) ALTER TABLE x DISCARD TABLESPACE
2) ALTER TABLE x ENGINE = BLACKHOLE

I get a 1814 error. But if the engine I'm trying to use is BLACKHOLE, this doesn't make sense. Since converting to BLACKHOLE kills all data, so who cares if the tablespace was discarded?

How to repeat:
.
[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.