MariaDB: How to create a Black Hole when BLACKHOLE is disabled

Versione italiana

The BLACKHOLE Storage Engine causes data to fall into a black hole. Not only the data you are INSERTing or 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 VIEW!

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 WHERE FALSE).
* 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.

The 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.

Enjoy!

Advertisements

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s