Error

WordPress database error: [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;

Solution

DELETE comments table and follow this

And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work. This is the default sql_mode in mysql new versions.

You can set sql_mode globally as root by command:

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';


create table wp_comments
(
    comment_ID           bigint unsigned auto_increment
        primary key,
    comment_post_ID      bigint unsigned default 0                     not null,
    comment_author       tinytext                                      not null,
    comment_author_email varchar(100)    default ''                    not null,
    comment_author_url   varchar(200)    default ''                    not null,
    comment_author_IP    varchar(100)    default ''                    not null,
    comment_date         datetime        default '0000-00-00 00:00:00' not null,
    comment_date_gmt     datetime        default '0000-00-00 00:00:00' not null,
    comment_content      text                                          not null,
    comment_karma        int             default 0                     not null,
    comment_approved     varchar(20)     default '1'                   not null,
    comment_agent        varchar(255)    default ''                    not null,
    comment_type         varchar(20)     default 'comment'             not null,
    comment_parent       bigint unsigned default 0                     not null,
    user_id              bigint unsigned default 0                     not null
)
    engine = InnoDB
    collate = utf8mb4_unicode_ci;

create index comment_approved_date_gmt
    on wp_comments (comment_approved, comment_date_gmt);

create index comment_author_email
    on wp_comments (comment_author_email(10));

create index comment_date_gmt
    on wp_comments (comment_date_gmt);

create index comment_parent
    on wp_comments (comment_parent);

create index comment_post_ID
    on wp_comments (comment_post_ID);

Reference

• https://stackoverflow.com/questions/62663791/my-sql-said-1067-invalid-default-value-for-user-registered
• https://wordpress.org/support/topic/wordpress-database-error-illegal-mix-of-collations-9/

• Author
• Recent Posts

rajeshkumar

Latest posts by rajeshkumar (see all)

• Best AI Pentesting Tools for Product Teams That Ship Weekly - August 3, 2026
• Bike Rental Management Software: Manage Bike and Scooter Rentals for Free with MotoShare - July 17, 2026
• How SCMGalaxy OS Helps CTOs Assess Engineering Maturity from Code to Production - July 2, 2026