Hacker News Reader

Comment by michh | Hacker News Reader

Classic forgetting the full WHERE-part of a manual UPDATE-query on a production system. The worst part is you know you fucked up the nanosecond you hit enter, but it's already too late. Lesson learned? Avoid doing things manually even if a non-technical co-worker insists something needs to be changed right away. And if you do: wrap it in a transaction so you can rollback, leave in a syntax error that you'll only remove when you're done typing the query.

View on HN

Exactly the same...

I was hired by my college to build a grade management system in my second-to-last year there. I was in a hurry due to a lunch meeting with other IT staff at the University, forgot to add the where clause, and suddenly every single student was a Computational-Science major (mine).

Funny part of the story was that the moment it happened I uttered "oh shit." My boss, who sat beside me, said "what'd you do?", and about 15 IT staff from other departments walked into the office to go out for lunch. I'm sure I was an interesting shade of red.

I had to explain what I did in front of all these people. My boss laughed out loud, brought the system offline, and simply said: "well, after lunch we get to test our backup process." We went for lunch.

Two valuable lessons I learned...

People make mistakes, that isn't a problem, it's how they respond that's important.

Don't try and solve hard problems when emotions are running high. If shit is going down in production, the most important thing to do is to breathe, and get a glass of water. That little bit of time helps a lot.

This is why, while I hate Oracle and everything they represent as a company, I kind of like their database because of the flashback feature. You can do

  SELECT * FROM table AS OF TIMESTAMP some_timestamp;

and that is pretty practical. It works online, no restore, no nothing, and while it only works as long as the old data are in logs, on a production system, you should have the spare space to have some history. Theres also FLASHBACK TABLE tab to BEFORE DROP but that shouldn't happen, right?

Of course, you should probably do every update of production data in a transaction, check the result and then commit, and if you want to be sure, you can do UPDATE ... RETURNING to check what's changing. Autocommit on manual access to production is pretty crazy. But still, flashback is useful.

I usually allocate a large part of the free space to FRA. In the production system I use right now (about 2TB of data, 50GB changes/day), I can go back a couple of weeks if needed. Fortunately everything is stable now, but that flashback was quite useful a few times.

Reading all of these makes me think, the admin tool for your database of choice should probably put you inside a transaction by default, and require you to explicitly commit changes. For the madmen, it could still have an auto-commit mode, but should be opt-in rather than the default.

I've done similar and now I almost always write a select first and then only after I've verified I'm getting the rows that I expect do I update my query to an update/delete.

In this case though, wouldn't you have to COMMIT before the actual update happens ? Usually in production, it is not a good idea to have auto COMMIT on.

I did something similar once. Now my query writing goes:

> UPDATE

> UPDATE SET url='new_url' WHERE source_id IN (etc)

> UPDATE sources SET url='new_url' WHERE source_id IN (etc)

I always add a LIMIT even when not necessary.

Why doesn't MySQL have a version control baked in? Even if it preserves just the last n hours of state..

That's what backups are for. With appropriate checkpoints, you can roll back to any point in time with a reasonable level of precision.

It kind of does. It's called the binary log.

Didn't know about this! Thanks.

https://dev.mysql.com/doc/refman/5.0/en/binary-log.html

https://dev.mysql.com/doc/refman/5.0/en/point-in-time-recove...

Since you're using MySQL, you can start it with --i-am-a-dummy [1]. Not a backup, but still handy.

[1] http://dev.mysql.com/doc/refman/5.6/en/mysql-tips.html#safe-...

Been there done that. Usually I always work inside a transaction, and carefully examine the results before typing that all important 'commit'. But a "simple" change at 4:55 and me in a hurry to get home....

This is why you have SET SQL_SAFE_UPDATES=1; (or equivalent) in your DB shell startup. It only takes one UPDATE users SET password='foo'; to learn why...

Ah, memories.

I did this in a production database (thought it was a QA environment) and brought trading on the mortgage desk of an investment bank to a grinding halt on September 14th, 2008.

The DBAs saved my 23 year old ass that day. I make it a point to send them beer on 9/14 every year.

I did this once and have since always typed the WHERE first in an UPDATE.

UPDATE ... UPDATE WHERE ... [go back, go back] UPDATE SET ... WHERE ...

Yep, I typically do SUPDATE just for fun that way. And only do that AFTER building the where clause with a SELECT * FROM foo WHERE ... so that I always start with the clause when making the update. Might be paranoid but it always seems to work out for me that way.

I wish I didn't know the stomach-plunge feeling of realizing you've done this

Reply on news.ycombinator.com