5 ms·

This is certainly a monumental fuckup, but these things inevitably happen even with better development practices, this is why you need backups, preferably daily

This is certainly a monumental fuckup, but these things inevitably happen even with better development practices, this is why you need backups, preferably daily, and as much separation of concerns and responsibilities as humanly possible. Anecdote: I am working for a company that does some data analysis for marketers aggregated from a vast number of sources. There was a giant legacy MyISAM (this becomes important later) table with lots of imported data. One day, I made some trivial looking migration (added a flag column to that table). I tested it locally, rolled it out to staging server. Everything seemed A-OK until we started migration on the production server. Suddenly, everything broke. By everything, I mean EVERYTHING, our web application showed massive 500-s, total DEFCON1 across the whole company. It turned out we ran out of disk space, since apparently myisam tables are altered the following way: first the table is created with updated schema, then it is populated with data from the old table. MyISAM ran out of disk space and somehow corrupted the existing tables, mysql server would start with blank tables, with all data lost. I can confirm this very feeling: "The implications of what I'd just done didn't immediately hit me. I first had a truly out-of-body experience, seeming to hover above the darkened room of hackers, each hunched over glowing terminals." Also, I distinctly remember how I shivered and my hands shook. It felt like my body temperature fell by several degrees. Fortunately for me, there was a daily backup routine in place. Still, several hour long outage and lots of apologies to angry clients. "There are two types of people in this world, those who have lost data, and those who are going to lose data"

MBCook 14y ago
Ug. MyISAM. More than once (over the last few years) I've been doing some important update. I tend to do it the same way. START TRANSACTION; --run SQL-- --check results-- COMMIT; or ABORT TRANSACTION; Of course, if you happen to run into the 1 or 2 MyISAM tables that no one knew were MyISAM, abort doesn't do anything. You've screwed up the data and need a backup. So you always have to make a backup and check that the tables are defined they way they should be. Nothing is quite as much fun as the first time you delete a bunch of critical data off a production system. Luckily the table was small, basically static, and we had backups so it was only a problem for ~5 minutes.
- mst 14y ago
  Any CREATE, ALTER or DROP on mysql does an implicit, silent COMMIT (not rollback) before running it. PostgreSQL can do DDL inside a transaction though.
  - MBCook 14y ago
    Yep, that's another one to watch out for. But I'm used to that, it's when an update or delete that can't be reversed I get surprised.
  - deleted 14y ago
    [deleted]
