How to troubleshoot data related issue?

I don't know how far this subject has been discuss before but hope to get some knowledge and experience from this discussion here. 

So far, if an application contains a bug and so obvious that it's a technical issue, quite straight forward to troubleshoot and debug it in the code level and solve the problem. 

However, if everything is fine in the code level and the issue still persist, then it might be categorize as data related bug.

We might consider this is a very hard to trace kind of issue.

In such cases, may I know what is the best way or best practices to debug such issue?

Appreciate any recommendations, step-by-step and discussion here.

Thank you very much.

 

Cheers 

eddykuan:

However, if everything is fine in the code level and the issue still persist, then it might be categorize as data related bug.

How does the code get qualified as "fine"? Because it has been tested with said data and works correctly? Then what is a data-related bug? Where is the "bug" occurring? In the database? Or in the application? If it is in the application? How does the code get qualified as "fine"?

Notice the cyclic nature of the questioning.

well, if im not wrong, when the application first being build in the development stage, after finish development, the application has to go through the testing stage and then finally if everything working 'fine' here, then will be deploy into production stage. the database for this 3 stages are totally different where possibilities that the value might also different for security reasons. 

the unexpected data result or what i meant by the data-related bug found in the backend using backend database tools such as oracle or sql server. when we query the data in the backend, the return result is in an extreme unacceptable value for a certain criteria. for e.g. we expect a positive value but we get a negative value instead as a result of some computation where the code for implementing the computation is consider QC passed. 

 

thanks alot for your feedback and discussion here.

 

cheers  

so the problem is with your stored proc then?

i don't think it's in the stored proc cos if it is the stored proc, then all the rows will return the wrong values.

problem here is one of the row throwing a very odd value (which is a summation of all values for a certain date period) and i need to find out why this happen.

 

thanks and cheers.

 

the database for this 3 stages are totally different where possibilities that the value might also different for security reasons.

This looks to me the test data is inadequate. If the test data does not represent real-world usage, it should not be use to certify an application works correctly.

Without looking at any application code (i don't think we can thoroughly debug even if the code was shown) or stored procedure, we can only speculate on the correctness of it all. Your team has to trace logic paths down the code which that particular record should have taken, and have the realistic test data to verify it works as it should.

One particular aspect are caught but unhandled exceptions (empty catch blocks) that make the upper layer think everything went fine when in fact it has been messed up underneath.

It could be because data in the existing database are not cleared before the new stored procedures are used. This is especially so when modifying new stored procedures or queries in existing production databases.

If I were to hit such an issue, I would probably execute the query or stored procedure in the database that is giving the data error, without the summation (or calculation) of values. This would provide me with the set of data to be calculated. I would then do my own computation, probably in Excel, to see which data is bad.

To prevent such issues from happening, tables, views, queries and stored procedures should also under version control. The should be processes to detail which tables, views or other stored procedures are affected. And if the changes constitute a data migration plan, that is, to clean up and maintain the data. The plan should detail how and what data (columns) to change. There could be more, but a basic one like such will do for prevention.

could it also be that the database is corrupted?

pretty weird kind of thing happen. user reported on yesterday that the data is back to normal now. huh

but we still need to find the root cause of this issue which is quite difficult to do so. 

we suspect that there might be an external application which call a stored proc that will update some value in the table.

 

anyway, thanks for the comment so far.

 

cheers  

in my previous work, we had database problems which were ultimately traced to a corrupted SQL Server instance. unfortunately the db had been corrupted for three days by then, so the daily backups were also corrupted. we lost three days worth of data that time.