Drupal 9 CMS: Delete equal customized translation (source language string = target language string)

d9-remove-wrong-equal-translations-l10n.md

Drupal 9 CMS: Delete equal customized translation (source language string = target language string)

If you should encounter the problem that some translations are wrongly translated with the equal source language string (for example in our case there were German translations for "Author" translated with "Author" or "Published" with "Published"), you may use the following snippet to list them.

SELECT s.lid,s.source, t.translation FROM `locales_source` s
INNER JOIN locales_target t
WHERE s.lid=t.lid AND CONVERT(s.source USING utf8) = CONVERT(t.translation USING utf8) 
AND t.customized=1

To finally delete them, you may use something like this, but make a backup before and know what you're doing!

DELETE FROM locales_target WHERE lid IN (SELECT lid FROM (SELECT s.lid FROM locales_source s
INNER JOIN locales_target t
WHERE s.lid=t.lid AND CONVERT(s.source USING utf8) = CONVERT(t.translation USING utf8) 
AND t.customized=1) temp);

Clear & Update translations

Afterwards you may perhaps want to update your translations the following way: http://www.vaerenbergh.com/blog/drupal-8-reimport-all-translation-files

Issue on d.org

https://www.drupal.org/project/drupal/issues/2806009

Module

https://www.drupal.org/project/l10n_tools

d9-remove-wrong-equal-translations-l10n.sh

#!/bin/sh

drush sql:query "DELETE FROM locales_target WHERE lid IN (SELECT lid FROM (SELECT s.lid FROM locales_source s INNER JOIN locales_target t WHERE s.lid=t.lid AND CONVERT(s.source USING utf8) = CONVERT(t.translation USING utf8) AND t.customized=1) temp);"
drush sql:query "DELETE FROM key_value WHERE collection='locale.translation_status';"
drush sql:query "UPDATE locale_file SET timestamp = 0;"
drush sql:query "UPDATE locale_file SET last_checked = 0;"
drush ev "\Drupal::state()->set('locale.translation_last_checked', 0);"
drush locale-check && drush locale-update && drush cr
drush sql:query "UPDATE locales_target SET customized = 0;"