BLACKHOLE Storage Engine causes data to fall into a black hole. Not only the data you are
LOADing, but also existing data. In other words,
BLACKHOLE disables a table and erases its contents. It can be useful for debugging (don’t execute query, but verify if they cause an error) or other special purposes.
Thought, hosting providers generally have
BLACKHOLE disabled. I’m not sure about how that simple Storage Engine can damage their service, but it’s usually you can’t use it, it’s a fact.
But then, how can you emulate a
BLACKHOLE table? The answer is simple: just use a
First, we need a table. The process is trivial and riskless, so I will not write a
CREATE TABLE. Use one of your existing tables. The following example assumes that your table is called
`t_user` – replace that name with the real one.
Then, we create a view. It is of the simplest type: it just mirrors an existing table. You can read from it and write in it. Here’s the code:
CREATE OR REPLACE VIEW `t_user` AS SELECT * FROM `user`;
You will use
`t_user` instead of
`user` for all your operations. At some point, you want to disable that view – ie, turn it into a Black Hole!
The process, as I said, is trivial. Just re-define
`user`, but with 2 changes:
* Add a
WHERE FALSE clause. Obviously the view will not show any data (because no row can possibly satisfy a
* Add a
WITH CHECK OPTION clause. It will make the view not-writable (because a row can only be
INSERTed if it satisfies
WHERE FALSE, which is impossible).
Here’s the code:
CREATE OR REPLACE VIEW `t_user` AS SELECT * FROM `user` WHERE FALSE WITH CHECK OPTION;
When you are ready to turn the Black Hole into a habitable planet, execute the first statement again.
OR REPLACE clause is useful in this case, because you don’t need to
DROP an re-
CREATE the view – you replace it with a single atomic operation.