Suppose your team writes a software system that is (quite surprisingly) 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?
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
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.
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.
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.
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.
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.
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.
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.
There are several things that must be done with non-reproducible bugs.
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)
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...
execute(<query>) with executeMyStoredProcedure(<params>)This might not fix the bug, but even if it doesn't, the system is now more stable/secure so it still pays off.
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)
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.
