Woodridge Tech Talk

Our ongoing series of bi-weekly technical presentations

SQL Statements on Production

Safely Running SQL Statement on a Production Server

SQL Statements

The title of this blog should frighten most developers. Generally speaking, we want to avoid running a SQL statement – UPDATE, INSERT or DELETE – on production if it is possible. One statement can do a lot of damage if it’s not 100% correct.

But, there are times where due to time constraints or other considerations, it is not possible or practical to create a feature or developer-only tool to make a change. If this is the case, there are a few precautions that can be taken to minimize the potential impact of a statement and to quickly revert changes.

Measuring The Potential Effect

The first step is to check the WHERE clause of your statement. For example, if I have a “users” table with the unique key “id”, and a property “first_name”, I might try using the following to make a change:

UPDATE users SET first_name="Chad" WHERE id>22;

But – first, I can run the following:

SELECT * FROM users WHERE id>22;

From this, I would see that I am affecting way more users than the one I intended to update (unless everyone happens to share my name), and could modify my query to use “=” instead of “>”.

Transactions

If you’re using MySQL, you may be using innoDB as the storage engine behind your tables (if not, you’re probably running on an older version). InnoDB supports transactions. From the MySQL documentation:

“Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.”

I make use of transactions frequently. Laravel’s implementation of transactions helps when several inserts/updates/deletes need to succeed or fail as a group. We can also make use of them when we want to have an additional measure of safety when running a statement on production.

If I missed the “>” in the query above and ran the statement anyways, that would be a huge problem for production. Unless I wrapped the statement in a transaction first:

START TRANSACTION;
UPDATE users SET first_name="Chad" WHERE id=22;
#Oh no! I updated WAY more users than the 1 I wanted!
ROLLBACK;

The statements inside the transaction are not executed until a COMMIT is executed, or a ROLLBACK undoes the changes. This provides a safer environment to make changes if you absolutely must run a statement on production. However, this is not a sandbox mode where you can sit there for minutes trying things out and then rolling back when you are done. Rows modified inside of an uncommitted/rolled-back transaction will remain locked by default. So, make sure that you already have the statements you want to execute written somewhere, so you can quickly run them and evaluate whether only the changes you wanted happened.

Summary

Running new SQL statements on a production server is still not something we want to do. But in those cases where it cannot be avoided, taking the time to test your WHERE clause and to use a transaction can help you catch errors before incorrect data is written, changed, or deleted. Of course, you should still have automated backups in place, and (if possible) a service such as Amazon Bactrack enabled, to undo mistakes.

Chad Eatman is a software developer at Woodridge Software, a custom software development firm located near Denver, Colorado. Woodridge specializes in Web, Android, and iOS development and works with a variety of clients ranging from startups to Fortune 500 companies.

Categories: Tech Talk