Redmine

Posting this in case someone else hits the same confusing symptom.

On our Redmine (6.0.x, MySQL 8.0.44) a project time entries list, e.g. /projects/foo/time_entries?issue_id=~12345, rendered 1 row while the pagination counter on the same page correctly said 4 ("1-4/4"). No errors anywhere in the logs. Changing the sort column made all 4 rows appear.

This is not a Redmine bug. The SQL Redmine generates is correct. We took the exact query from the general log and ran it straight in the mysql client: two identical statements differing only in the ORDER BY clause return 1 row vs 4 rows. It is a wrong result bug in the MySQL optimizer, in the semijoin "duplicate weedout" strategy when the plan sorts the driving table by row IDs before the joins.

The trigger is nothing exotic, just the pattern Redmine's permission checks produce: EXISTS subqueries on enabled_modules combined with ORDER BY + LIMIT. Whether the optimizer picks the bad plan depends on your data and index statistics, so the symptom can appear on some projects/filters and not others, and can come and go over time.

Verified affected: MySQL 8.0.42, 8.0.44, 8.0.46, 8.4.7 and 8.4.10. There is no fixed 8.x release. Fixed in MySQL 9.3+. Reported upstream with a small self-contained test case: https://bugs.mysql.com/bug.php?id=120943

Workaround until you can move to MySQL 9:

SET PERSIST optimizer_switch='duplicateweedout=off';

On Aurora/RDS, where SET PERSIST is not allowed, set optimizer_switch to duplicateweedout=off in the DB (cluster) parameter group instead. Either way it only applies to new connections, so restart Redmine afterwards.

With the flag off the optimizer falls back to its other semijoin strategies and results are correct. We saw no measurable performance impact.