In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.
Short of using a test database, what are options to tell what an update statement will do before running it?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
What about Transactions? They have the ROLLBACK-Feature.
START TRANSACTION; SELECT * FROM nicetable WHERE somthing=1; UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1; SELECT * FROM nicetable WHERE somthing=1; #check COMMIT; # or if you want to reset changes ROLLBACK; SELECT * FROM nicetable WHERE somthing=1; #should be the old value
Answer on question from @rickozoe below:
In general these lines will not be executed as once. In PHP f.e. you would write something like that (perhaps a little bit cleaner, but wanted to answer quick 😉 ):
$MysqlConnection->query('START TRANSACTION;'); $erg = $MysqlConnection->query('UPDATE MyGuests SET lastname='Doe' WHERE id=2;'); if($erg) $MysqlConnection->query('COMMIT;'); else $MysqlConnection->query('ROLLBACK;');
Another way would be to use MySQL Variables (see https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
# do some stuff that should be conditionally rollbacked later on SET @v1 := UPDATE MyGuests SET lastname='Doe' WHERE id=2; IF(v1 < 1) THEN ROLLBACK; ELSE COMMIT; END IF;
But I would suggest to use the language wrappers available in your favorite programming language.
In addition to using a transaction as Imad has said (which should be mandatory anyway) you can also do a sanity check which rows are affected by running a select using the same WHERE clause as the UPDATE.
So if you UPDATE is
UPDATE foo SET bar = 42 WHERE col1 = 1 AND col2 = 'foobar';
The following will show you which rows will be updated:
SELECT * FROM foo WHERE col1 = 1 AND col2 = 'foobar';
Set Autocommit to OFF.
set autocommit=0; sets the autocommit off for the current session.
You execute your statement, see what it has changed, and then rollback if it’s wrong or commit if it’s what you expected!
The benefit of using transactions instead of running select query is that you can check the resulting set easily.
For testing update, hash # is your friend.
If you have an update statement like:
UPDATE wp_history SET history_by="admin" WHERE history_ip LIKE '123%'
You hash UPDATE and SET out for testing, then hash them back in:
SELECT * FROM #UPDATE wp_history #SET history_by="admin" WHERE history_ip LIKE '123%'
It works for simple statements.
An additional practically mandatory solution is, to get a copy (backup duplicate), whenever using update on a production table. Phpmyadmin > operations > copy: table_yearmonthday. It just takes a few seconds for tables <=100M.
I’ve seen many borked prod data situations that could have been avoided by typing the
WHERE clause first! Sometimes a
WHERE 1 = 0 can help with putting a working statement together safely too. And looking at an estimated execution plan, which will estimate rows affected, can be useful. Beyond that, in a transaction that you roll back as others have said.
You can also use
WHERE FALSE for MySQL, but keep in mind other DBMSes like SQL Server won’t accept that.
One more option is to ask MySQL for the query plan. This tells you two things:
- Whether there are any syntax errors in the query, if so the query plan command itself will fail
- How MySQL is planning to execute the query, e.g. what indexes it will use
In MySQL and most SQL databases the query plan command is
describe, so you would do:
describe update ...;
SELECT of it,
like if you got
UPDATE users SET id=0 WHERE name='jan'
convert it to
SELECT * FROM users WHERE name='jan'
In these cases that you want to test, it’s a good idea to focus on only current column values and soon-to-be-updated column values.
Please take a look at the following code that I’ve written to update WHMCS prices:
# UPDATE tblinvoiceitems AS ii SELECT ### JUST ii.amount AS old_value, ### FOR h.amount AS new_value ### TESTING FROM tblinvoiceitems AS ii ### PURPOSES. JOIN tblhosting AS h ON ii.relid = h.id JOIN tblinvoices AS i ON ii.invoiceid = i.id WHERE ii.amount <> h.amount ### Show only updatable rows # SET ii.amount = h.amount
This way we clearly compare already existing values versus new values.
Run select query on same table with all
where conditions you are applying in update query.