perlgeek 14y ago
Reading those stories makes me realize how well thought-out the process at my work is: We have dev databases (one of which was recently empty, nobody knows why; but that's another matter), then a staging environment, and finally production. And the database in the staging environment runs on a weaker machine than the prod database. So before any schema change goes into production, we do a time measurement in the staging environment to have a rough upper bound for how long it will take, how much disc space it uses etc. And we have a monthly sync from prod to staging, so the staging db isn't much smaller than prod db. And the small team of developers occasionally decides to do a restore of the prod db in the development environment. The downside is that we can't easily keep sensitive production data to find its way into the development environment.
- wpietri 14y ago
  When moving data from prod to other environments, consider a scrambler. E.g., replace all customer names with names generated from census data. I try to keep data having the same form (e.g., length, number of records, similar relationships, looks like production data). But it's random enough so that if the data ever leaks, we don't have to apologize to everybody. Since your handle is perlgeek, you're already well equipped to do a streaming transformation of your SQL dump. :)
  - Domenic_S 14y ago
    Yep. For x.com I wrote a simple cron job that sterilizes the automated database dump and sends it to the dev server. Roughly, it's like this: -cp the dump to a new working copy -sed out cache and tmp tables -Replace all personal user data with placeholders. This part can be tricky, because you have to find everywhere this lives (are form submissions stored and do they have PII?) -Some more sed to deal with actions/triggers that are linked to production's db user specifically. -Finally, scp the sanitized dump to the dev server, where it awaits a Jenkins job to import the new dump. The cron job happens on the production DB server itself overnight (keeping the PII exposure at the same level it is already), so we don't even have to think about it. We've got a working, sanitized database dump ready and waiting every morning, and a fresh prod-like environment built for us when we log on. It's a beautiful thing.
hef19898 14y ago
Remebers me of adifferent story from back the day when I worked around a pretty huge SAP system (as some kind of super user whatever). In one, seemingly trivial update (trivial compared to the complete system upgrade from one version to the higher one that worked without problems) cleansed the database including all purchase orders from the last two days company wide. Ah, and the back-up becamo "unusable", too. But as far as I know, nobody was fired for this. Because, yes, things like this just can happen. An eventually it got fixed anyway.

MBCook 14y ago

Ug. MyISAM. More than once (over the last few years) I've been doing some important update. I tend to do it the same way. START TRANSACTION; --run SQL-- --check results-- COMMIT; or ABORT TRANSACTION; Of course, if you happen to run into the 1 or 2 MyISAM tables that no one knew were MyISAM, abort doesn't do anything. You've screwed up the data and need a backup. So you always have to make a backup and check that the tables are defined they way they should be. Nothing is quite as much fun as the first time you delete a bunch of critical data off a production system. Luckily the table was small, basically static, and we had backups so it was only a problem for ~5 minutes.

mst 14y ago
Any CREATE, ALTER or DROP on mysql does an implicit, silent COMMIT (not rollback) before running it. PostgreSQL can do DDL inside a transaction though.
- MBCook 14y ago
  Yep, that's another one to watch out for. But I'm used to that, it's when an update or delete that can't be reversed I get surprised.
- deleted 14y ago
  [deleted]

perlgeek 14y ago

Reading those stories makes me realize how well thought-out the process at my work is: We have dev databases (one of which was recently empty, nobody knows why; but that's another matter), then a staging environment, and finally production. And the database in the staging environment runs on a weaker machine than the prod database. So before any schema change goes into production, we do a time measurement in the staging environment to have a rough upper bound for how long it will take, how much disc space it uses etc. And we have a monthly sync from prod to staging, so the staging db isn't much smaller than prod db. And the small team of developers occasionally decides to do a restore of the prod db in the development environment. The downside is that we can't easily keep sensitive production data to find its way into the development environment.

wpietri 14y ago
When moving data from prod to other environments, consider a scrambler. E.g., replace all customer names with names generated from census data. I try to keep data having the same form (e.g., length, number of records, similar relationships, looks like production data). But it's random enough so that if the data ever leaks, we don't have to apologize to everybody. Since your handle is perlgeek, you're already well equipped to do a streaming transformation of your SQL dump. :)
- Domenic_S 14y ago
  Yep. For x.com I wrote a simple cron job that sterilizes the automated database dump and sends it to the dev server. Roughly, it's like this: -cp the dump to a new working copy -sed out cache and tmp tables -Replace all personal user data with placeholders. This part can be tricky, because you have to find everywhere this lives (are form submissions stored and do they have PII?) -Some more sed to deal with actions/triggers that are linked to production's db user specifically. -Finally, scp the sanitized dump to the dev server, where it awaits a Jenkins job to import the new dump. The cron job happens on the production DB server itself overnight (keeping the PII exposure at the same level it is already), so we don't even have to think about it. We've got a working, sanitized database dump ready and waiting every morning, and a fresh prod-like environment built for us when we log on. It's a beautiful thing.

hef19898 14y ago

Remebers me of adifferent story from back the day when I worked around a pretty huge SAP system (as some kind of super user whatever). In one, seemingly trivial update (trivial compared to the complete system upgrade from one version to the higher one that worked without problems) cleansed the database including all purchase orders from the last two days company wide. Ah, and the back-up becamo "unusable", too. But as far as I know, nobody was fired for this. Because, yes, things like this just can happen. An eventually it got fixed anyway.