71

Suppose your team writes a software system that is running fine.

One day one of the engineers mistakenly runs some SQL queries that change some of the DB data, then forgets about it.

After some time you discover the corrupted/erroneous data and everyone scratches their heads as to which part of the code caused this and why, to no avail. Meanwhile the project manager insists that we find the part of the code that caused it.

How do you deal with this?

  • 31
    If the engineer forgot about it, how do you know that's what happened? How do you it was corrupted by someone running a script, and not by a bug? – DaveG Oct 25 '18 at 21:05
  • 17
    He had an epiphany after a day or two. This is a hypothetical in case he never did remember which could have easily been the case. – Nik Kyriakides Oct 25 '18 at 21:07
  • 11
    This is a hypothetical. I'm sure the PM would have us chase this is as much as we can if he never did remember. I know I would. – Nik Kyriakides Oct 25 '18 at 21:14
  • 58
    xkcd.com/583 ;) [NSFW language] – Baldrickk Oct 26 '18 at 8:25
  • 99
    “Suppose your team writes a software system that is running fine.” Stop teasing me with impossible fantasies! – Paul D. Waite Oct 26 '18 at 11:24
131

It is obvious no project manager will invest an infinite amount of time into such a problem. They want to prevent the same situation happening again.

To achieve this goal, even if one cannot find the root cause of such a failure, it is often possible to take some measures to

  • Detect such failures earlier in case they reoccur
  • Make it less likely the same failure will happen again
  • Make the system more robust against the specific kind of inconsistency

For example, more detailed logging, more finegrained error handling, or immediate error signaling could help to prevent the same error striking again, or to find the root cause. If your system allows adding database triggers, maybe it is possible to add a trigger which forbids the inconsistency being introduced in the first place.

Think of what the appropriate kind of action might be in your situation, and suggest this to the team; I am sure your project manager will be pleased.

One day one of the engineers mistakenly runs some SQL queries that change some of the DB data, then forgets about it.

