MySQL natively supports transactions on InnoDB tables (and, therefore, MariaDB and Percona).
But what are transactions, let’s look at it with a very simplified example.
In a banking system, I make a transfer of 100 € to my friend Luis. Internally, an update of my user is done by subtracting 100 € and a new update of the user Luis is done by adding 100 €. So far so good (and, of course, very simplified).
But what happens if after taking the 100 € from me the system fails and, therefore, does not add the 100 € to Luis, then 100 € remains lost in no man’s land.
To avoid this, there are transactions. We have a series of operations, which must all be executed, if everything goes well we confirm and they are executed, if something fails along the way we go back and everything was.
These are the transactions, which we start with START TRANSACTION; or with BEGIN; and if everything is as expected, we confirm it (and it is executed) with COMMIT; or if we detect any failure, we go back with ROLLBACK;
WordPress is not built with transactions, although we can use them in our queries (in 99% of cases they would not be necessary), but nothing prevents us from using them in our tests.
Actual use scenario
In this case, besides downloading the database and doing it locally, it was a bit complicated to perform the update and if it went wrong, restore the database and test again, so I used the transactions directly in MySQL.
It was a website that had been online for many years and had gone through several hostings and of the thousands of photos it had, there were about 100 that could not be regenerated from WP Cli for having the wrong path in the metadata (wp media regenerate --only-missing).
When viewing one of the errors Warning: Can't find "xxxyyyzzz" (ID 1881). and looking for the ID in the table wp_postmeta it was found that the value of _wp_attached_file was incorrect, of the type /home/.xxxx/yyyy/www.zzzz.com/wp-content/uploads/2008/09/xxxyyyzzz.jpg instead of 2008/09/xxxyyyzzz.jpg
The solution was to perform the replacement in the database, but in order to avoid errors and new tests restoring the database, the solution was to start a transaction, search, perform updateIf you want to change the ID field, search again, look at the value of that particular ID field, and when you check that everything is correct, run the update.
START TRANSACTION;
SELECT * FROM wp_postmeta WHERE meta_value like '/home/.xxxx/yyyy/www.zzzz.com/wp-content/uploads/%';
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '/home/.xxxx/yyyy/www.zzzz.com/wp-content/uploads/', '') WHERE meta_value like '/home/.xxxx/yyyy/www.zzzz.com/wp-content/uploads/%';
SELECT * FROM wp_postmeta WHERE meta_value like '/home/.xxxx/yyyy/www.zzzz.com/wp-content/uploads/%';
SELECT * FROM wp_postmeta WHERE post_id=1881;
ROLLBACK;We initiate a transaction.
In the first query we search for all entries containing that string and we get back about 100 records.
In the second query we execute a UPDATE eliminating that string and keeping the rest of the path.
In the third query, we run the first query again, but now we get zero results.
In the fourth query we look at the postmeta of the original ID to check that the route is correct.
And then we return everything to the previous state.
And everything has remained as it was, without any change, but verifying that our query was correct. Actually the correct way to use it is with checks and perform ROLLBACK or COMMIT according to the result.
But in this case it was something manual, in which I check the results and when I see that everything is correct, I simply execute the query of UPDATE without transactions when I check that the results are the desired ones.
And as you can see, we can apply the same to a multitude of situations where we are not 100% sure of the results on the DB. Always remember to create backups before doing anything, no matter how safe what you are going to do is, and if possible, always work on a copy (development, staging, local, etc.).
