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/
Latest posts by rajeshkumar (see all)
- How to get Blackduck Trial version? - December 3, 2023
- PHP ionCube Error: cannot be decoded by this version of the ionCube Loader - December 2, 2023
- Cloudbees CD/RO Error: ectool - November 24, 2023