As mentioned by others, it is also a good idea to forbid such a procedure (if you have influence on how the system is operated). No one should be allowed to run undocumented ad-hoc queries which change database content. If there is a need for such a query, make sure there is a policy to store the query together with its execution date, the name of the person who executed it, and the reason why it was used, in a documented place.

  • 8
    @NicholasKyriakides Probably both. All of these are common-sense measures to make "deferred" debugging simpler. They've probably been written in countless procedures. – Nic Hartley Oct 25 '18 at 22:24
  • 29
    It does happen from time to time that you have some kind of serious problem in a production system and fail to determine the cause despite significant effort. Ultimately, you attribute it to cosmic rays and try to improve reporting (so if it happens again, you'll have a better chance of finding the cause) and mitigation (so if it happens again, damage will be minimal) and see if it repeats. – David Schwartz Oct 26 '18 at 3:29
  • 2
    @Nicholas Kyriakides: personal experience over several decades. – Doc Brown Oct 26 '18 at 4:10
  • 4
    It should also be noted that it is very possible that, even if there was a bug, it might not be there anymore. The best you can sometimes do is fix the data, and improve testing/procedures to make sure the same problem doesn't happen again. – kutschkem Oct 26 '18 at 7:17
  • 2
    Finding intermittent issues is all about logging and finding a choke point that can detect them as they occur, then walking backwards from there to pinpoint the source. Sometimes it requires unpleasant things like triggers, or deploying code with noisy error logging, just to get a handle on the when/where of the error. – AaronLS Oct 26 '18 at 19:17
48

This is not a bug

At least not on your code. It is a bug in your process. Your project manager should be a lot more worried about your process than your code.

How do you deal with this?

Quite simply, by not letting engineers change production or shared development databases.


Assuming this is a shared development database:

Ideally, if at all possible, avoid having a shared database in the first place. Instead, have per-developer databases that are short-lived. This should be automated with scripts, otherwise the cost to test becomes too great, and there's a incentive to not test things. You can have these databases on either the developer's workstation, or on a central server.

If, for some reason, you absolutely MUST have a shared database, you should use fixtures - essentially, something that sets the database to a known-good state every time you need to use it. This avoids developers getting bitten by other people's changes.

If you need to apply permanent changes to the database, you should commit them to your source control. Setup your database such that devs don't have permission to write to it directly, and have a program that pulls changes from source control and applies them.

Finally, from your description on how you're debugging things, it sounds like you're not using CI. Use CI. It's a bit of a pain to setup, but it'll save SO much time in the long run, not to mention stop you from worrying about unreproducible database bugs. You'll only have to worry about heisenbugs now!


Assuming this is a production database:

If your devs are changing production databases, many things have gone horribly wrong, even if the changes are absolutely correct.

Developers should never access production databases. There's absolutely no reason to, and so many things that can go very very wrong.

If you need to fix something in a production database, first you backup, restore that backup on a different (development) instance, and then play around that development database. Once you think you have a fix ready (on source control!), you re-do the restore, apply the fix, and see the result. Then, after backing things up again (and ideally preventing concurrent updates), you fix the production instance, ideally through a software patch.

If you need to test something in a production database... no, you don't. Whatever tests you need to do, you should do in a development instance. If you need some data to do the tests, you get that data in there.

  • 12
    So, your recommended solution is time travel? – Benubird Oct 26 '18 at 9:06
  • 7
    Although this is a decent solution for the example given, the question has a much more general context of dealing with bugs that can not be reproduced and managers who want them to persue these. That can apply to much more than just database issues and permission management. I feel like this answer doesn't actually answer the intended question, just the given example. – Kyle Wardle Oct 26 '18 at 10:00
  • @KyleWardle Agreed. I think Doc Brown's answer covers the general case quite well (detailed logging and error handling, guard conditions). I mostly added mine because I saw no one had mentioned the process failures that led to the problem in the first place – goncalopp Oct 26 '18 at 19:11
  • 2
    @Benubird I think the answer boils down to "the way you deal with this is preventing it from happening again". I don't think you can "solve" a corrupted production database from a software engineering perspective. – goncalopp Oct 26 '18 at 19:16
  • 1
    You're not going to change code to put data in the dev database. Everywhere I've worked, including huge corporations, developers are free to insert test data, and use the same credentials the application uses. – David Conrad Oct 28 '18 at 3:07
13

A production database should have full access logging and role based access controls. Thus you should have hard evidence as to WHO did WHAT WHEN to the database thus moving the attention from the code to poor operational security.

  • 2
    It sounds like they may not know exactly when the data corruption occurred, which could make it difficult to figure out what logs they need to investigate. – Nathanael Oct 25 '18 at 20:54
  • 3
    Unfortunately on tracing one of these we discovered it was trashing the logs too. (Yes, It. the bug was real.) – Joshua Oct 25 '18 at 23:04
  • Couple logging with scheduled jobs checking for data integrity, even if only overnight, means issues can be flagged early and resolved. If you want to be really careful, require peer review for changes. – Keith Oct 26 '18 at 0:11
  • Everywhere I've worked the devs connect to the database with the same credentials the app uses, so the access logging would only show when that id had made the change, not that it was done by a human rather than a program. I suppose you could compare the timestamp against the application logs to see if the application was doing anything that would write to the db at that time. – David Conrad Oct 26 '18 at 21:48
  • @DavidConrad: Why do devs have access to the credentials that the app uses in production? You should be using some kind of secret management so that those credentials cannot even be read except for by your application service account, from production application servers. – Daniel Pryden Oct 29 '18 at 13:10
6

In this case, you ultimately did figure out the cause, but taking your hypothetical that you didn't...

First, analyze what changed. If the system was running fine before, a careful look at everything that's been done recently might reveal the change that caused the bug. Systematically review your version control, CI/deployment systems, and configuration control to see if anything changed. Run git bisect or an equivalent mechanism to perform a binary search. Check logs. Hunt around for logs you didn't know you had. Talk to everyone with access to the system to see if they've done anything recently. For your problem, if you're thorough enough in this process, this should hopefully reveal the forgotten SQL queries.

Second, instrumentation. If you can't directly find the cause of a bug, add instrumentation around it to gather data about the problem. Ask yourself "if I could reproduce this bug on command, what would I want to look at in the debugger," and then log that. Repeat as needed until you have a better understanding of the problem. As Doc Brown suggests, add logging for states relevant to the bug. Add assertions that detect corrupt data. For example, if your bug is an application crash, add a crash logging mechanism. If you already have one, great, add annotations to the crash logs to record state potentially relevant to the crash. Consider whether concurrency issues may be involved and test to exercise thread-safety.

Third, resiliency. Bugs are inevitable, so ask yourself how you can improve your systems to be more resilient so recovery from the bug is easier. Could your backups be improved (or existent)? Better monitoring, failover, and alerting? More redundancy? Better error handling? Decouple dependent services from each other? Can you improve your processes around database access and manual queries? At best, these things will make the consequences of your bug less severe, and at worst, they're probably good things to do anyway.

4
  1. Explain to your project manager that you think the most likely cause is manual database access.
  2. If they still want you to look for the code that caused this, go and have another look at the code.
  3. Come back in a couple of hours (or some other appropriate time) and say you can't find any code which would have caused this, therefore you still believe the most likely cause is manual database access.
  4. If they still want you to look for the code, ask how much time they would like you to spend on this. Subtly remind them that you won't be working on feature X, bug Y or enhancement Z while you're doing this.
  5. Spend as much time as they ask. If you still think the most likely cause is manual database access, tell them this.
  6. If they still want you to look for the code, escalate the issue as this has clearly become an unproductive use of your team's time.

You may also want to consider if you should add in an extra processes to reduce the likelihood of manual database access causing this kind of issue in future.

  • 1
    I had no idea one of the engineers did a manual update + engineers almost never run queries directly on the database. This one just did, as a one-off thing and forgot about it. We spent a day + preparing to spent a full week on finding out what's wrong. My question is what happens if you can't find the cause and can't suggest what the potential cause might be. – Nik Kyriakides Oct 25 '18 at 20:59
  • 5
    "My question is what happens if you can't find the cause and can't suggest what the potential cause might be" This is the exact reason the 'won't fix - can't duplicate' flag was invented. – esoterik Oct 25 '18 at 21:31
3

I was working on the development team for a mainframe database product when a customer reported they had a corrupt database. A corruption in the sense that the internal state of the bits on the disc meant the database was not readable via the database software. In the mainframe world customers are paying you $millions and you need to take this seriously. This is what we did:

Step 0: help the customer to get up and running again by repairing the database.

Step 1: by examining the file on disc at hex level we determined that the corruption was systematic: there were many instances of the same corruption. So it was definitely caused at the level of the database software. Indeed, it was sufficiently systematic that we felt we could rule out multi-threading problems.

After eliminating many other theories we homed in on a utility that could be used for physical reorganization of the database. It seemed to be the only code that had access to the data at the right level. We then discovered a way of running this utility, with carefully selected options, which reproduced the problem. The customer wasn't able to confirm or deny that this is what they had done, but since it was the only explanation we could come up with, we decided that it was the likely cause, and they had little choice but to accept our diagnosis.

Step 2: We then made two changes to the software: (a) made it harder to cause this effect accidentally through a "yes I know what I'm doing" user interface, and (b) introducing a new log file so that if it ever happened again, we would have a record of the user actions.

So basically (a) repair the damage and restore live running, (b) find the root cause, (c) do whatever is needed to prevent it happening again, or to enable easy diagnosis if it does happen again.

3

From my experience what your boss wants is some level assurance that this will not reoccur. If it is the case that no code was the cause, because that is assured by unity testing, so assuming you already have testing coverage on your code base, the solution should be adding "testing" to your database. I'll quote Don Gilman, cuz he nailed there:

A production database should have full access logging and role based access controls. Thus you should have hard evidence as to WHO did WHAT WHEN to the database thus moving the attention from the code to poor operational security.

But also, you should have Standard Operating Procedure on changing data in production. For example, no DBA should change data, no developer should execute the change themselves and they should, as defined in the SOP, require to each other formally the change by mail or ticket.

There must be a quote like this somewhere, if not you can quote me on it:

There is a perfectly good reason for chefs not being the ones responsible for cleaning toilets.

1

There are several things that must be done with non-reproducible bugs.

  1. Create a ticket for it

Create a ticket, and log everything you can think of in the ticket. Also check if this "bug" has been logged before, and link the tickets together. Eventually you may get enough tickets to establish a pattern for how to reproduce the bug. This includes work arounds used to try an avoid it. Even if this is the only instance, if there is a first time, there will eventually be second time. When you do find the cause, close the ticket with an explanation of what the cause was so that you have a strong idea of what happened if it happens again (fix lost in bad merge)

  1. Do a hardening analysis

Look at the system, what failed, and how it failed. Try to find area's of the code that can be updated to make failure less likely. Some examples...

  • Replace ad-hoc code with a dedicated call (like execute(<query>) with executeMyStoredProcedure(<params>)
  • Run nightly verification scripts to verify data integrity (so that this can be detected within 24h next time)
  • Add/improve logging and archiving (backing up).
  • Change improper security limits (for example, people/programs that only read data don't have write permission; Not letting developers who are not responsible for production from being able to log into the production servers)
  • Add data verification/sanitation where missing

This might not fix the bug, but even if it doesn't, the system is now more stable/secure so it still pays off.

  1. Add system alerts

Kinda part of 2, but something happened, and you need to know when it happens again. You should create some health check scripts/programs to monitor the system, so that admins can be alerted within 24h of the bug resurfacing (the less delay, the better, within reason). This will make cleanup much easier. (Note that in addition to the databases's logs, the OS should also be logging who logs into it, and any non-read actions they perform. At the very least, there should be network logs of traffic to that machine)

0

Your problem was not caused by a fault in your software, but by someone fiddling with the database. If you call things going wrong a "bug", then your bug is easily reproducible: Things will always go wrong when someone does stupid things to the database. And there are ways to avoid this "bug", by not allowing the database to be modified manually or using untested software, and by strictly controlling who can modify the database.

If you only call faults in your database a "bug", then you don't have an irreproducible bug, you have no bug at all. You may have a bug report, but you also have evidence that the problem was not caused by a bug. So you can close the bug report, not as "irreproducible", but something else like "damaged database". It's not uncommon to have bug reports where investigation shows there is no bug, but a user used the software wrong, the user's expectations were wrong etc.

In that case you still know there was a problem that you don't want to be repeated, so you take the same action as in the first case.

protected by gnat Oct 26 '18 at 21:52

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.