I used to think it was impossible to recover data from the MySQL BLACKHOLE storage engine. As stated in the manual:

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’ve always trusted the manual on this, but a recently published article about information and black holes proposes that the information may be recoverable. As stated in the abstract:

…probability conservation is guaranteed because black holes stimulate the emission of radiation in response to infalling matter. This stimulated radiation is non-thermal and contains all of the information about the infalling matter…

The nearest black hole is about 1600 light years away from earth, so I decided to apply this research closer to home by creating a new storage engine to capture the data that I insert into my BLACKHOLE table using stimulated radiation. I should be ready to open source this project soon, but for now here’s a quick demonstration.

First, here’s the classical example of data loss with the BLACKHOLE storage engine:

1
2
3
4
5
6
7
8
9

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

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

mysql> SELECT * FROM test;
Empty set (0.00 sec)

Now I will use my new storage engine (I named it “Adami” after the author of the research paper) to recover the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

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

mysql> SELECT * FROM test_recovery;
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)

I recovered the data! If you’ve ever lost important data in the BLACKHOLE storage engine, this could be the solution you’ve been looking for.