There was an “unfortunate incident” the other day where one of our “developers” did something I like to call “forgetting the WHERE clause” during a DB update in UAT. Let’s try to avoid this. This is just from my experience – Nick or anyone else is welcome to chime in here for best practices
1.Turn off auto-commit
- in DbVisualizer, go to settings by hitting Cmnd+, (this is a handy osx standard)
- click the database tab
- choose MySQL
- click Transaction
- uncheck Auto Commit
- uncheck the boxes under “Ask when Auto Commit is OFF” unless you want a popup every time
- click Apply
- click OK
2.Always do a SELECT first, then modify it
don’t just look at the data and start writing your UPDATE or DELETE statement
write your select statement and validate that ONLY the rows you want to change will be updated:
SELECT * FROM fulfiller_account_settings
WHERE fulfiller_id = 96576
AND fkey='CARRIER'
---only 1 row, good!
Now change SELECT * FROM to UPDATE
And add your SET clause:
UPDATE fulfiller_account_settings
SET fvalue = 'CanadaPost'
WHERE fulfiller_id = 96576
AND fkey='CARRIER'
17:26:19 [UPDATE - 1 rows, 0.160 secs] Command processed
... 1 statement(s) executed, 1 rows affected, exec/fetch time: 0.160/0.000 sec [1 successful, 0 errors]
---only 1 row, good!
Now click the little “database with a floppy disk” icon in the toolbar to commit. To see if auto-commit is on or off, look in the area between the input/output windows (see green arrows in the screenshot).
A couple final notes:
1.If you do an update or delete WITHOUT auto-commit: when you do a select, the data WILL reflect your changes. The rest of the world will NOT see those changes and you have effectively done nothing until you commit, but to you it will look like the changes are made
2.You can do multiple transactions before doing a commit (someone correct me if I’m wrong)
3.You cannot roll back after a commit
4.If you have not committed and want to roll-back any changes in the buffer, hit the “database with a red dot” button to the Right of the commit button (see green arrow in the screenshot).
4.If you have not committed and want to roll-back any changes in the buffer, hit the “database with a red dot” button to the Right of the commit button (see green arrow in the screenshot).
