My search engine's crawler backed off from hosts that kept failing: each consecutive failure doubled the wait before the next attempt, up to a cap of one day. During one long working session in July, one test of that logic failed in every run, and it kept being written off as a "pre-existing, unrelated" failure. At the end of the session I traced it, and the cause was a comparison that MySQL quietly did on text instead of numbers.
The query
The backoff was a single UPDATE, with the values bound as parameters through Doctrine DBAL:
SET crawl_claimed_until = DATE_ADD(NOW(),INTERVAL LEAST(? * POW(2, consecutive_failures), ?) SECOND)
The first parameter was the base wait, the second the cap, 86400 seconds. LEAST() picks the smaller of the growing wait and the cap. That's the whole idea, and it's correct for numbers.
What actually happened
To see it for myself, I ran the same expression against MySQL 8.0.46 from PHP, binding the parameters as strings the way Doctrine does by default, with PDO's default settings for MySQL. A base of 60 seconds, the cap of 86400, and a few failure counts:
- 3 failures: 480, as expected
- 20 failures:
'62914560', not 86400, and returned as a string - 34 failures:
'1030792151040', andDATE_ADDreturned NULL
At 20 failures the "smaller" value was 62,914,560 seconds, about two years. That's only smaller as text: the string '62914560' sorts before '86400' because '6' comes before '8'. At 34 failures, '1030792151040' sorts first because it starts with '1', and the interval is too large for DATE_ADD, which gives up with NULL. The cap that should have held at one day did nothing, exactly on the long failure streaks it existed for.
Why the cap became a string
Two things combine. First, how the values arrive. Doctrine DBAL binds a parameter as a string unless you give it a type, and PHP's PDO driver for MySQL emulates prepared statements by default, so the values are substituted into the SQL as quoted string literals. The server sees '86400', not 86400.
Second, how LEAST() chooses a comparison. MySQL's documentation lists the rules: integers are compared as integers, if any argument is a double they're compared as doubles, a mix of numbers and strings is compared as strings, and so on. Which rule applies to an expression like this isn't obvious from reading the list, and I wouldn't have predicted the outcome from it. The test above settles it: on this server, the comparison was done as strings.
The fix is two characters
The fix adds + 0 to the cap's placeholder:
INTERVAL LEAST(? * POW(2, consecutive_failures), ? + 0) SECOND
Arithmetic on the parameter turns it into a number before LEAST() sees it. In the same test, with + 0, every failure count from 20 upwards returned 86400 and DATE_ADD landed exactly one day ahead.
Two characters that look like decoration are easy to delete in a later cleanup, so the fix came with a comment above the query that says the + 0 is load-bearing and explains why.
The test that everyone had stopped reading
The test that caught this, the one checking that the backoff is capped at the maximum, had been failing on every run. It was labelled pre-existing and unrelated each time, and that label is what kept it from being looked at. Once it was traced, the suite went fully green, 303 of 303.
The general lessons are two. If you compare values in SQL, make sure both sides are the type you think they are, especially when the values arrive as bound parameters, which in PHP are strings by default. And a test that fails all the time isn't noise. It's a bug report nobody has read yet.
Leave a comment
Comments are reviewed before they appear.