Safer Staging Environments with Blackhole Storage

Published on by

Safer Staging Environments with Blackhole Storage image

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.

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;

And that's it! The table will exist, be queryable, but will never retain data!

Chris Fidao photo

Teaching coding and servers at CloudCasts and Servers for Hackers. Co-founder of Chipper CI.

Cube

Laravel Newsletter

Join 40k+ other developers and never miss out on new tips, tutorials, and more.

image
No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project.

Visit No Compromises
Laravel Forge logo

Laravel Forge

Easily create and manage your servers and deploy your Laravel applications in seconds.

Laravel Forge
Tinkerwell logo

Tinkerwell

The must-have code runner for Laravel developers. Tinker with AI, autocompletion and instant feedback on local and production environments.

Tinkerwell
No Compromises logo

No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project. ⬧ Flat rate of $7500/mo. ⬧ No lengthy sales process. ⬧ No contracts. ⬧ 100% money back guarantee.

No Compromises
Kirschbaum logo

Kirschbaum

Providing innovation and stability to ensure your web application succeeds.

Kirschbaum
Shift logo

Shift

Running an old Laravel version? Instant, automated Laravel upgrades and code modernization to keep your applications fresh.

Shift
Bacancy logo

Bacancy

Supercharge your project with a seasoned Laravel developer with 4-6 years of experience for just $2500/month. Get 160 hours of dedicated expertise & a risk-free 15-day trial. Schedule a call now!

Bacancy
LoadForge logo

LoadForge

Easy, affordable load testing and stress tests for websites, APIs and databases.

LoadForge
Paragraph logo

Paragraph

Manage your Laravel app as if it was a CMS – edit any text on any page or in any email without touching Blade or language files.

Paragraph
Lucky Media logo

Lucky Media

Bespoke software solutions built for your business. We ♥ Laravel

Lucky Media
Lunar: Laravel E-Commerce logo

Lunar: Laravel E-Commerce

E-Commerce for Laravel. An open-source package that brings the power of modern headless e-commerce functionality to Laravel.

Lunar: Laravel E-Commerce
DocuWriter.ai logo

DocuWriter.ai

Save hours of manually writing Code Documentation, Comments & DocBlocks, Test suites and Refactoring.

DocuWriter.ai
Rector logo

Rector

Your partner for seamless Laravel upgrades, cutting costs, and accelerating innovation for successful companies

Rector

The latest

View all →
Generate Code Coverage in Laravel With PCOV image

Generate Code Coverage in Laravel With PCOV

Read article
Non-backed Enums in Database Queries and a withSchedule() bootstrap method in Laravel 11.1 image

Non-backed Enums in Database Queries and a withSchedule() bootstrap method in Laravel 11.1

Read article
Laravel Pint --bail Flag image

Laravel Pint --bail Flag

Read article
Laravel Herd for Windows is now released! image

Laravel Herd for Windows is now released!

Read article
The Laravel Worldwide Meetup is Today image

The Laravel Worldwide Meetup is Today

Read article
Cache Routes with Cloudflare in Laravel image

Cache Routes with Cloudflare in Laravel

Read article