The effects of alcohol on sustanon 250 leucine for – real weight loss & bodybuilding benefits?
DB collation issue ...
 
Share:
Notifications
Clear all

[Solved] DB collation issue after updating to 7.0.0

8 Posts
3 Users
1 Reactions
1,337 Views
Posts: 9
 xpil
Topic starter
(@xpil)
Active Member
Joined: 4 years ago

Hello again.

 

I can see the following error message in my WPDiscuZ dashboard after 7.0.0 update:

 

[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;

Link to a screenshot of the error:

It does not seem to affect anything but I would like it to be gone.

Hints?

7 Replies
Alina
Posts: 71
Moderator
(@alina)
Member
Joined: 4 years ago

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.

6 Replies
 xpil
(@xpil)
Joined: 4 years ago

Active Member
Posts: 9

@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.

Tom
 Tom
Support
(@tomson)
Joined: 9 years ago

Honorable Member
Posts: 494

@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?

 xpil
(@xpil)
Joined: 4 years ago

Active Member
Posts: 9

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.

Alina
Moderator
(@alina)
Joined: 4 years ago

Member
Posts: 71

@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. 

 xpil
(@xpil)
Joined: 4 years ago

Active Member
Posts: 9

@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.

 xpil
(@xpil)
Joined: 4 years ago

Active Member
Posts: 9

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)
Share: