The Sort That Changed Order Between Two Servers

It ended at the incident. It started at the duplicates. Every hand-off in between did exactly what it was told, and this is the one that lied.

The locale-dependent ordering has a single weak property, and here it is: each replica sorts correctly by its own collation, and the collations differ, so the same query returns a different order depending on which node answers, and the pagination assumes one order.

This was Postgres with two replicas built from different base images, and the mechanics are the same for any database whose text ordering depends on the operating system's locale.

Why the order differed

The text comparison is the collation, and the collation is the libc's, and the libc's is the container's locale, and the locale was the C on one image and the en_US.UTF-8 on the other, and the two were the different rules, and the rules were the different order, and the order was the pagination's assumption, and the assumption was the broken.

The C collation is the byte order, and the byte order is the case-sensitive and the punctuation-first, and the en_US is the dictionary order, and the dictionary is the case-insensitive-ish and the punctuation-ignored, and the two were the genuinely different sequences, and the sequences were the not a bug in either, and the not bug was the mismatch.

The pagination was the offset, and the offset was the order's dependent, and the dependent was the node's, and the node's was the load balancer's choice, and the choice was the random, and the random was the duplicate on the one node and the gap on the other, and the two were the incident.

Why the tests did not catch it

The test database was the one image, and the one image was the one collation, and the one collation was the consistent, and the consistent was the pass, and the pass was the confidence, and the confidence was the deploy, and the deploy was the two images, and the two images were the two collations, and the collations were the incident, and the incident is the reason the test environment should match the production's images rather than the production's schema.

The sort was the not asserted, and the not asserted was the implicit, and the implicit was the assumption, and the assumption was the stable, and the stable was the wrong, and the wrong was the untested, and the untested is the reason the ordering needs the explicit tiebreaker and the assertion.

This is the same family as the comparator that broke the transitive contract, which is why the two fixes look alike.

What I changed

Set the collation explicitly on the column. The column became the COLLATE "C" or the named collation, and the named was the explicit, and the explicit was the node's independence, and the independence was the fix, and the fix was the DDL, and the DDL was the discipline, because the implicit collation is the environment's.

Added a unique tiebreaker to every ORDER BY. The tiebreaker was the identifier, and the identifier was the total order, and the total order was the deterministic, and the deterministic was the pagination's safety, and the safety was the fix, and the fix was the one column, and the column was the discipline.

Standardised the base image across the replicas. The image was the one, and the one was the locale's consistency, and the consistency was the fix, and the fix was the Dockerfile, and the Dockerfile was the discipline, because the two images are the two behaviours.

Asserted the ordering in the test with the ambiguous data. The test data was the mixed case and the accented characters, and the characters were the collation's exposure, and the exposure was the catch, and the catch was the guard, and the guard was the prevention, and the prevention was the fix.

Compared the replicas' settings directly. The query was the SHOW LC_COLLATE on each node, and the each node was the comparison, and the comparison was the mismatch's proof, and the proof was the diagnosis, and the diagnosis was the fix's entry, and the entry was the discipline.

Checked the index's collation as well as the column's. The index was the built with the collation, and the collation was the ordering's, and the ordering's was the query's use, and the use was the mismatch's second place, and the place was the rebuild, and the rebuild was the discipline, because an index built under one collation cannot serve an ordering under another, so fixing the column without rebuilding leaves the same disagreement in a second location.

The rule

Text ordering in a database depends on the collation, which comes from the operating system's locale unless set explicitly, so replicas built from different images can order the same rows differently. Set the collation on the column, add a unique tiebreaker to every ORDER BY, standardise the base image, and assert the ordering with accented and mixed-case data.

The second page of the list repeated rows from the first and skipped others, because the two replicas had been built from different base images with different locales and the load balancer sent consecutive pages to different nodes. Name that one property in review and this class of bug gets hard to ship.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud