News for hackers. Stuff that matters.
Re:An administrator accidentally deleted the production data... by odinduty on Wednesday March 23, 2016 @06:53AM (#11343331)
Well, who hasn't done a DELETE without a WHERE clause? ;P
Re:An administrator accidentally deleted the production data... by enobrev on Wednesday March 23, 2016 @08:19AM (#11343734)
I'm a fan of the "Write as a SELECT first" method for any DELETE or UPDATE queries, but in my busier no-sleep days, I was very fond of mysql's --i-am-a-dummy switch[1] on the CLI client, which will "Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values."

Although, these days, I generally avoid mucking around with production DBs directly. It's all manually scripted migrations, and testing said scripts on a backup or unused slave, which is by far the safest, and also helps to avoid running queries that might adversely affect production's performance.

1: http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options...

Re:An administrator accidentally deleted the production data... by traviscj on Wednesday March 23, 2016 @07:19AM (#11343450)
I have a very strict habit after once or twice doing this: always start all DELETE commands with "--" (that is, comment it out) until I have written a where-clause.

My command in the buffer might go through these steps:

1. "Delete from" 2. "-- delete from" 3. "-- delete from table where condition limit n;" 4. (Generally either ask a co-worker or make a Jira with the exact command I have at this point, so there's a sanity check and/or permanent record, but for very low risk/especially mundane/especially time critical updates, do it) 5. Delete the "--" and run it. 6. Think hard about adding some functionality in the app for doing it in app code unsteady of in database code.

Generally do the same for "UPDATE".

Re:An administrator accidentally deleted the production data... by leftnode on Wednesday March 23, 2016 @07:24AM (#11343470)
You can also start all of your commands with "BEGIN;" (at least in Postgres) to ensure you're in a transaction you can easily rollback.

Another trick it to start all "DELETE" commands as a "SELECT" to ensure you get the "WHERE" part correct, and then swap out the "SELECT *" with a "DELETE".

Re:An administrator accidentally deleted the production data... by oneeyedpigeon on Wednesday March 23, 2016 @07:50AM (#11343583)
I use the latter almost religiously. Also can be useful to add a LIMIT on the end, just to guard against an UPDATE running amok. And I tend to write my queries 'backwards' (e.g. starting with the WHERE clause, ending with the "DELETE", to guard against executing the query prematurely.
Re:An administrator accidentally deleted the production data... by spacecowboy_lon on Wednesday March 23, 2016 @08:08AM (#11343668)
I tend to write my DELETE statements by writing a SELECT first then edit that into the delete version
Re:An administrator accidentally deleted the production data... by cwilkes on Wednesday March 23, 2016 @10:19AM (#11344664)
I've turned to creating a temp table and putting the primary keys from the select statement into there so I can guard myself against the thing that was to delete a handful of rows deleting everything. Plus with that you can do another join and see if those rows have some sort of value in it that you didn't expect.
Re:An administrator accidentally deleted the production data... by sixothree on Wednesday March 23, 2016 @05:03PM (#11348216)
I also put my DELETE statement on a line by itself and comment out the line. In SQL Server execution processes only the selected text. So I select starting with DELETE then run.
Re:An administrator accidentally deleted the production data... by danieltillett on Wednesday March 23, 2016 @08:39AM (#11343856)
This is exactly what I do too.
Re:An administrator accidentally deleted the production data... by hobs on Wednesday March 23, 2016 @08:02AM (#11343634)
I dont use mysql, but it actually has a command line flag to help with this problem that always made me laugh, --i-am-a-dummy

This is an alternate form of the flag --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided.

Re:An administrator accidentally deleted the production data... by rlonstein on Thursday March 24, 2016 @11:27AM (#11353548)
Several years ago a colleague wrote a handy command line tool that was potentially destructive if misused, so naturally the outsourced operations team misused it. In response he added a flag along the lines of “—-yes-i-really-do-know-what-im-doing” that was undocumented and mandatory for the destructive operations.
Re:An administrator accidentally deleted the production data... by was_boring on Wednesday March 23, 2016 @08:12AM (#11343693)
My personal preference is to either have a "deleted_at" column so deletes never need to happen, or have a "history" table.

When this isn't possible, I do a select for the PK and delete based upon only the PK. This way I can review the rows to be deleted and back them up manually if needed.

All of this is way overkill considering the backups available these days.

Re:An administrator accidentally deleted the production data... by MichaelBurge on Wednesday March 23, 2016 @07:18AM (#11343449)
I've gotten into the habit of enabling transactions by default in my .psqlrc. And before I do any maintenance, a good:

abort;

begin;

Just to be doubly sure. Then notice the number of deleted rows, and select from the table afterwards.

Re:An administrator accidentally deleted the production data... by traviscj on Wednesday March 23, 2016 @07:24AM (#11343469)
I am a bit less convinced by this practice. The general consensus seems to be: "oh you can SELECT around after you've done your update and before you commit; the update." But while you're doing that, you might be holding row locks on all the data your update touched, and meanwhile the lock acquisitions in your app are failing, along with whatever that entails. And if you're not doing that and immediately commit;ing, are you gaining anything?
Re:An administrator accidentally deleted the production data... by jsnell on Wednesday March 23, 2016 @07:38AM (#11343525)
When the UPDATE or DELETE finishes it will output the number of rows changed. If that number is much larger than expected, something probably went wrong. So there's a gain even if you don't investigate the results in detail.
Re:An administrator accidentally deleted the production data... by bottled_poe on Wednesday March 23, 2016 @08:02AM (#11343636)
I agree with parent that using transactions is good practice. In general, naked SQL on a production DB is a bad idea - it is not different to running untested coded in a production environment. Why risk it?

Transactions are a sane safeguard if you absolutely must run SQL on your production database.

Re:An administrator accidentally deleted the production data... by MichaelBurge on Wednesday March 23, 2016 @08:05AM (#11343651)
[REMOVED]
Re:An administrator accidentally deleted the production data... by brianwawok on Wednesday March 23, 2016 @08:19AM (#11343726)
This is a lot of manual steps. Why not have migration scripts in your code base. Run them in dev and qa and staging so you know they work. Then you go to prod and are done.

The less manual steps the less mistakes will happen.

Re:An administrator accidentally deleted the production data... by blakeyrat on Wednesday March 23, 2016 @07:20PM (#11349165)
I had a great mentor who taught me all the safety mechanisms.

Start your .sql file with "use xxxxx" (non-existent database name; will prevent execution if you fat-finger F5.) Always write your deletes as a SELECT first:

SELECT *

-- delete

FROM blah

WHERE blah = 1

Never uncomment the "delete" line; just select the portion of the query after the comment with your mouse and F5 it.

And of course: always design your database with support for soft-deletes, because sooner or later you'll need to add them in.

Re:An administrator accidentally deleted the production data... by CaptainZapp on Wednesday March 23, 2016 @08:06AM (#11343656)
Simple solution:

  BEGIN TRAN
  DELETE (without where clause)
  UUPS!
  ROLLBACK 
That said it would be not quite honest that there wasn't an incident in my past, which enforces this policy. Always! Without fail!
Re:An administrator accidentally deleted the production data... by rlidwka on Wednesday March 23, 2016 @02:00PM (#11346741)
Better solution is:

    BEGIN TRAN; DELETE (without where clause)
... on the same line.

I've lost a lot of data to "oh lets find that line in mysql history... up, up, enter, FUCK"

Re:An administrator accidentally deleted the production data... by mightybyte on Wednesday March 23, 2016 @07:25AM (#11343473)
Yep, I've never done it, but I've definitely been aware of how easy it can happen. When I'm in this kind of situation on a production DB without easily restorable backups I usually try to remember to put all my delete/update queries in a transaction. Then commit after I see the appropriate number of rows have been effected.
Re:An administrator accidentally deleted the production data... by markatkinson on Wednesday March 23, 2016 @07:08AM (#11343403)
Oh man oh man.... This hurts just reading that, or UPDATE without WHERE.
Re:An administrator accidentally deleted the production data... by odinduty on Wednesday March 23, 2016 @07:41AM (#11343542)
I have a story about that based on real facts(TM): I once had to reset my own password on a production database and I decided to hash it by hand and UPDATE my row in the users table.

A few hours later we had got a few calls from angry customers who couldn't log in. I had effectively forgotten the WHERE clause so all users had the same password: mine.

Extra points for not having read the "xxx rows updated" line that the mysql console outputs after each query...

Re:An administrator accidentally deleted the production data... by Gigablah on Wednesday March 23, 2016 @08:53AM (#11343933)
Technically they didn't have the same password, unless you're saying that your passwords aren't salted ;)
Re:An administrator accidentally deleted the production data... by odinduty on Wednesday March 23, 2016 @09:07AM (#11344021)
I updated the hash and the salt in the same query. They weren't salted against the user id or anything like that, just a second column for the salt, which is... common practice.
Re:An administrator accidentally deleted the production data... by jsjohnst on Wednesday March 23, 2016 @09:16AM (#11344085)
where do you get that conclusion? There's a lot of ways the password could be literally the exact same string, yet still be salted and even peppered.
Re:An administrator accidentally deleted the production data... by mpnordland on Wednesday March 23, 2016 @11:57AM (#11345577)
I like adding garlic to my passwords, gives them a kick!
Re:An administrator accidentally deleted the production data... by jsjohnst on Wednesday March 23, 2016 @12:01PM (#11345632)
Your response could be taken as a joke (made me laugh anyway!), but also seriously too. If it was serious, what do you use as garlic and why?
Re:An administrator accidentally deleted the production data... by mpnordland on Friday March 25, 2016 @10:27PM (#11364179)
It was a joke, I'm not sure what garlic would be added to a password.