Background of the issue:
I am trying to manage our database size, which is growing too large due to the icl_translate (4.3 GB) and icl_translation_status (3.2 GB) tables. We suspect there is an issue with how WPML retains data. Link to a page where the issue can be seen: hidden link
Symptoms:
Our database is growing too large because of the icl_translate and icl_translation_status tables.
Questions:
Why are the icl_translate and icl_translation_status tables growing so large?
Is there an issue with how WPML retains data?
Welcome to the WPML support forum. Before passing this thread to my colleague, I would like to share some suggestions and possible solutions for the issues you mentioned.
Normally, the sizes of these two tables depend on the size of the posts and postmeta tables, as well as the number of languages you have.
So please try the WPML Troubleshoot steps and the workaround suggested in the following errata after a full site backup {mandatory} and check if is there any difference in the result.
Languages: English (English )Spanish (Español )German (Deutsch )
Timezone: America/Lima (GMT-05:00)
Hi,
I would like to offer to take a closer look and request temporary access (wp-admin and FTP) to the site to investigate the issue further.
The required fields are below the comment section when you log in to leave the next reply. The information you provide is private, meaning only you and I can see and access it.
IMPORTANT
Please be sure to back up the site and database before granting us access.
If you can't see the "wp-admin / FTP" fields, your post and site login details will be set to "PUBLIC". DO NOT publish the data unless you see the required wp-admin / FTP fields.
The private reply form looks like this: hidden link
The next time you reply, click on "I still need assistance".
Video: hidden link
Please note that we are obliged to request this information individually on each ticket. We are not allowed to access any credentials that were not specifically submitted on this ticket in the private response form.
Languages: English (English )Spanish (Español )German (Deutsch )
Timezone: America/Lima (GMT-05:00)
The provided login credentials appear not to be correct. Please verify the provided information and leave me a comment on this ticket once access is granted.
I've double checked the provided credentials, they are correct and you should be able to login.
Please note that you should add the following line into your hosts file:
35.214.200.65 weply.chat hidden link weply.se weply.dk weply.nl
so that your local traffic get redirected to our test/staging WP instance.
If you need access to the FTP server you should provide me with an IP so that I put it on the whitelist, this is a requirement from our hosting provider.
Please let me know if it worked for you after you inserted the server address into your hosts file.
Languages: English (English )Spanish (Español )German (Deutsch )
Timezone: America/Lima (GMT-05:00)
Regarding wp_icl_translate and wp_translation_status:
Most entries on this table are coming from Gutenberg and Beaver Builder which both appear to require many entries.
I can not suggest manipulating this table as it could damage the connections of contents between languages. According to our clean-up plugin, there is nothing more that could get deleted without causing risking data loss.
I am consulting the second-tier support about this and will get back to you once I receive feedback.
---
According to WPML > Support > Troubleshooting > Clear invalid strings there are 2,009 string packages that are no longer valid. I deleted those strings now.
I then updated WPML to the latest version and used the new option "Check for string issues" which found another 1333 string entries, that I deleted.
Anyhow, taking those tasks only helped to clean up the string tables and not the translation tables.
Also, take note that this site used WordPress 6.4.3 which has been outdated for a
long time. I updated WordPress on the staging site.
Many other plugins and the theme have not been updated for a long time either.
---
Could you maybe provide more background about the site's history? Could it be that the site was migrated from Gutenberg to Beaver Builder? If so, when did this migration occur?
Also, take note that I am not able to take a closer look into the database as I am getting rejected by the host when trying to take action. I am trying to obtain a site package for further investigation.
Languages: English (English )Spanish (Español )German (Deutsch )
Timezone: America/Lima (GMT-05:00)
I am currently trying to set up a local test site on which I can investigate the database.
The second-tier support asked me to try to run the following query to find out which entries have a huge size.
SELECT
tid,
job_id,
content_id,
CHAR_LENGTH(field_data) + CHAR_LENGTH(field_data_translated) AS total_size
FROM wp_icl_translate
ORDER BY total_size DESC
LIMIT 50;
He suspects that the site has big posts and recalled an issue with figma/figmeta being inserted in posts that boosted the size to a significant amount. These elements should not be normally translated and should probably be removed from the post or the post should be just duplicated and edited with the native WordPress Editor.
I will need to take a closer look at the data to be able to make a conclusion. Sadly on your website, this does not work for me. The host is rejecting my access every couple minutes.
Languages: English (English )Spanish (Español )German (Deutsch )
Timezone: America/Lima (GMT-05:00)
The biggest content on your site is the Cookie Policy page. It looks like this page was earlier created in Gutenberg and then Beaver Builder was added to the site.
The issue:
When translating the page you did not only translate the content, but also all the settings of the page builder. See screenshot. This might be why there are so many entries on the database.
This happens because someone had set the following system fields at WPML > Settings > Custom Field Translation > Show system fields to "Translate":
_fl_builder_data should be set to "Don't translate" and the other two fields to "Copy".
Now, if I open the translations inside the translation editor, we only see the title, content, and important custom fields, but no longer all the settings.
In this example, the size of the entry for this page was lowered by the character length from 80k to 60k. I further revised the saved data and there is nothing uncommon in the post content or its translation.
It is simply that for every post or page, we save on the translation table to content, and the translation in base64, plus we need to create string packages for WPML String Translation to make them translatable and this duplicates everything. This means if the original content has 60k characters, then this will be x4 on the WPML table.
The issue that remains is, that you would still need to manually clean up the old jobs from the translation tables after you updated all the translations.
You can do this at Tools > WPML Delete Jobs while the plugin is installed.
Our developers are still looking into this issue and will be working on a fix in an upcoming version of WPML.
