The report landed in finance’s inbox at 06:00: nine thousand and some accounts present in the billing export and absent from the customer directory. Finance did what finance does and started calling people. By 08:00 I was on the phone with someone who wanted to know if we had lost a database.
We had not lost a database. We had lost an argument about alphabetical order.
The job
The reconcile script was a pair of one-liners and a comm. Pull account IDs from billing, pull account IDs from the directory, sort both, and print whatever is only in the first list.
sort billing_ids.txt > /tmp/b.sorted
sort directory_ids.txt > /tmp/d.sorted
comm -23 /tmp/b.sorted /tmp/d.sorted > /tmp/missing.txtI ran it by hand that morning. Output: empty. Zero missing accounts. I ran it again to make sure I wasn’t hallucinating, because the thing I’d been told was nine thousand and the thing I was looking at was nothing.
So the script was right and the report was wrong, except the report was generated by that same script. Same file, same commands, same inputs.
What was different
Not the data. I diffed the cron run’s saved intermediate files against mine and the sorted files differed in order, not content. Same lines, shuffled.
The account IDs were mixed case alphanumerics. Things like ACC-0042, acc-0042b, Acc_17. Three different people had created IDs over eleven years, and nobody had been consistent.
My shell had LANG=en_US.UTF-8. In that locale, sort uses the locale’s collation rules: case is mostly ignored on the first pass, punctuation is mostly ignored, and acc-0042b lands next to ACC-0042. Cron runs with a nearly empty environment. No LANG, no LC_*. That’s the C locale, which is plain byte order: every uppercase letter sorts before every lowercase letter, and punctuation goes wherever ASCII puts it.
Both sort calls in the cron run used the same locale, so that part was consistent. The files were sorted correctly for C. The problem was upstream of that. The directory export was not produced by sort in the script at all. It came pre-sorted from a database query, sorted by the database in its own collation, and somebody had dropped the second sort call from the script two years ago because “it’s already sorted.”
Interactively I was running a different version of the pipeline than cron, because my environment papered over the difference. I’d fixed my own copy by habit, with a locale that happened to match the database.
Why comm doesn’t tell you
comm requires both inputs to be sorted in the same order it expects. It does a merge walk: look at the head of each file, emit the smaller, advance that file. If the files aren’t sorted the way comm compares them, the walk loses its place. A line that exists in both files gets reported as unique to one, because the other file’s head has already moved past it.
It will tell you, sometimes. GNU comm prints comm: file 2 is not in sorted order to stderr and exits non-zero if it notices, and --check-order makes it strict about that. But without the flag, it checks only some conditions, and the script’s stderr went to a cron mail spool nobody had read since the sysadmin before last. join has the same contract and the same trap.
The output was not a crash. It was a confident list of nine thousand accounts.
The fix
Two lines of fix and one of discipline.
export LC_ALL=C
sort billing_ids.txt > /tmp/b.sorted
sort directory_ids.txt > /tmp/d.sorted
comm --check-order -23 /tmp/b.sorted /tmp/d.sorted > /tmp/missing.txtLC_ALL=C at the top of any script that sorts, compares, or joins text. LC_ALL overrides every other LC_* and LANG, so there is nothing left to inherit from a terminal or a cron environment. Byte order is boring and identical everywhere, which is exactly the property you want from a thing a merge depends on. Re-sort both inputs inside the script even if the source “is already sorted.” Sorted by whom, in what collation, is a question you can’t answer from a file.
--check-order goes on every comm so that the day it breaks, it breaks loudly.
The discipline: reproduce a cron bug the way cron runs it, not the way your shell does. env -i /bin/sh script.sh gets you most of the way there. It’s the same lesson as the config file that changed and nothing reloaded: the thing you tested was not the thing that ran.
The part I keep thinking about
Finance called customers. A few of them were told their accounts might not exist. That cost more than any outage I’ve had this year, and it came from a job that did precisely what its code said.
I’ve written before about scripts that succeed against the wrong thing (the mirror that was perfect). This is the quieter variant. The script succeeded, the data was right, and the answer was wrong because two programs disagreed about what “in order” means. If your tooling is going to leave a number in front of a human, put a sanity bound on it. Nine thousand missing out of nine thousand and change total would have tripped any threshold anyone bothered to write.
Locale is a global variable with no declaration and no warning. Treat it like one.