Safer Staging Environments with Blackhole Storage
Published on 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:— Chris Fidao (@fideloper) January 20, 2022
🧐 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
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 sessionSET 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 datatruncate offending_worrisome_table; -- Set the table's new storage engineALTER TABLE offending_worrisome_table ENGINE = BLACKHOLE;
And that's it! The table will exist, be queryable, but will never retain data!