[Closed] Media Translation Setup will not finish

Hello,

Thanks for the heads-up.

Best Regards,
Otto

Hello Andraz,

I got the files and managed to deploy your site in my local development environment.

I am still checking the issue. I'll get back to you as soon as possible.

Best Regards,
Otto

Hello Andraz,

I tried to clean the duplicated images safely, but I couldn't. I escalated the issue to our second tier support. I'll get back to you as soon as I have news about the issue.

Best Regards,
Otto

Hi,

This is christopher checking with this issue. We have a question from the second-tier support.

There is a huge improvement on how Media Translation works on WPML version 4.8.2.

I wonder if you tested the same thing with the new version?

- IMPORTANT STEP! Create a backup of your website. Or better approach will be to test this on a copy/staging version of the website to avoid any disruption of a live website.
- Go to "WordPress Dashboard > Plugins > Add new > Commercial (tab)".
- Click the "Check for Updates" button.
- Update WPML and its addons there.

For more information:
https://wpml.org/faq/install-wpml/#automated-updates

Thanks.

Hello,

Our second tier crafted a query to delete the duplicated attachments. This is a risky operation, so please read carefully 🙂

The query creates a temporary table, populates it with IDs from the database, and should remove the higher ID (which are the duplicated images). This will remove all translated images, but I think this is your intention at this stage. In any case, the image "translation" can be done again after this problem is fixed.

This can not be undone, so I suggest you do it in a staging environment and check that everything works as expected before doing it in production. And always, ❌ make a full website backup before proceeding ❌

I tried it locally, and it took some time to run, so you may need to overcome server limitations even with this optimized query. Currently, there are around 400k attachment rows in wp_posts and after running the query it goes down to 19k. Do these numbers make sense to you?

Finally, the query uses wp_ as table prefix, you'll need to modify it if your DB uses another one.

You may want to run it first until step 4 (uncomment the statements) to see how many items will be deleted before actually delete them.

This is the query:

-- 1) Groups that have duplicate attachments by (file path, title)
CREATE TEMPORARY TABLE dup_groups AS
SELECT pm.meta_value AS file, p.post_title AS title
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_wp_attached_file'
  AND p.post_type = 'attachment'
  AND p.post_mime_type LIKE 'image/%'
GROUP BY pm.meta_value, p.post_title
HAVING COUNT(*) > 1;

-- 2) For each duplicate group, keep the smallest ID among items that have a parent
--    (matches your original logic with p.post_parent != 0)
CREATE TEMPORARY TABLE keepers AS
SELECT MIN(p.ID) AS keep_id, pm.meta_value AS file, p.post_title AS title
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_wp_attached_file'
  AND p.post_type = 'attachment'
  AND p.post_mime_type LIKE 'image/%'
  AND p.post_parent != 0
GROUP BY pm.meta_value, p.post_title;

-- 3) Collect the IDs to delete: any attachment in a duplicate group
--    that is not the chosen keeper for that group
CREATE TEMPORARY TABLE temp_ids AS
SELECT p.ID
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
JOIN dup_groups d
  ON d.file = pm.meta_value AND d.title = p.post_title
LEFT JOIN keepers k
  ON k.file = pm.meta_value AND k.title = p.post_title AND k.keep_id = p.ID
WHERE pm.meta_key = '_wp_attached_file'
  AND p.post_type = 'attachment'
  AND p.post_mime_type LIKE 'image/%'
  AND k.keep_id IS NULL;

-- (Optional) sanity check before deleting:
-- SELECT COUNT(*) FROM temp_ids;  -- how many will be deleted?
-- SELECT * FROM temp_ids LIMIT 50;

-- 4) Delete posts, then their postmeta
DELETE FROM wp_posts
WHERE ID IN (SELECT id FROM temp_ids);

DELETE FROM wp_postmeta
WHERE post_id IN (SELECT id FROM temp_ids);

-- 5) Clean up
DROP TEMPORARY TABLE IF EXISTS temp_ids;
DROP TEMPORARY TABLE IF EXISTS keepers;
DROP TEMPORARY TABLE IF EXISTS dup_groups;

Best Regards,
Otto

Hello Andraz,

I understand your concern about reactivating WPML. The latest version should handle it better. But, in any case, this should be done in a staging or development environment and with a reliable backup.

Let me know how it goes, please.

Best Regards,
Otto