Ask HN: Strategies for not screwing up?
Yesterday I was trying to update a status flag on a couple MySQL records. I've learned to always select the dataset first to make sure the correct records are going to be updated. When writing the SQL update I copied and pasted the WHERE part of the select and ran the command. It errored out and so a quick review made it look like I had missed an AND. So the SQL looked something like this:
UPDATE table SET flag = 1 AND m = 123 AND s = 888 which updated nearly 5 years worth of data and 100K records by nuking the actual flag value. I was able to essentially restore the flag from a backup but it got me thinking about strategies to prevent oneself from being your own worst enemy. Doesn't have to be SQL related. Thanks!
6 comments
1. Make sure your backups work.
2. Pair programming.
2. Pair programming.
People screw up. I think it is better to make sure that you are always working over a safety net.
Safety nets for developers include automated tests, rolling out upgrades to a 'pre-production' clone of your real server before doing the real upgrade, and keeping your code in version control so you can reverse mistakes quickly.
For database work, not working on the production database, lots of backups, and using transactions all work for me, but some databases support Point In Time recovery if you have it turned on.
But what works best is doing code reviews, including reviews of SQL that is going to be executed against the database. Two heads are better than one.
Safety nets for developers include automated tests, rolling out upgrades to a 'pre-production' clone of your real server before doing the real upgrade, and keeping your code in version control so you can reverse mistakes quickly.
For database work, not working on the production database, lots of backups, and using transactions all work for me, but some databases support Point In Time recovery if you have it turned on.
But what works best is doing code reviews, including reviews of SQL that is going to be executed against the database. Two heads are better than one.
Thank you. I'm going to screw up again at some point just hoping to get best practices for preventing it. I should have mentioned I work for a tiny startup that doesn't have someone competent to review the SQL.
I think that even if the other developers are not familiar with SQL specifically, you can show them the query you plan on running.
I׳ve found that the fact that you need to explain something to someone makes you double check everything, and reduces the chances of screwing up. Even if they׳re not familiar with SQL, they can still easily understand the logic behind the query.
I׳ve found that the fact that you need to explain something to someone makes you double check everything, and reduces the chances of screwing up. Even if they׳re not familiar with SQL, they can still easily understand the logic behind the query.
Yeah, that is a problem. Could you train up one of the cofounders slowly on the basics? I mean, someone needs to fix the database if you are ill...
Mistakes are kind of human nature though. I think a lot of 'best practice' is about trying to spot them before they cause a big problem.
Mistakes are kind of human nature though. I think a lot of 'best practice' is about trying to spot them before they cause a big problem.
If you can, wrap all your SQL statements in a transaction and check them first :)
I often do a SELECT first using the same WHERE to make sure I am updating the correct rows. Also on my staging system I use a subset of the production database so that I can test destructive queries like this before running them on the production database.
That's essentially exactly what I did except when it threw an error I mistakenly substituted an AND in place of WHERE so the criteria were effectively ignored. We do run a stale version of the production database on staging but I didn't try running the SQL on it first so it wouldn't have helped. Do you run every destructive query against staging prior to production and then copy and past the FULL command?
It happens, just make sure you backup frequently. I am always nervous when directly modifying production databases. So, I take every precaution I can think of, even if it sounds like it will "obviously" work. Hopefully you will get to the point of not having to update the database manually very often, instead writing scripts that you know work.
Missing the condition on the UPDATE statement is a classic. I blame the SQL standard which has most of its syntax backwards. It should be FROM <table> WHERE <conds> UPDATE <variables>.
Anyway I dont think you can prevent screwups in general. The only thing you can do is have systems in place which mitigate the consequences of screwups. Like unit tests and backups.
Anyway I dont think you can prevent screwups in general. The only thing you can do is have systems in place which mitigate the consequences of screwups. Like unit tests and backups.