Accidental Correctness and Auto Increment Database Identifiers
I recently ran into a couple of bugs that we should have found in our development, test and staging environments. However, they slipped through into our production environment. These were not major issues, just a couple of helper utilities. We thought they were working fine. When we tested the utilities they behaved correctly.
This was just luck of the draw. We had done our testing with clean databases — databases in which the various primary key id all started at an auto increment value of 1. Since, all of the inserted data started at the same id value, the functionality accidentally worked.
We considered a couple of approaches to prevent this from happening again. We already have a copy of our production data that we use for some testing. This is not always feasible since the amount of production data results in a substantial setup time. Most of the time, we will use the same scripts we have always used to create a clean database. However, we added an additional step to the scripts where they set the auto increment starting values for each table at 10,000 value increments from each other. This will prevent us from “accidentally” thinking our application is working.