LockerGnomeTech news and nostalgia for today's technology professionals
Subscribe

Search

Software

A MySQL Payment Query Counted Zero Successes Because 1 Picked the Status ‘0’

An old payment table made a quick check in MySQL Workbench disagree with similar queries in the application.

A knitted illustration of Chris Pirillo reaching toward an empty receipt tray while the tray beside it is full.
Illustration: LockerGnome

A colleague called Valts over to look at two queries in MySQL Workbench. One showed a customer’s payments, all marked successful. The other asked for a count of those successful payments and came back with zero. Valts tried it on his own machine. “And this was reproducible on my machine, too,” he recalled.

Valts used 12 rows to illustrate the problem because he couldn’t remember the exact queries. In his example, SELECT * FROM payments WHERE client_id=1234 shows 12 payments with a 1 in the successful column. Add AND successful=1 to the count query and MySQL finds zero matches. That’s a rough result to get from a database that has just put all 12 payments in front of you.

The column definition gave the game away. Someone had declared successful as ENUM('0','1'), a list of two allowed strings. MySQL numbers those choices starting at one. The string '0' is first and the string '1' is second. In that comparison, the bare 1 meant “give me the first choice.” The quotes in successful='1' made it an order by name. Whoever numbered the lockers put locker zero in the first slot (naturally).

“Yet in code similar queries worked just fine,” Valts wrote. He pointed out that some query builders put quotes around every value and let MySQL sort out the types. That gives you a concrete difference to look for when a query works in the app and falls over in Workbench. A hand-typed query can look exactly like the one you meant to copy, right down to every digit. You can still be two little marks short.

A payment report is exactly the kind of place this mistake can turn up again. Someone writes a one-off reconciliation query, sees 1 in the grid, and types it into a filter. MySQL returns a perfectly respectable integer. Zero even looks tidy in a spreadsheet. A useful test would give the report one known successful payment and one known unsuccessful payment, then check the IDs it includes. In that fixture, the wrong filter can return the right count and still pick the wrong payment.

Names like 'successful' and 'unsuccessful' would make the mix-up easier to spot in review. If the column already feeds an application, changing those values is a migration. A smaller first move is to check the queries that read the column and make their comparisons agree with the stored strings.

Valts says the code and table were still in use when he wrote up the incident. The next person opening Workbench inherits the same column.

Mind the quotes.

More Software

Every Software story →