5 ms·
Well, who hasn't done a DELETE without a WHERE clause? ;P
by odinduty 11y ago
Well, who hasn't done a DELETE without a WHERE clause? ;P
- markatkinson 11y agoOh man oh man.... This hurts just reading that, or UPDATE without WHERE.
- odinduty 11y agoI 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...
- Gigablah 11y agoTechnically they didn't have the same password, unless you're saying that your passwords aren't salted ;)
- odinduty 11y agoI 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.
- jsjohnst 11y agowhere 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.
- mpnordland 11y agoI like adding garlic to my passwords, gives them a kick!
- jsjohnst 11y agoYour 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?
- mpnordland 11y agoIt was a joke, I'm not sure what garlic would be added to a password.
- MichaelBurge 11y agoI'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.
- traviscj 11y agoI 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?
- jsnell 11y agoWhen 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.
- bottled_poe 11y agoI 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.
- MichaelBurge 11y ago[REMOVED]
- brianwawok 11y agoThis 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.
- traviscj 11y agoI 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".
- leftnode 11y agoYou 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".
- oneeyedpigeon 11y agoI 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.
- spacecowboy_lon 11y agoI tend to write my DELETE statements by writing a SELECT first then edit that into the delete version
- danieltillett 11y agoThis is exactly what I do too.
- cwilkes 11y agoI'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.
- mightybyte 11y agoYep, 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.
- hobs 11y agoI 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.
- rlonstein 11y agoSeveral 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.
- CaptainZapp 11y agoSimple 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!
- rlidwka 11y agoBetter 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"
- was_boring 11y agoMy 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.
- enobrev 11y agoI'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.html#option_mysql_safe-updates http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options...
- blakeyrat 11y agoI 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.