‘❤’ in Comment Generates DB Error

[HisKingdomProphecy]

I got the following error message notice when a post comment included the ‘’ special character.
When I approved the comment, there was no further error message generated.
I have not seen this message before – but then again, we rarely get special character in comments.
I am using WordPress 5.1.1. The WP-config: ‘define(‘DB_CHARSET’, ‘utf8′);’
Can someone please advise how to fix this?

Regards and thanks,
Angus

Error message:
* [Thu Mar 21 02:17:57.574584 2019] [php7:notice] [pid 24789] [client 68.40.22.245:41759] WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation ‘=’ for query SELECT comment_ID FROM xxxcomments WHERE comment_post_ID = 134978 AND comment_parent = ‘0’ AND comment_approved != ‘trash’ AND ( comment_author = ‘Rhonda Lott’ AND comment_author_email = ‘xxx@…’ ) AND comment_content = ‘Amen And Amen! Thank you Rabbi!\xe2\x9d\xa4\xf0\x9f\x94\xa5\xf0\x9f\x92\xaf\xf0\x9f\x99\x8c’ LIMIT 1 made by wp_handle_comment_submission, wp_new_comment, wp_allow_comment, referer: ​https://www.hiskingdomprophecy.com/its-time-to-unearth-the-truth/ *

[SergeyBiryukov]

Hi @HisKingdomProphecy, welcome to WordPress Trac!

Thanks for the ticket, I was able to reproduce the issue.

[subrataemfluence]

Sorry for asking this question when @SergeyBiryukov already reproduced the issue.

I may have done it wrong way, but when I posted this comment Jesus ❤ you, it got saved without any error notification and displayed perfectly. Also when I edited and saved the comment I didn't get into any issue either. It is still displaying correctly after edit.

I also have define( 'DB_CHARSET', 'utf8' ); set in my wp-config.php.

Would you mind placing the exact steps here to reproduce?

[ayeshrajans]

I'm pretty sure @subrataemfluence is onto the root. Heart emoji is not under the utf8 Charest in MySQL, and requires ut8mb4.

[westonruter]

I believe this is a duplicate of a more recently-reported issue: #60362.

There is a patch attached to that ticket.

Props @callumbw95 for pointing this out.

[kasparsd]

@westonruter It seems that this issue is caused by the DB tables/columns using utf8mb4_unicode_ci charset (does support 4-byte chars) while the DB_CHARSET constant is set to utf8 (now called utf8mb3) which doesn't support 4-byte characters. The constant should be updated to either default empty string '' or to the correct collation.

During wp_new_comment() WP runs $wpdb->strip_invalid_text_for_column() instead of the emoji-to-htmlentities mapper, which should remove all multibyte chars if the column reports lack of mb4 support.

It could also be that the collations of the different tables used in the DB query are different and are causing the Illegal mix of collations (...,IMPLICIT) and (..,COERCIBLE) for operation ‘=’ error (a configuration issue).