[wp-trac] [WordPress Trac] #60362: Emojis cause "Updating failed. Could not update post in the database." error

[WordPress Trac]

#60362: Emojis cause "Updating failed. Could not update post in the database."
error
-------------------------------------------------+-------------------------
 Reporter:  mikefitzman                          |       Owner:  (none)
     Type:  defect (bug)                         |      Status:  new
 Priority:  normal                               |   Milestone:  Awaiting
                                                 |  Review
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  has-test-info needs-testing needs-   |     Focuses:
  patch                                          |
-------------------------------------------------+-------------------------
Changes (by kasparsd):

 * keywords:  has-test-info needs-testing needs-screenshots => has-test-info
     needs-testing needs-patch


Comment:

 I was able to replicate the issue with a fresh WP 6.8.2 + mysql v8 and
 **importantly** `define( 'DB_CHARSET', 'utf8mb3' );` in `wp-config.php`
 ''before'' running the WP install.

 Recent versions of MySQL and MariaDB now use `utf8mb3_*` instead of
 `utf8_*` for collations/charsets.

 This causes the `wp_insert_post()` logic https://github.com/WordPress
 /wordpress-develop/blob/35037e699148d9e73607480319bd494f8449767f/src/wp-
 includes/post.php#L4840-L4850 to skip `wp_encode_emoji()` because
 `$wpdb->get_col_charset( $wpdb->posts, 'post_content' )` returns `utf8mb3`
 instead of `utf8`.

 {{{#!php
 $emoji_fields = array( 'post_title', 'post_content', 'post_excerpt' );

 foreach ( $emoji_fields as $emoji_field ) {
         if ( isset( $data[ $emoji_field ] ) ) {
                 $charset = $wpdb->get_col_charset( $wpdb->posts,
 $emoji_field );

                 if ( 'utf8' === $charset ) {
                         $data[ $emoji_field ] = wp_encode_emoji( $data[
 $emoji_field ] );
                 }
         }
 }
 }}}


 `get_col_charset()` returns `utf8mb3` because:

 {{{#!php
 public function get_col_charset( $table, $column ) {
         // Rest of logic.

         list( $charset ) = explode( '_', $this->col_meta[ $tablekey ][
 $columnkey ]->Collation );
         return $charset;
 }
 }}}

 where `$this->col_meta[ $tablekey ][ $columnkey ]->Collation` is something
 like `utf8mb3_general_ci` from which it extracts `utf8mb3`.

 The actual fix here is to extend the `if ( 'utf8' === $charset ) {`
 conditional to `if ( 'utf8' === $charset || 'utf8mb3' === $charset ) {`.
 This is safe to do because `utf8mb3` is an alias
 https://dev.mysql.com/doc/refman/8.4/en/charset-unicode-utf8.html

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/60362#comment:17>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform