I have received your clear instructions for removing excess media entries from the database.
I note that there are 12 languages enabled on the DEV site.
For each language, I have attempted to remove all media as advised by WPML. However, the media page claims that there are 44,790 media entries in the database for each of the 12 languages. Clearly it isn’t practical to delete these in batches of 100. Please could you advise a more effective way of deleting these database entries in bulk? Can it be done through direct access to the database using phpmyAdmin?
Thanks for this.
I have followed the fix you published.
I ran the following code against the database:
SELECT element_id
FROM wp_icl_translations
WHERE
element_type = 'post_attachment'
AND
source_language_code IS NOT NULL
The result is a table of 537,516 entries.
The fix suggests I run the following code against that table:
$wpdb->query($wpdb->prepare("DELETE FROM $wpdb->posts WHERE $wpdb->posts.ID = %d", $attachment->element_id));$wpdb->query($wpdb->prepare("DELETE FROM $wpdb->postmeta WHERE $wpdb->postmeta.post_id = %d", $attachment->element_id));
Please could you clarify how to do this? Do I add a snippet to the website or is this some sort of offline script to prepare the data?
This is more of a custom request that you can try but only with a backup done first.
The recommended way is to use the WordPress backend for removing the media content, as each media element that you upload to WordPress is connected to various tables, and running an incomplete query could lead to unexpected DB entries that were missing or forgotten to get removed while running the queries.
Very many thanks, Mihai,
I am unable to try this until probably Tuesday, so I'll let y ou know how it goes.
This is clearly a capability that should be shipped with WPML, but I accept that we are having to use a workaround instead. I shall be careful!
Kind regards,
Phil.
Hi Mihai, sorry for the delay. I was away for longer than intended and was unable to pursue the solution you presented, until now.
I have now been through your response. I have addressed each of your points below:
1. Please take a look here: hidden link
This is an interesting introduction to using the $wpdb class to interact with the WordPress database. The article will be very useful to programmers interested in writing queries for a WordPress database. It does not address the problem we are having with WPML.
This article proposes a solution by Carlos Rojas which isn't accepted by the customer, who instead has to develop his own solution. Sadly the customer's solution isn't provided.
3. "This is more of a custom request."
Actually no. It's a fairly standard request to disable something WPML has done. What is surprising is that it seems to require a custom solution to do it. After all these years, is there still no workable, published solution to undo WPML media library bloat?
Let's try this (I just tested it on a local environment and it worked fine).
NOTE: It would be best if you can test it on a staging environment or at least make sure you have a complete database and files backup of the site before proceeding
1. Add this code to the currently active theme's functions.php file:
add_action('init', function() {
if (isset($_GET['wpml_media_cleanup'])) {
set_time_limit(0);
global $wpdb;
$ids_to_fix = $wpdb->get_results("SELECT element_id FROM {$wpdb->prefix}icl_translations WHERE element_type = 'post_attachment' AND source_language_code IS NOT NULL", ARRAY_A);
if (!empty($ids_to_fix) && is_array($ids_to_fix)) {
foreach ($ids_to_fix as $row_data) {
if (!empty($row_data['element_id'])) {
$wpdb->query($wpdb->prepare("DELETE FROM $wpdb->posts WHERE $wpdb->posts.ID = %d", $row_data['element_id']));
$wpdb->query($wpdb->prepare("DELETE FROM $wpdb->postmeta WHERE $wpdb->postmeta.post_id = %d", $row_data['element_id']));
}
}
wp_die('Done!');
} else {
wp_die('No media translation found!');
}
}
});
2. Visit the site URL with wpml_media_cleanup in query string (e.g. hidden link)
Thanks for sharing this solution, Mihai. I followed the steps you provided but at step 2., it crashes the site: "There has been a critical error on this website.".
Please could you look again at your code and see if you can produce a working version?
Very many thanks,
Phil.
