This thread is resolved. Here is a description of the problem and solution.
Problem: You are experiencing an issue where the translation jobs queue is empty despite showing 20 pages, and you receive emails about 'Overdue translation jobs report' but see 'no data' in the backend. The translations queue is also empty. Solution: The problem is due to a collation mismatch between the default WordPress tables and the WPML tables. To resolve this, follow these steps: 1. Ensure you have a full database backup before making changes. 2. Check the current collation settings of the relevant tables using these commands:
SHOW FULL COLUMNS FROM wp_icl_languages;
SHOW FULL COLUMNS FROM wp_posts;
SHOW FULL COLUMNS FROM wp_icl_string_packages;
SHOW FULL COLUMNS FROM wp_icl_translation_batches;
3. Update the collation of all WPML tables to match the WordPress default (`utf8mb4_unicode_520_ci`) by executing the following `ALTER TABLE` commands:
ALTER TABLE `wp_icl_background_task` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
... 4. Optionally, generate a dynamic list of `ALTER TABLE` commands for all WPML tables using this SQL query:
SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;') FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name LIKE '%_icl_%';
5. Apply these changes during off-peak hours to minimize impact, focusing on key tables like `wp_icl_languages`, `wp_icl_string_packages`, and `wp_icl_translation_batches`.
If this solution does not resolve your issue or seems irrelevant due to being outdated or not applicable to your case, we recommend opening a new support ticket. We also highly advise checking related known issues at https://wpml.org/known-issues/, verifying the version of the permanent fix, and confirming that you have installed the latest versions of themes and plugins. For further assistance, please visit our support forum at WPML Support Forum.
This is the technical support forum for WPML - the multilingual WordPress plugin.
Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.
Background of the issue:
I am trying to add posts/pages to translation in Translation Management -> Translation Dashboard. After doing this, I go to 'Translation jobs', but the translation jobs queue is empty. It shows 20 pages, but no data is visible. I also receive emails with 'Overdue translation jobs report', but the backend shows 'no data'. Additionally, the Translations queue is empty.
Symptoms:
The translation jobs queue is empty despite showing 20 pages. Emails are sent with 'Overdue translation jobs report', but the backend displays 'no data'. The Translations queue is also empty.
Questions:
Why is the translation jobs queue empty despite showing 20 pages?
Why am I receiving 'Overdue translation jobs report' emails when the backend shows 'no data'?
Languages: English (English )German (Deutsch )French (Français )
Timezone: Europe/Zagreb (GMT+02:00)
This has been escalated to our 2nd tier team team and may take some debugging time, I'll get back to you as soon as I have any news or questions for you.
Languages: English (English )German (Deutsch )French (Français )
Timezone: Europe/Zagreb (GMT+02:00)
The issue is caused by a collation mismatch in your database, which results in the following error:
WordPress database error Illegal mix of collations for operation 'UNION' for query (
SELECT
translation_status.rid AS id, 'post' AS type, ...
This occurs because the default WordPress tables use `utf8mb4_unicode_520_ci`, while WPML tables are currently using `utf8mb4_unicode_ci`. This difference causes conflicts when executing queries that join data from both sets of tables. The solution is to align the collation of WPML tables with the WordPress default.
Make sure a full database backup is taken before proceeding.
1. Check the current collation settings of the relevant tables:
SHOW FULL COLUMNS FROM wp_icl_languages;
SHOW FULL COLUMNS FROM wp_posts;
SHOW FULL COLUMNS FROM wp_icl_string_packages;
SHOW FULL COLUMNS FROM wp_icl_translation_batches;
2. Execute the following `ALTER TABLE` commands to update the collation of all WPML tables to `utf8mb4_unicode_520_ci`:
ALTER TABLE `wp_icl_background_task` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_content_status` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_core_status` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_flags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_languages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_languages_translations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_links_post_to_post` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_links_post_to_term` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_locale_map` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_message_status` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_mo_files_domains` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_node` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_reminders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_string_batches` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_string_packages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_string_positions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_string_status` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_string_translations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_strings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translate` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translate_job` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translation_batches` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translation_downloads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translation_status` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_icl_translations_copy` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
3. If needed, you can generate this list dynamically using the following SQL:
SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;')
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name LIKE '%_icl_%';
4. It is recommended to apply these changes during off-peak hours as the process is resource-intensive. At a minimum, apply the change to:
