This thread is resolved. Here is a description of the problem and solution.
Problem: The client reported that the WPML database schema was not up-to-date, with missing tables such as wp_icl_background_task and missing fields in wp_icl_translation_status. Solution: We compared the client's database with a fresh WPML install and identified missing tables and fields. We executed SQL commands to create the missing tables and alter existing ones to add the necessary fields. After applying these changes, the database issues related to WPML were resolved. However, we cannot guarantee that other tables from core or other plugins were not affected.
If this solution does not resolve your issue or seems irrelevant due to being outdated or not applicable to your case, we highly recommend 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. If the problem persists, please open a new support ticket.
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:
We have realized that for some actions linked to other plugins, the database schema (icl_) of WPML tables does not seem to be up-to-date. For example, table wp_icl_background_task is missing, and there is at least one field in wp_icl_translation_status missing. Here is version information, we seem to be up-to-date: WPML Multilingual CMS 4.6.13 4.6.13 August 20, 2024 Release notes, String Translation 3.2.14 3.2.14 July 23, 2024 Release notes, Advanced Custom Fields Multilingual 2.1.4.2 2.1.4.2 August 22, 2024 Release notes, WP All Import Multilingual 2.3.0 2.3.0 December 9, 2021 Release notes.
Symptoms:
The database schema (icl_) of WPML tables does not seem to be up-to-date. Table wp_icl_background_task is missing, and there is at least one field in wp_icl_translation_status missing.
Questions:
Is there a script that we can run that could make sure everything is in order?
Languages: English (English )Portuguese (Brazil) (Português )
Timezone: America/Sao_Paulo (GMT-03:00)
Hello there,
Thank you for contacting our support.
If you already have translations on your site, please follow these steps:
1- Make a Full Backup: Before making any changes, ensure that you create a full backup of your site (both files and database). There are several free plugins available on WordPress.org that can help you with this: https://wordpress.org/plugins/tags/backup
2- Add Custom Code: Add the following code to the bottom of the functions.php file located in your theme's folder:
3- Visit Your Site's Homepage: After adding the code, visit your site's homepage at least once to trigger the function that should recreate the missing tables.
4- Remove the Custom Code: Once the tables are recreated, remove the code from the functions.php file to avoid any unintended consequences.
If you have no translations on your site, you can go to WPML > Support > Troubleshooting, scroll down, and select Reset and Deactivate WPML. This will reset WPML and should recreate the necessary database tables.
If the above solutions do not resolve the issue, please let us know, and we will investigate further.
Languages: English (English )Portuguese (Brazil) (Português )
Timezone: America/Sao_Paulo (GMT-03:00)
Hello there,
In that case it might be necessary to take a closer look to see what's wrong with the tables.
I would like to request temporary access (wp-admin and FTP) to your site to take better look at the issue. You will find the needed fields for this below the comment area when you log in to leave your next reply. The information you will enter is private which means only you and I can see and have access to it.
Our Debugging Procedures
I will be checking various settings in the backend to see if the issue can be resolved. Although I won't be making changes that affect the live site, it is still good practice to backup the site before providing us access. In the event that we do need to debug the site further, I will duplicate the site and work in a separate, local development environment to avoid affecting the live site.
- Please make a backup of site files and database before providing us access.
- If you do not see the wp-admin/FTP fields this means your post & website login details will be made PUBLIC. DO NOT post your website details unless you see the required wp-admin/FTP fields. If you do not, please ask me to enable the private box. The private box looks like this: hidden link
Please, let me know if you need any additional details. Have a nice day.
Languages: English (English )Portuguese (Brazil) (Português )
Timezone: America/Sao_Paulo (GMT-03:00)
Hello there,
Thank you for sharing that access.
What I did to make sure the DB is looking fully functional now was to compare it with a fresh install of WPML.
First I detected 3 missing tables, wp_icl_background_task, wp_icl_string_batches and wp_icl_translation_downloads that I created with the following command:
CREATE TABLE `wp_icl_background_task` (
`task_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`task_type` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
`task_status` smallint unsigned NOT NULL DEFAULT '0',
`starting_date` datetime DEFAULT NULL,
`total_count` int unsigned NOT NULL DEFAULT '0',
`completed_count` int unsigned NOT NULL DEFAULT '0',
`completed_ids` text COLLATE utf8mb4_unicode_ci,
`payload` text COLLATE utf8mb4_unicode_ci,
`retry_count` smallint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wp_icl_string_batches` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`string_id` bigint unsigned NOT NULL,
`batch_id` bigint unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wp_icl_translation_downloads` (
`editor_job_id` bigint unsigned NOT NULL,
`download_url` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
`lock_timestamp` int unsigned DEFAULT NULL,
PRIMARY KEY (`editor_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
After that I compared table by table to see the different/missing fields and fixed them with the following batch of queries:
-- Add the missing 'country' column to wp_icl_languages
ALTER TABLE wp_icl_languages
ADD COLUMN country varchar(10) NULL;
-- Modify the 'domain' column in wp_icl_mo_files_domains
ALTER TABLE wp_icl_mo_files_domains
MODIFY COLUMN domain varchar(160) NOT NULL;
-- Add the missing 'edit_timestamp', 'automatic', and 'ate_sync_count' columns to wp_icl_translate_job
ALTER TABLE wp_icl_translate_job
ADD COLUMN edit_timestamp int unsigned NULL,
ADD COLUMN automatic tinyint unsigned NOT NULL DEFAULT 0,
ADD COLUMN ate_sync_count int unsigned NULL DEFAULT 0;
-- Modify the 'batch_id' column type in wp_icl_string_translations
ALTER TABLE wp_icl_string_translations
MODIFY COLUMN batch_id int NOT NULL DEFAULT 0;
-- Add the missing 'uuid', '_prevstate', and 'ate_comm_retry_count' columns to wp_icl_translation_status
ALTER TABLE wp_icl_translation_status
ADD COLUMN uuid varchar(36) NULL,
ADD COLUMN _prevstate longtext NULL,
ADD COLUMN ate_comm_retry_count int unsigned NOT NULL DEFAULT 0;
Finally, I ran a check on each table, looking for corrupted data, non int values for integer fields, missing increments, empty data in required fields and I couldn't find anything, it looks like the issue was related to the structure rather than the data.
Everything seems OK with the DB now regarding WPML, I can't assure other tables from core or other plugins weren't affect, but in theory the warnings you were experiencing with WPML should be fixed by now.
Thank you so much for adding the missing elements. It seems though that there is still one missing field. Is this something you can add or give me the proper ALTER TABLE code?
Thank you!
review_status in the wp_icl_translation_status table:
[Wed Sep 18 20:37:18.435138 2024] [proxy_fcgi:error] [pid 299525:tid 299630] [client 24.37.50.50:0] AH01071: Got error '; PHP message: WordPress database error Unknown column 'ts.review_status' in 'field list' for query SELECT t.trid, tj.job_id, ts.status, ts.review_status, ts.needs_update, t.language_code, SUBSTRING_INDEX(t.element_type, '_', 1) AS element_type_prefix FROM wp_icl_translate_job tj JOIN wp_icl_translation_status ts ON tj.rid = ts.rid JOIN wp_icl_translations t ON ts.translation_id = t.translation_id WHERE t.trid = 164438 made by wp_dashboard, do_meta_boxes, wp_dashboard_quick_press, wp_dashboard_recent_drafts, get_edit_post_link, apply_filters('get_edit_post_link'), WP_Hook->apply_filters, WPML_URL_Filters->get_edit_post_link, apply_filters('wpml_link_to_translation'), WP_Hook->apply_filters, WPML_TM_Page_Builders_Hooks->link_to_translation_filter, WPML_TM_Page_Builders->link_to_translation_filter, WPML_TM_Translation_Status->filter_translation_status, WPML\\LIB\\WP\\Cache::WPML\\LIB\\WP\\{closure}, call_user_func_array, WPML_TM_Translation_Status->{closure}...; PHP message: WordPress database error Unknown column 's.review_status' in 'field list' for query SELECT j.rid, j.translator_id, t.translation_id, s.batch_id, j.translated, j.manager_id, s.status, s.review_status, s.needs_update, s.translation_service, s.uuid, s.ate_comm_retry_count, t.trid, t.language_code, t.source_language_code, iclt.field_data AS original_doc_id, iclt.job_id, ito.element_type AS original_post_type, j.title, j.deadline_date, j.completed_date, j.editor, j.editor_job_id, j.automatic, SUBSTRING_INDEX(ito.element_type, '_', 1 ) as element_type_prefix FROM wp_icl_translate_job j JOIN wp_icl_translation_status s ON j.rid = s.rid JOIN wp_icl_translations t ON s.translation_id = t.translation_id JOIN wp_icl_translate iclt ON iclt.job_id = j.job_id JOIN wp_icl_trans...; PHP message: WordPress database error Unknown column 'ts.review_status' in 'field list' for query SELECT t.trid, tj.job_id, ts.status, ts.review_status, ts.needs_update, t.language_code, SUBSTRING_INDEX(t.element_type, '_', 1) AS element_type_prefix FROM wp_icl_translate_job tj JOIN wp_icl_translation_status ts ON tj.rid = ts.rid JOIN wp_icl_translations t ON ts.translation_id = t.translation_id WHERE t.trid = 164026 made by wp_dashboard, do_meta_boxes, wp_dashboard_quick_press, wp_dashboard_recent_drafts, get_edit_post_link, apply_filters('get_edit_post_link'), WP_Hook->apply_filters, WPML_URL_Filters->get_edit_post_link, apply_filters('wpml_link_to_translation'), WP_Hook->apply_filters, WPML_TM_Translation_Status_Display->filter_status_link, WPML_TM_Translation_Status_Display->is_in_basket, WPML_Post_Status->get_status, apply_filters('wpml_translation_status'), WP_Hook->apply_filters, WPML_TM_Translation_Status->filter_translation_status, WPML\\LI...'
