Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
l10n_update.check.inc (starting at line 112) uses the following SQL statement to check local update files:
SELECT p.name, l.language AS lang, f.* FROM {l10n_update_project} p INNER JOIN {languages} l
LEFT JOIN {l10n_update_file} f ON p.name = f.project AND l.language = f.language
WHERE p.status = 1 AND l.enabled = 1 AND (f.status IS NULL OR f.status = 1 AND f.last_checked < %d) ORDER BY last_checked
That throws an error in Postgres.
The following equivalent SQL code works on Postgres (but likely not in Mysql, due to the subselect):
SELECT pl.name, pl.language AS lang, f.*
FROM (SELECT p.name, l.language FROM {l10n_update_project} p, {languages} l WHERE p.status = 1 AND l.enabled = 1) pl
LEFT JOIN {l10n_update_file} f ON pl.name = f.project AND pl.language = f.language
WHERE f.status IS NULL OR f.status = 1 AND f.last_checked < %d
ORDER BY last_checked
Comments
Comment #1
Sutharsan CreditAttribution: Sutharsan commentedThe inner join in the original query looks wrong to me. Try this:
Comment #2
Sutharsan CreditAttribution: Sutharsan commentedCorrected:
Comment #3
threading_signals CreditAttribution: threading_signals commented#2 code worked for me. No noticeable errors after cron run now.
Comment #4
threading_signals CreditAttribution: threading_signals commentedSeveral cron jobs later and this:
pg_query(): Query failed: ERROR: duplicate key value violates unique constraint "l10n_update_file_pkey" in /var/www/includes/database.pgsql.inc on line 139.
query: INSERT INTO l10n_update_file (project, language, type, filename, fileurl, filepath, timestamp, version, status, last_updated, last_checked, import_date) VALUES ('addthis', 'ko', '', 'addthis-6.x-3.x-dev.ko.po', '', '', 0, '6.x-3.x-dev', 1, 0, 1293685227, 0) in /var/www/includes/common.inc on line 3538.
Comment #5
Sutharsan CreditAttribution: Sutharsan commented@vividgates, this error is not related. See #952102: Duplicate entry for long time.. and search other issues queue for "duplicate key" and you find many similar errors.
Comment #6
threading_signals CreditAttribution: threading_signals commentedThank you. I'll search as suggested.
Comment #7
bartl CreditAttribution: bartl commentedAs far as I understand it, the idea behind the inner join was to produce a carthesian product of the two tables, with no restriction: any project, any language.
Comment #8
grub3 CreditAttribution: grub3 commentedI confirm this is a major problem for PostgreSQL.
Writing:
is ambiguous, it should be replaced with #2.
Writing:
is not ambiguous.
The database optimizer will run the query several times and rewrite it.
So there is no reason why write ambiguous queries.
Comment #9
grub3 CreditAttribution: grub3 commentedThe correct line 112 is:
Comment #10
Gábor HojtsyCommitted #1044652: [PostgreSQL] error in l10n_update_check as the Drupal 7 port of this patch then committed this patch as well. Had some attention issues here, so referenced both commits to this issue. Well, well. It landed at least :)
Comment #12
Liam Morland