Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result.

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam. but i had tried that also but unable to get the result. Can any one pl help me on this issue to fix it.

mysql> CREATE TABLE test1(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test1;
Empty set (0.00 sec)

mysql>  CREATE TABLE test_recovery as select * from test1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_recovery ENGINE = innodb;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM test_recovery;
Empty set (0.00 sec)
share|improve this question

closed as off-topic by billinkc, Marian, RLF, Mark Storey-Smith, Max Vernon Aug 8 at 18:27

If this question can be reworded to fit the rules in the help center, please edit the question.

4  
Nothing can come out of a black hole, not even light. That's why they were named this way. –  ypercube Aug 8 at 12:17
2  
@ypercube What about Hawking radiation? And what about point in time recovery with bin logs? –  jynus Aug 8 at 12:20
4  
What about the MySQLWormhole engine? That way you could travel back in time and retrieve your data before it enters the MySQLBlackhole! –  Vérace Aug 8 at 12:25
6  
Are you running on Unix? The data might be in /dev/null –  Phil Aug 8 at 14:19
5  
This question appears to be off-topic because it is about the impossible. As user and the documentation states "The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result" –  billinkc Aug 8 at 15:00

1 Answer 1

You said

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam

Whoever told you that should have told you to enable binary logs as @jynus commented.

The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups.

EXAMPLE #1 : Star Topology

Some have Star Topologies to write data to a MySQL Replication Master. The Master has nothing but BLACKHOLE tables. All Slaves have InnoDB or MyISAM. That way, if the Master gets hacked, there is no local data. Such a Master is known as a Distribution Master.

EXAMPLE #2 : Single Slave, Multiple Masters

This involves setting up Circular Replication with three servers. Two Masters with a mix of BLACKHOLE tables and a Slave with all real tables. This technique predates multisource replication.

EXAMPLE #3 : Increase Write Performance

I knew of a Web Hosting Client who went to Percona for performance issues on one table. Since the client had MySQL Replication with Multiple Slaves, Percona converted the problem table to BLACKHOLE. This increased write performance on the Master. The data had to be read from the Slaves.

I once suggested this in a post for recording audit information (Performance of a Trigger vs Stored Procedure in MySQL)

EPILOGUE

While some of the comments remind me of StarTrek DS9, the BLACKHOLE Storage Engine is what its name says: NO DATA IS EVER STORED !!! If you had binary logs enabled, that would have been your only chance of having data.

share|improve this answer
6  
since when were facts and technicalities involved in this question? :) –  Phil Aug 8 at 15:50
2  
Isn't it great that we can be techies (or even, perhaps, nerds), without being propeller headed dweebs! +1 –  Vérace Aug 8 at 16:10
2  
Of course, if binlogging is enabled... the data is actually all there if the logs haven't been purged, and there is a possibility of recovering it by replaying them from just the right point on another system. –  Michael - sqlbot Aug 9 at 20:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.