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?
Autocommit OFF ...
MySQL
set autocommit=0;
It sets the autommit 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 !
EDIT: The benefit of using transactions instead of running select query is that you can check the resulting set easierly.
Not a direct answer, but 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.
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.
One more option is to ask MySQL for the query plan. This tells you two things:
In MySQL and most SQL databases the query plan command is describe
, so you would do:
describe update ...;
I know this is a repeat of other answers, but it has some emotional support to take the extra step for testing update :D
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.
make a SELECT
of it,
like if you got
UPDATE users SET id=0 WHERE name='jan'
convert it to
SELECT * FROM users WHERE name='jan'
Run select query on same table with all where
conditions you are applying in update query.
What about Transactions? They have the ROLLBACK-Feature.
@see https://dev.mysql.com/doc/refman/5.0/en/commit.html
For example:
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
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 and https://stackoverflow.com/a/18499823/1416909 ):
# 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.
Source: Stackoverflow.com