4 ms·

I had a coworker who would always do manual dangerous SQL like these within a transaction ... and would always mentally compare the "rows affected" with what he

I had a coworker who would always do manual dangerous SQL like these within a transaction ... and would always mentally compare the "rows affected" with what he thought it should be before committing. And then commit it. It's a good habit.

mulmen 10y ago
I have never done what the GP describes but I consider myself very lucky as it's a very common mistake. I have heard enough horror stories to always keep that concern in the back of my mind. I do what your coworker did and it's a great feeling when you get the "451789356 rows updated" message inside a transaction where you are trying to change Stacy's last name after her wedding and all you have to do is run a ROLLBACK. Then it's time to go get a coffee and thank your deity of choice.
desas 10y ago
Not doing this is like juggling with knives. I cringe every time I see a colleague doing it.
DrPhish 10y ago
My workflow for modifying production data is: 1) Write a select statement capturing the rows you want to modify and verify them by eyeball 2) (Optional) Modify that statement to select the unchanged rows into a temp table to be deleted in a few days 3) Wrap the statement from step 1 in a transaction 4) Modify the statement into the update or delete 5) Check that rowcounts haven't changed from step 1 6) Copy-and-paste the final statement into your ticketing or dev tracking system 7) Run the final statement It may be overkill, but the amount of grief it can save is immeasurable
rzzzt 10y ago
MySQL's console can also warn you if you issue a potentially destructive statement without a WHERE clause: http://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html#safe-updates http://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html#safe-...
- dotancohen 10y ago
  The `--i-am-a-dummy` flag, which I wish were called `--i-am-prudent` because we all are dummies.
lobster_johnson 10y ago
One of PostgreSQL's best features is transactional DDL: You can run "drop table" etc. in a transaction and roll back afterwards. This has saved me a few times. It also makes it trivial to write atomic migration scripts: Rename a column, drop a table, update all the rows, whatever you want -- it will either all be committed or not committed at all. Surprisingly few databases support this. (Oracle doesn't, last I checked.)
scriptman 10y ago
I do a select first using the where clause I intend to use to get the row count. Then open a transaction around the update with that same where clause, check the total number of rows updated matches the earlier select, then commit. This approach definitely reduces your level of anxiety when working on a critical database.
foobarian 10y ago
It works for more than databases. - With shells, I prefix risky commands on production machines with #, especially when cutting and pasting - Same for committing stuff into VCS, especially when I'm cherrypicking diffs to commit - Before running find with -delete, run with -print first. Many other utilities have dry-run modes
lysp 10y ago
My practise is to do: UPDATE ImportantTable SET ImportantColumn = ImportantColumn WHERE Condition = True Check the rows affected, then change it to: UPDATE ImportantTable SET ImportantColumn = NewValue WHERE Condition = True

mulmen 10y ago

I have never done what the GP describes but I consider myself very lucky as it's a very common mistake. I have heard enough horror stories to always keep that concern in the back of my mind. I do what your coworker did and it's a great feeling when you get the "451789356 rows updated" message inside a transaction where you are trying to change Stacy's last name after her wedding and all you have to do is run a ROLLBACK. Then it's time to go get a coffee and thank your deity of choice.

desas 10y ago

Not doing this is like juggling with knives. I cringe every time I see a colleague doing it.

DrPhish 10y ago

My workflow for modifying production data is: 1) Write a select statement capturing the rows you want to modify and verify them by eyeball 2) (Optional) Modify that statement to select the unchanged rows into a temp table to be deleted in a few days 3) Wrap the statement from step 1 in a transaction 4) Modify the statement into the update or delete 5) Check that rowcounts haven't changed from step 1 6) Copy-and-paste the final statement into your ticketing or dev tracking system 7) Run the final statement It may be overkill, but the amount of grief it can save is immeasurable

rzzzt 10y ago

MySQL's console can also warn you if you issue a potentially destructive statement without a WHERE clause: http://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html#safe-updates http://dev.mysql.com/doc/refman/5.7/en/mysql-tips.html#safe-...

dotancohen 10y ago
The `--i-am-a-dummy` flag, which I wish were called `--i-am-prudent` because we all are dummies.

lobster_johnson 10y ago

One of PostgreSQL's best features is transactional DDL: You can run "drop table" etc. in a transaction and roll back afterwards. This has saved me a few times. It also makes it trivial to write atomic migration scripts: Rename a column, drop a table, update all the rows, whatever you want -- it will either all be committed or not committed at all. Surprisingly few databases support this. (Oracle doesn't, last I checked.)

scriptman 10y ago

I do a select first using the where clause I intend to use to get the row count. Then open a transaction around the update with that same where clause, check the total number of rows updated matches the earlier select, then commit. This approach definitely reduces your level of anxiety when working on a critical database.

foobarian 10y ago

It works for more than databases. - With shells, I prefix risky commands on production machines with #, especially when cutting and pasting - Same for committing stuff into VCS, especially when I'm cherrypicking diffs to commit - Before running find with -delete, run with -print first. Many other utilities have dry-run modes

lysp 10y ago

My practise is to do: UPDATE ImportantTable SET ImportantColumn = ImportantColumn WHERE Condition = True Check the rows affected, then change it to: UPDATE ImportantTable SET ImportantColumn = NewValue WHERE Condition = True