Safer Staging Environments with Blackhole Storage

Published on January 25th, 2022 by Chris Fidao

I recently made a mistake where a staging environment emailed out a few thousand people.

It's a common-enough story.

There are many ways to safeguard against this, but this "trick" is one that I don't commonly see used.

In our case, we have a database table that has a list of people to email, which email to send them, and when to send it. A CRON task checks this table periodically.

👨‍🔬 Cool MySQL hack:

🧐 If your staging env has a table that ideally doesn't have data in it (esp production data)

🕳 You can make it a BLACKHOLE table

😅 I used this to make sure a table with a list of emails to send never contains any rows. Because, you know.... pic.twitter.com/kGh3wHnLzJ
— Chris Fidao (@fideloper) January 20, 2022

The BLACKHOLE storage engine

One way to safeguard against this, in my case, would be if that table never had data in it.

In MySQL, we're all mostly using the InnoDB storage engine (or MyISAM for older database, or for specific use cases). MySQL has other storage engines, and one of those is the BLACKHOLE engine.

As MySQL describes it:

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:

This was perfect - everything in our application can work normally in staging, but this table will just never have data within it.

Altering a MySQL table

The only step I had to take was altering a table.

I did run into one error, where MySQL did not like that I had a default value of 0000-00-00 00:00:00 for a timestamp field. To get around that, I had to adjust the sql_mode for my database connection's session.

Here's what SQL commands I ran:

SHOW VARIABLES LIKE 'sql_mode';
-- Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
--         NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
--         NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
-- We want to remove modes related to NO_ZERO_DATE
-- But just for this session
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
-- Ensure the table has no data
truncate offending_worrisome_table;
 
-- Set the table's new storage engine
ALTER TABLE offending_worrisome_table ENGINE = BLACKHOLE;