The Gassed Pump

"Staff augmentation," was a fancy way of saying, "hey, contractors get more per hour, but we don't have to provide benefits so they are cheaper," but Stuart T was happy to get more per hour, and even happier to know that he'd be on to his next gig within a few months. That was how he ended up working for a national chain of gas-station/convenience stores. His job was to build a "new mobile experience for customer loyalty" (aka, wrapping their website up as an app that can also interact with QR codes).

At least, that's what he was working on before Miranda stormed into his cube. "Stuart, we need your help. ProdTrack is down, and I can't fix it, because I've got to be at a mandatory meeting in ten minutes."

ProdTrack was their inventory system that powered their point-of-sale terminals. For it to be down company wide was a big problem, and essentially rendered most of their stores inoperable. "Geeze, what mandatory meeting is more important than that?"

"The annual team-building exercise," Miranda said, using a string of profanity for punctuation. "They've got a 'no excuses' policy, so I have to go, 'or else', but we also need to get this fixed."

Miranda knew exactly what was wrong. ProdTrack could only support 14 product categories. But one store- store number 924- had decided that they needed 15. So they added a 15th category to the database, threw a few products into the category, and crossed their fingers. Now, all the stores were crashing.

"You'll need to look at the StoreSQLUpdates and the StoreSQLUpdateStatements tables," Miranda said. "And probably dig into the ProductDataPump.exe app. Just do a quick fix- we're releasing an update supports any number of categories in three weeks or so, we just need to hold this together till then."

With that starting point, Stuart started digging in. First, he puzzled over the tables Miranda had mentioned. StoreSQLUpdates looked like this:

ID	STATEMENT	STATEMENT_ORDER
145938	DELETE FROM SupplierInfo	90
148939	INSERT INTO ProductInfo VALUES(12348, 3, 6)	112

Was this an audit tables? What was StoreSQLUpdateStatements then?

ID	STATEMENT
168597	INSERT INTO StoreSQLUpdates(statement, statement_order) VALUES ('DELETE FROM SupplierInfo', 90)
168598	INSERT INTO StoreSQLUpdates(statement, statement_order) VALUES ('INSERT INTO ProductInfo VALUES(12348, 3, 6)', 112)

Stuart stared at his screen, and started asking questions. Not questions about what he was looking at, but questions about the life choices that had brought him to this point, questions about whether it was really that bad an idea to start drinking at work, and questions about the true nature of madness- if the world was mad, and he was the only sane person left, didn't that make him the most insane person of all?

He hoped the mandatory team building exercise was the worst experience of Miranda's life, as he sent her a quick, "WTF?" email message. She obviously still had her cellphone handy, as she replied minutes later:

Oh, yeah, that's for data-sync. Retail locations have flaky internet, and keep a local copy of the data. That's what's blowing up. Check ProductDataPump.exe.

Stuart did. ProductDataPump.exe was a VB.Net program in a single file, with one beautifully named method, RunIt, that contained nearly 2,000 lines of code. Some saintly soul had done him the favor of including a page of documentation at the top of the method, and it started with an apology, then explained the data flow.

Here's what actually happened: a central database at corporate powered ProdTrack. When any data changed there, those changes got logged into StoreSQLUpdateStatements. A program called ProductDataShift.exe scanned that table, and when new rows appeared, it executed the statements in StoreSQLUpdateStatements (which placed the actual DML commands into StoreSQLUpdates).

Once an hour, ProductDataPump.exe would run. It would attempt to connect to each retail location. If it could, it would read the contents of the central StoreSQLUpdates and the local StoreSQLUpdates, sorting by the order column, and through a bit of faith and luck, would hopefully synchronize the two databases.

Buried in the 2,000 line method, at about line 1,751, was a block that actually executed the statements:

If bolUseSQL Then For Each sTmp As String In sProductsTableSQL sTmp = sTmp.Trim() If sTmp <> "" Then SQLUpdatesSQL(lngIDSQL, sTmp, dbQR5) End If Next sTmpEnd If

Once he was done screaming at the insanity of the entire process, Stuart looked at the way product categories worked. Store 924 didn't carry anything in the ALCOHOL category, due to state Blue Laws, but had added a PRODUCE category. None of the other stores had a PRODUCE category (if they carried any produce, they just put it in PREPARED_FOODS). Fixing the glitch that caused the application to crash when it had too many categories would take weeks, at least- and Miranda already told him a fix was coming. All he had to do was keep it from crashing until then.

Into the StoreSQLUpdates table, he added a DELETE statement that would delete every category that contained zero items. That would fix the immediate problem, but when the ProductDataPump.exe ran, it would just copy the broken categories back around. So Stuart patched the program with the worst fix he ever came up with.

If bolUseSQL Then For Each sTmp As String In sProductsTableSQL sTmp = sTmp.Trim() If sTmp <> "" Then If nStoreNumber = 924 And sTmp.Contains("ALCOHOL") Then Continue For ElseIf nStoreNumber <> 924 And sTmp.Contains("PRODUCE") Then Continue For Else SQLUpdatesSQL(lngIDSQL, sTmp, dbQR5) End If End If Next sTmpEnd If

[Advertisement] Application Release Automation - build complex release pipelines all managed from one central dashboard, accessibility for the whole team. Download and learn more today!