d

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore.

15 St Margarets, NY 10033
(+381) 11 123 4567
ouroffice@aware.com

 

KMF

Safer Staging Environments with Blackhole Storage

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:

1SHOW VARIABLES LIKE 'sql_mode';

2-- Result: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,

3-- NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,

4-- NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

5 

6-- We want to remove modes related to NO_ZERO_DATE

7-- But just for this session

8SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

9 

10-- Ensure the table has no data

11truncate offending_worrisome_table;

12 

13-- Set the table's new storage engine

14ALTER TABLE offending_worrisome_table ENGINE = BLACKHOLE;

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


Credit: Source link

Previous Next
Close
Test Caption
Test Description goes like this