Comment On Death by Delete

Rick worked for a regional ISP that provided hosting services. The customer base consisted primarily of consumers and small businesses, so the ISP offered a lot of "a la carte" services for things like SSL, authentication and database access.

A small chain of pet stores in the tri-state area, MegaPetCo, approached Rick's company because it was trying to expand into other cities and states. Having outgrown its current provider, MegaPetCo wanted Web space, a database and a few e-mail boxes. After careful consideration, it opted for the $74.99/ month Advanced Package that included a 10MB database, 100MB of disk space and 100GB of bandwidth.

After the switchover, it became apparent that what MegaPetCo needed far exceeded what it had ordered. For starters, its database ballooned to several gigabytes within the first month. On top of that, its Web traffic was far higher than the company's allotted bandwidth and other Web sites on the shared server were getting bogged down.

MegaPetCo opted to pay for overages, and its first monthly bill was astronomical: $4,281.32. Assuming that no one received the automated daily overage warning e-mails, the customer service rep offered to discount the bill and advised MegaPetCo to switch to a dedicated server. The owners of MegaPetCo, however, suspected the rep was "trying to pull a fast one" and instead opted to pay the bill, overages and all.

Behind the Scenes

MegaPetCo had no problem paying outrageous hosting bills, but the company was upset that its Web site ran incredibly slow. Although Rick's company had long since dedicated a "shared" hosting server for MegaPetCo, it would still take upward of a couple seconds for a page to load.

Rick decided to look at MegaPetCo's code. He was surprised that the company's Web site, which required a several-gigabyte database, was very small. As it turned out, there was a good reason for that: it was a very simple PHP-based framework that housed virtually everything -- including other PHP files -- in the database.

Further investigation found that 98 percent of all the company's requests came from one set of IP addresses, which turned out to be its stores and home offices. Rick guessed that MegaPetCo had kiosks for something and perhaps ran some of its sales traffic through the database.

How little he really knew. The shared database for this Web site -- a 10MB file which was meant to power nothing more than a couple of blogs and small-traffic forums -- was the database for the entire company. The Web site itself accounted for less than 5 percent of the total data in MegaPetCo's database. The rest was home-office stuff, including POP sales registers, payroll, HR, inventory, tax records, and a kind of dynamic storage for invoices and maintenance tickets.

The database was incredibly simple: a single table with hundreds of columns. It probably had humble beginnings as a spreadsheet and organically grew into a vast monolith over the seven or so years that MegaPetCo was in business. If MegaPetCo wanted to find out what insurance it gave a truck driver five years ago, it was in the same table as the one serving up pictures of bird seeds for sale. All told, the database had millions and millions of rows -- had being the operative word.

Uh Oh

One day, a developer was optimizing the database and removing records that MegaPetCo no longer needed. All it took was a single, poorly formed delete query to wipe out each and every row in the database table.

To say the developer panicked is akin to describing a quadruple amputation as a mere flesh wound. MegaPetCo's sales immediately ground to a halt. Along with everything else. Payroll, logistics, reporting, purchasing, you name it. Its Web site didn't work -- but that was the least of the company's worries. The status of its backups was bleaker still: None. Zip. Zilch. Nada.

The Aftermath

MegaPetCo blamed Rick's company. There was nothing the ISP could do, other than remind MegaPetCo that it had repeatedly advised the company to change services.

That didn't stop MegaPetCo from filing a lawsuit against Rick's company. Of course, it proved to be a bit of a challenge to sue without knowing how much money MegaPetCo actually had, so it simply withdrew the suit.

Unfortunately, the single delete query proved to be far more than MegaPetCo could bear. Within a few months, the company filed for bankruptcy and was forced to close every one of its stores -- laying off several hundred people along the way.

Death by Delete was originally published in the January 2009 issue of Redmond Developer News.