I am writing to you because I have an issue with wrong URLs showing in my RankMath sitemap.
Our website: hidden link is in French, and we've been translating it into Spanish and English. For now, we've mainly focused on translating French content into Spanish.
For many posts (but not all of them) we have double Spanish entries in the sitemap: a correct one with the /es/ slug and a wrong one without the /es/ slug (but with the rest of the URL translated), which is causing many 404 URLS appearing in our sitemap.
For example:
- French version (OK): hidden link
- Spanish version (OK): hidden link
- Spanish version (NOT OK): hidden link
And we have many more (80+):
- OK: hidden link
- NOT OK: hidden link
- OK: hidden link
- NOT OK: hidden link
- OK: hidden link
- NOT OK: hidden link
Further information:
When I check the code of the pages in French and Spanish, the HREFLANG tags are OK. So these wrong URLs are ONLY showing in the sitemaps. Also, I cannot find them on the frontend, even when I search for posts in Spanish directly.
This is weird because this issue is happening on ≈80 translated pages, whereas we've already translated 100s of pages into Spanish.
I've already done some research and I've seen this is related to wrong entries created in the database. What should I do?
This is really annoying for us, as it is creating many 404 errors and we heavily rely on organic traffic.
Here you'll find our main sitemap:
- hidden link
You'll see the wrong URLs on our sub-sitemaps:
- hidden link
- hidden link
- hidden link
...
Thank you for providing the details. Please consider the following:
1. What can cause duplicated translations in WPML?
There can be several reasons, like, a failed translation job due to communication errors, ghost entries, internal behavior of the WordPress system where it creates revisions and so on. This is normal, and it may happen to any WP system with redundant data. Occasionally, this may also happen due to trashed content, which is deleted but stays in the trash. It is important to delete such content permanently. Please let me know if you have items in the trash.
2. How can I easily remove them without having to go over the entire DB?
WPML provides some handy (advanced) operations for these, from syncing jobs with ATE to removing ghost entries etc. Please go to WPML → Support → Troubleshooting page and find the relevant options. Each option has some description underneath. You can try the suitable ones. After applying each operation, wait for it to finish and check for your issue before applying another one. So you can know what resolved the problem.
Please remember to take a full backup of your website and database, before going for troubleshooting operations.
Please notify me if any of these help resolve the issue, or I'll try my best to further assist you on the matter.
It's probable that these wrong URL entries were created due to the reasons I stated above. I found a client with a similar issue, and he manually deleted the entries as a workaround. Manually manipulating the database can lead to critical errors and loss of data, please be aware that it's essential to have a backup. You can also try to manually delete the wrong URL entries from your database. If this situation happens again, please get in touch with us, so we can find a permanent solution to this issue.
If you are not experienced or comfortable manipulating the DB, please let me know so we can advise you further.
You can delete the entries by running a simple query on your database.
These URLs are likely stored in the in wp_posts table, so you'll need to frame your query based on where these URLs are located. You can access the database with phpMyAdmin. You can usually access phpMyAdmin through your web hosting control panel (like cPanel, Plesk, etc.). After accessing your database, please do the following:
1)Back up your database: Before executing any DELETE query, ensure you have a complete backup of your database and of your website.
2) Once in phpMyAdmin, you'll see a list of all the tables in your WordPress database. Find the tab labeled 'SQL' or a similar option where you can enter SQL commands.
3) Verify the table and field: Make sure you are targeting the correct table (wp_posts or wp_postmeta) and field (guid or meta_value). Run a SELECT query first: Before executing the DELETE command, you might want to run a SELECT query with the same conditions to ensure you are deleting the correct entries. The following code contains some 404-error-URLs you sent previously, please add all the other 404-error-URLs and run the command.
SELECT FROM wp_postmeta
WHERE guid IN (
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>'
);
4) Now run the DELETE query, also adding all the 404-error-URLs:
DELETE FROM wp_postmeta
WHERE guid IN (
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>',
'<em><u>hidden link</u></em>'
);
After running the query, it's advisable to check your website to ensure everything is functioning as expected. As I said, this should solve your issue for the moment. By avoiding the situations that cause these wrong entry cases, you shouldn't have any issues in the future.
Please let me know if you need any further assistance.
I don't really know about DB management, but I can easily handle these kind of tasks as long as I have clear explanations.
I did a whole backup of the website and ran your "SELECT" command. However, I faced an error: 1064 syntax error.
I used the plugin "search and replace" to see where the wrong URLs were located, and I found out that they appear in these DB tables :
- wp_options
- wp_posts
However, I don't have enough knowledge to know why there's a 1064 syntax error. You'll find a screenshot.
Thank you for sharing your results. As for the wrong entries being found/deleted, it really shouldn't happen, as we are using the whole URL, which should match only one result. This might be the sign of a more profound issue.
Usually, in these situations, I would already escalate your issue to our development team, but I want you to try one last solution that might work before doing that. I found an issue that is very similar to yours, please try to follow the steps described here:
Share your results. If it doesn't solve the problem, I'll enable the private box and ask you for access to the staging version you mentioned, so our developers can take a more in-depth look into your website.
I'll create a new staging environment for you with the latest copy of the website, this way you can have an exact overview of what's happening without the tweaks I recently did on the current staging.
Thank you for sharing. I'd like to have access to a staging version of your website, as you mentioned.
You will find the needed fields for this below the comment area when you log in to leave your next reply. The information you will enter is private which means only you and I can see and have access to it.
Maybe I'll need to replicate your site locally. For this, I’ll need to temporarily install a plugin called “Duplicator” or "All in One WP Migration" on your site. This will allow me to create a copy of your site and your content. Once the problem is resolved I will delete the local site. Let me know if this is ok with you.
IMPORTANT
- Please make a backup of site files and database before providing us access.
- If you do not see the wp-admin/FTP fields this means your post & website login details will be made PUBLIC. DO NOT post your website details unless you see the required wp-admin/FTP fields. If you do not, please ask me to enable the private box. The private box looks like this:
I escalated your issue to our development team, which will look for a permanent solution to your issue. That usually takes a few days, so I'll keep you posted. Thank you for your cooperation.
