[Solved] DB collation issue after updating to 7.0.0

Hi @xpil,

Please navigate to the Dashboard > Comments > wpDiscuz > Tolls > Fix Tables  admin page and click on "Fix Tables" button and check again. Everything should work fine.

@alina thanks for quick response.

I clicked the "Fix Tables" button as per you advice and it gave me the green "Done" message but the issue still remains.

Also, unlike other "Fix*" buttons in the Tools section, this one becomes active after refreshing the page.

@xpil,

The problem should be fixed. Please check it again and copy and paste the error if it still exists. Are you sure the error is not changed?

Hi @tomson. The error has not disappeared despite me clicking the "Fix Tables" button for the 3rd time. It states:

[Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation 'like']
SELECT
`c`.`comment_author_email`, `c`.`comment_author`,
COUNT(`c`.`comment_ID`) AS `count`, IFNULL(`s`.`count`, 0) AS `scount`,
IFNULL(`fi`.`count`, 0) AS `ficount`, IFNULL(`fw`.`count`, 0) AS
`fwcount`, MAX(`c`.`comment_date_gmt`) AS `last_date` FROM `wp_comments`
AS `c` LEFT JOIN (SELECT `email`, COUNT(`email`) AS `count` FROM
`wp_wc_comments_subscription` WHERE `confirm` = 1 GROUP BY `email`) AS
`s` ON `s`.`email` LIKE `c`.`comment_author_email` LEFT JOIN (SELECT
`follower_email`, COUNT(`follower_email`) AS `count` FROM
`wp_wc_follow_users` WHERE `confirm` = 1 GROUP BY `follower_email`) AS
`fi` ON `fi`.`follower_email` LIKE `c`.`comment_author_email` LEFT JOIN
(SELECT `user_email`, COUNT(`user_email`) AS `count` FROM
`wp_wc_follow_users` WHERE `confirm` = 1 GROUP BY `user_email`) AS `fw`
ON `fw`.`user_email` LIKE `c`.`comment_author_email` WHERE
`c`.`comment_approved` = '1' GROUP BY `c`.`comment_author_email`,
`c`.`comment_author` ORDER BY `count` DESC LIMIT 7 OFFSET 0;

As you can see below, the two fields have different collation settings indeed:

so the message reflects what's in the tables.

I would say either enforce collation on the JOIN operator or update one of the tables (the latter one preferably) so that collations match.

@xpil,

The wpDiscuz get the collection from WordPress. It seems you've cloned it from somewhere else, that causes the issue. You just need to change the wpDiscuz table collection to your table collection and check again. 

@alina I do not really understand your last response. I did not clone anything from anywhere, been using this database since 2011.

Anyway, what would be the quickest way for me to fix the issue?

I am not a MySQL expert but I understand databases in general so if you could just nudge me in the right direction, I'd probably manage fixing this on my own.

Thanks in advance.

OK I fixed the issue. Thanks once again for all the hints.

For future readers, the fix was:

alter table wp_wc_comments_subscription CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(then the same for the wp_wc_follow_users table)