This thread is resolved. Here is a description of the problem and solution.
Problem: If you're using WPML for automatic translations and encounter an issue where it shows a lot of pending reviews, but when you attempt to approve them, you receive a 'no data' message, this might be due to a database collation error. Solution: We found that the issue typically arises from a mismatch in database collations. For instance, while your core WordPress and WPML tables might share the same collation, such as 'utf8mb4_general_ci', individual columns within these tables could differ. This discrepancy can cause SQL errors during operations that involve joining columns with different collations. To fix this, you can adjust the collation of the affected columns to match the default. For example, changing the 'post_title' column in the 'wp_posts' table back to 'utf8mb4_general_ci' should resolve the issue. We recommend using a database management tool like Adminer to make these changes. Always ensure to back up your database before making any modifications.
If this solution does not apply to your case, or if it seems outdated, please check the related known issues and confirm that you have the latest versions of themes and plugins installed. If the problem persists, we highly recommend opening a new support ticket at WPML support forum for further assistance.
This is the technical support forum for WPML - the multilingual WordPress plugin.
Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.
Background of the issue:
I am using automatic translations in WPML and after finishing, it shows that I have a lot of pending reviews. However, when I click to check the pending reviews and approve them, it shows 'no data'.
I need to request temporary access (wp-admin and FTP) to your site—preferably to a test site where the problem has been replicated if possible—in order to be of better help. When you log in to leave your next reply, you will find the needed fields below the comment area. The information you will enter is private, meaning only you and I can see and access it.
Thank you for the provided details. However, I am unable to log in to the admin. Once I enter the server protection details and access the admin URL, it gives me a "page not found" error. Could you please check this?
We have identified the cause of the issue you experienced. Your site encountered the following error in the debug.log:
WordPress database error: Illegal mix of collations for operation 'UNION' for query
This error indicates that your database tables have different collations. While the core WordPress tables and the WPML tables have the same collation, individual columns within those tables may have different collations. This discrepancy can lead to SQL errors when running queries that join columns with mismatched collations.
For example, the default collation for the `wp_posts` table is `utf8mb4_general_ci`, which is the same collation used for WPML tables. However, some individual columns have different collations.
To resolve this specific issue, I changed the collation of the `post_title` column in the `wp_posts` table back to the default `utf8mb4_general_ci`. I have completed this fix on your staging site using the Adminer plugin and attached the screenshots for your reference.
I also recommend reviewing all your database tables and their columns to ensure that they all have consistent collations.
Please backup your database before making any changes.
To confirm, so to fix this on live the only table i need to change to utf8mb4_general_ci` is `post_title` column in the `wp_posts` table?
Also, one more thing, i see here now the posts in the pending review page but some of them are throwing errors when I try to approve (others approved fine)
To confirm, so to fix this on live the only table i need to change to utf8mb4_general_ci` is `post_title` column in the `wp_posts` table?
Yes, in order to see the translation qeueue.
Also, one more thing, i see here now the posts in the pending review page but some of them are throwing errors when I try to approve (others approved fine)
It seems there may be an issue with this specific item review. Please navigate to WPML → Translation Management, click on the link to review, and complete the translation for this item review. I was able to do this successfully in your staging environment.