CodeSOD: Waiting for the Future

One of the more interesting things about human psychology is how bad we are at thinking about the negative consequences of our actions if those consequences are in the future. This is why the death penalty doesn't deter crime, why we dump massive quantities of greenhouse gases into the atmosphere, and why the Y2K bug happened in the first place, and why we're going to do it again when every 32-bit Unix system explodes in 2038. If the negative consequence happens well after the action which caused it, humans ignore the obvious cause and effect and go on about making problems that have to be fixed later.

Fran inherited a bit of technical debt. Specifically, there's an auto-numbered field in the database. Due to their business requirements, when the field hits 999,999, it needs to wrap back around to 000,001. Many many years ago, the original developer "solved" that problem thus:

function getstan($callingMethod = null){ $sequence = 1; // get insert id back $rs = db()->insert("sequence", array( 'processor' => 'widgetsinc', 'RID' => $this->data->RID, 'method' => $callingMethod, 'card' => $this->data->cardNumber ), false, false); if ($rs) { // if query succeeded... $sequence = $rs; if ($sequence > 999999) { db()->q("delete from sequence where processor='widgetsinc'"); db()->insert("sequence", array('processor' => 'widgetsinc', 'RID' => $this->data->RID, 'card' => $this->data->cardNumber), false, false); $sequence = 1; } } return (substr(str_pad($sequence, 6, "0", STR_PAD_LEFT), -6));}

The sequence table uses an auto-numbered column. They insert a row into the table, which returns the generated ID used. If that ID is greater than 999,999, they" delete the old rows. They then insert a new row. Then they return "000001".

Unfortunately, sequences don't work this way in MySQL, or honestly any other database. They keep counting up unless you alter or otherwise reset the sequence. So, the counter keeps ticking up, and this method keeps deleting the old rows and returning "000001". The original developer almost certainly never tested what this code does when the counter breaks 999,999, because that day was so far out into the future that they could put off the problem.

Speaking of putting off solving problems, Fran also adds:

For the past 2 years this function has been returning 000001 and is starting to screw up reports.

Broken for at least two years, but only now is it screwing up reports badly enough that anyone wants to do anything to fix it.

[Advertisement] Easily create complex server configurations and orchestrations using both the intuitive, drag-and-drop editor and the text/script editor. Find out more and download today!