8

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)
12
  • 4
    Nothing can come out of a black hole, not even light. That's why they were named this way. Commented Aug 8, 2014 at 12:17
  • 2
    @ypercube What about Hawking radiation? And what about point in time recovery with bin logs?
    – jynus
    Commented Aug 8, 2014 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
    Commented Aug 8, 2014 at 12:25
  • 6
    Are you running on Unix? The data might be in /dev/null
    – Philᵀᴹ
    Commented Aug 8, 2014 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
    Commented Aug 8, 2014 at 15:00

1 Answer 1

13

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.

3
  • 6
    since when were facts and technicalities involved in this question? :)
    – Philᵀᴹ
    Commented Aug 8, 2014 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
    Commented Aug 8, 2014 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. Commented Aug 9, 2014 at 20:11

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