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)
Karthick
  • 1,197
  • 9
  • 25
  • 4
    Nothing can come out of a black hole, not even light. That's why they were named this way. – ypercubeᵀᴹ Aug 08 '14 at 12:17
  • @ypercube there is no way to get data's of that?? – Karthick Aug 08 '14 at 12:20
  • 2
    @ypercube What about Hawking radiation? And what about point in time recovery with bin logs? – jynus Aug 08 '14 at 12:20
  • 1
    @jynus Hawking radiation is a tiny amount of energy compared to what comes into the hole. As for point in time recovery, that's like reconstructing the universe, still nothing is taken from the back hole itself ;) – ypercubeᵀᴹ Aug 08 '14 at 12:23
  • 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 08 '14 at 12:25
  • @Vérace i had tried by altering engine to wormhole but unable to get data.. – Karthick Aug 08 '14 at 12:45
  • 6
    Are you running on Unix? The data might be in /dev/null – Philᵀᴹ Aug 08 '14 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 08 '14 at 15:00
  • 2
    Boring! Facts. Worse still, backed up with credible references to actual documentation (very unsporting behaviour). This thread has clearly degenerated into complete silliness - perfect for a Friday afternoon! :-) – Vérace Aug 08 '14 at 15:44
  • 1
    A second point - as Napoléon clearly stated in 1808, "Impossible n'est pas français". So, if it's not impossible for the inventor of the metric system and most of the world's legal frameworks, it clearly can't be impossible for MySQL. QED, or as the French would say, CQFD! – Vérace Aug 08 '14 at 15:54
  • 2
    @Vérace Yes, but the little corporal also said "I think I'll invade Russia" – billinkc Aug 08 '14 at 18:31
  • Perhaps the title should really be Is it Possible to Recover Data from a BLACKHOLE table ? or Why Can Data Not Be Recovered from a BLACKHOLE table ? or put If the Data is Gone, Why Can Data Not Be Recovered from a BLACKHOLE table ? into the Question. – RolandoMySQLDBA Aug 08 '14 at 19:04

1 Answers1

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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 6
    since when were facts and technicalities involved in this question? :) – Philᵀᴹ Aug 08 '14 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 08 '14 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 09 '14 at 20:11