Open
Reported for: WPML Multilingual CMS 4.5.8
Overview of the issue
You may see some of the WPML tables growing too large, specifically:
- icl_translate
- icl_translate_job
- icl_translation_status
This may occur if there is a lot of data in the posts and postmeta tables and you are using a page builder or the Gutenberg block editor.
You may wish to delete the old translation jobs data while keeping the current content intact, however, we do not have any method to delete these jobs. This feature is still under consideration because it carries the risk of data loss, but we have an experimental script that can delete the old jobs data.
Workaround
Please make sure you have a complete database backup. If anything doesn’t seem to be right, there is only one option which is to restore the database. If you agree, then please proceed.
- Download this temporary plugin
- Activate the plugin provided.
- Visit Tools → WPML Delete jobs
- You will see a text area prefilled with job IDs, these are 50 suggested job IDs to delete first. These job IDs are the same that you will see in WPML → Translation Management → Jobs. You can uncheck some job IDs in case you don’t want to touch a few pages or posts.
- Click Delete and wait. Once the content is deleted, you will see a new batch of 50 IDs to delete, and so on until there are no jobs for revision.
- Finally click on Optimize _icl_translation_status table to reduce the size of icl_translation_status table.
- Deactivate and remove the cleaning plugin.
Is there a way to delete more jobs at a time? There are over 15000 jobs in my database, this would take forever to click 50 at a time
Hello!
In the plugin that we shared with you in your support ticket, there is a line that sets the limit:
$suggested_job_ids = $wpdb->get_col("SELECT DISTINCT rid FROM {$wpdb->prefix}icl_translate_job WHERE revision IS NOT NULL AND (completed_date is NOT NULL OR editor != 'ate') ORDER BY job_id ASC LIMIT 50");You can change the limit to higher than 50 there.
Do mind that you might have issues with the server if you increase the limit to a higher than your servers capabilities value.
Hello,
We have a similar experience here on our website. The autoload data from the WPML plugin is huge and the main options and its data values are listed below. Due to this, we are experiencing too much delay on the admin side when updating, changing, creating, or translating a post. Sometimes translation won’t work, and we need to wait for some time for the proper working of the WPML ATE editor.
wpml_strings_need_links_fixed(411084)
ATE_RETURNED_JOBS_QUEUE(83389)
icl_sitepress_settings(17141)
otgs-installer-log(9747)
wpml_shortcode_list(7599)
wpml_language_switcher_template_objects(6803)
How can we reduce the data overload of these options and optimize the database for the smooth running of the website? Please let us know your feedback.
Thanks in advance 🙂
Hello there,
In that case, please open a chat in our assistance. We will provide you the required plugin for following the workaround here provided with the assistance of one of our specialists.
Regards