Out of app database modifications invalidate cached values

If there is duplicate suggestions, then there would be at least two suggestions, or otherwise they would not be duplicates, right? The "has_suggestions" flag in the database and on the user interface is "cached" data, and ideally should always reflect the current state. This is to avoid to do a count query on all suggestions for each string. So it is possible the data is somehow out of sync, in which case this should be investigated.

Well, cache might not be the right word. This flag is not regenerated from time to time but only when there is some actions regarding suggestions (one is added, approved or deleted). If you removed suggestions from the database by hand, the flag was not updated obviously, because it is updated as part of the process of doing the action with the suggestion in l10n_server. One option might be to have a "consistency update" of this flag on crons, with something like (pseudo code):

UPDATE has suggestion on all strings to 0;
UPDATE has suggestion on strings WHERE string id in (SELECT UNIQUE string id FROM translations WHERE is suggestion);

This has an obvious data inconsistency point between the two queries, where the UI would display bad data, so if you can build this to one reasonable query, then feel free :) Updating all strings one by one with COUNT() subqueries sounds like very bad performance wise (this is why we have this cached flag).

My solution to this:

CREATE TEMPORARY TABLE _l10n_temp SELECT DISTINCT sid FROM l10n_community_translation WHERE is_suggestion = 1 AND is_active = 1;
UPDATE l10n_community_translation SET has_suggestion = 0;
UPDATE l10n_community_translation SET has_suggestion = 1 WHERE sid IN (SELECT sid FROM _l10n_temp);

Btw, this inconsistency is due to me deleting manually translations directly in the database because someone imported a foreign translation into the German language translation.

hass: Konstantin's explanation on the manual deletion is exactly what makes this "cached" flag outdated, so if it did not happen earlier, it surely happened with that manual deletion. If it happened earlier (before any manual tweaking in the database), then you need to find out what has happened, or try to reproduce it. I have not heard this report from others, who don't work directly on their databases.

As I said in #4, we can add an update on cron, if it looks workable to make this fix automated.

The SQL queries are posted above.

Let's close this since it's more than a year old?

No inconsistencies arise from the normal use of the server and the moderate page now allows for mass-moderation. Therefore I'm not going to add one-time hacks like creating temporary tables for cleanups which are not needed on normal operation to the module.

We can either mark this fixed (as in fixed for the German server) or won't fix (as in this hack is not getting into l10n_server as the underlying error was not in the server).

Also retitling for the correct problem at hand.