[Resolved] Posts listings in the backend include a mix of languages

It's weird, because the post you edited is 702582, but hidden link is post 702583.

However, hidden link is post 66518 ! (at least that's what I see if I hover over "Edit activity" the WordPress toolbar)

Then I went to hidden link and that's where your edited header is. That's post 702582. This URL should not exist but I have seen a similar duplicate before: /rigi-2/ (a long time ago, before the current problem). I was never able to delete it.

I did another test and went to hidden link to change the header "Corvatsch: Fragen und Antworten WPML".

While I was publishing that change, I noticed that the URL of the post changed from hidden link to hidden link ! So WPML created a duplicate again! I guess the problem we are researching has been present for a long time and it's also what caused the Rigi duplicate a long time ago.

Something inside WPML is triggering it to mix up the language versions, and everything including editing a post is getting messy. Perhaps the database shows what happened?

It's strange that this wasn't a practical problem until after the last updates. Until then, the backend only showed English posts, and only German posts when switching language, and the frontend was fine too.

1. We never manually change URLs to edit posts. That was only for debugging the issue, trying to understand what's happening.

2. We very rarely make changes to URLs. If we do it, we go into the WPML Advanced Translation Editor and change the German slug there. That has happened maybe 3 times in 6 months. It worked fine.

It's not our workflow. We always carefully edit via the Advanced Translation Editor. There was no issue until we updated the plugins last week. Our workflow has not changed. There's a bug inside WPML that is causing this, because it even happens with all other plugins disabled.

I have no steps to reproduce this. It's a problem that is permanent, it doesn't come and go. All I can think of is providing you with an old backup of the site (before the updates and this issue). That should not have this issue, although I don't know what happens if you restore it, maybe that triggers it too. If you then update all plugins, WordPress, and the theme, it's likely that the problem will occur.

Can you please urgently get a developer to look into this? A developer should be able to see what's actually going wrong on the code level.

We can't edit any posts now, to prevent making the issue even bigger. It's a big problem on our end.

Note that we can't restore a backup because this is a dynamic site. New user accounts and forum posts have been created. Apart from that, we need to actually fix this because it could happen again if we don't.

Hi Bruno,

Thank you. I adjusted the query to this to prevent it from returning post types that aren't the ones in the frontend:

set sql_mode = '';
SELECT p.post_title, p.post_type FROM `wp_posts` p
JOIN `wp_icl_translations` as t ON p.ID = t.element_id AND t.element_type = CONCAT('post_', p.post_type)
WHERE t.source_language_code IS NULL AND p.post_type <> 'attachment' AND p.post_type <> 'wp_block' AND p.post_type <> 'nav_menu_item' AND p.post_type <> 'mb-post-type' AND p.post_type <> 'mb-taxonomy' AND p.post_type <> 'wp_navigation' AND p.post_type <> 'revision'
GROUP BY p.post_title
HAVING COUNT(p.post_title) > 1;

Both your query and the adjusted one don't return all the posts that I had identified manually. That's probably because our titles are very short (e.g. just a town name) and duplicates are expected there (because town names don't change with the language mostly). We use the custom field content_long_title as our main post title. I tried to use that instead but didn't get the query to work.

However, I did try step 2 on only the Activity post type. I went to the list of posts in the WordPress admin with WPML set to English. I deleted 12 German posts from there. Now, the number of English and German posts is the same, and everything looks fine in the frontend and backend, for this post type.

So, I am tempted to go ahead and do this on the live site for all post types.

Nevertheless, it would be handy if the query worked, to be sure I won't miss anything.

As for the posts being duplicated a long time ago: that's an interesting finding. That means that those duplicates were in the database all along, but only started showing up in the frontend and backend lists recently for some reason. Perhaps they were in the database but not marked as English yet, because that's what's causing the German duplicates to show up along with English posts.

The only possible cause I can think of, is that an editor perhaps had multiple browser tabs open, and switched the WPML language to German in one of the tabs. And then went to work in the tab still showing English while that actual WPML language was switched to German.

I will certainly let you know if I can reproduce it or if it spontaneously happens again.

For now, I will go ahead and delete the duplicates in the live site, if that's ok?

If you have a query that works for the content_long_title field, please let me know.

Yes, correct. But the query only returns 1 record so it doesn't work unfortunately.

I will try and do it based on my manual findings. It's not that many posts so hopefully I won't miss any.