DB errors with utf8 charset / utf8mb4 tables with MariaDB 10.3

  • Hi all,

    Ran into an issue after upgrading from MariaDB 10.1 to MariaDB 10.3, using the latest HyperDB (1.7). On a site with database tables using the utf8mb4 charset but the wp-config constant set to utf8, a Twitter oembed with an emoji repeatedly caused an “Incorrect string value” database error and it could not cache to the postmeta table. Toggling the DB_CHARSET constant to utf8mb4 resolved the issue.

    Without HyperDB, with DB_CHARSET set to utf8, WordPress will allow or disallow emojis based on the table charset. This would be my expectation of HyperDB behavior. I have only encountered it in this configuration (MariaDB 10.3, WordPress 4.9.16, HyperDB 1.7) so I don’t know how prevalent it is across database versions.

Viewing 1 replies (of 1 total)
  • Thread Starter davidegreenwald

    (@davidegreenwald)

    I’ve identified the code problem and a second issue, see comments below:

    The line $this->charset = 'utf8'; will prevent ut8mb4 tables from supporting utf8mb4 characters if DB_CHARSET is not defined explicitly as utf8mb4, which is not how WordPress core works.

    $this->collate = 'utf8_general_ci'; will conflict with creating new tables in multisite if utf8mb4 is set as the charset and prevent the tables from generating if collation is not set (WordPress’ recommended behavior), so this should conditionally set to utf8bm4_unicode_ci or another collation if utf8mb4 is active. In other words, the plugin should behave more like WordPress.

    The relevant code block from db.php, line 221, with comments:

    
function init_charset() {
		if ( function_exists('is_multisite') && is_multisite() ) {
		
                        # problem 1
                        $this->charset = 'utf8';

			if ( defined( 'DB_COLLATE' ) && DB_COLLATE )
				$this->collate = DB_COLLATE;
			else

                                # problem 2
				$this->collate = 'utf8_general_ci';

		} elseif ( defined( 'DB_COLLATE' ) ) {
			$this->collate = DB_COLLATE;
		}

		if ( defined( 'DB_CHARSET' ) )
			$this->charset = DB_CHARSET;
	}
    • This reply was modified 3 years, 8 months ago by davidegreenwald. Reason: Improve clarity
Viewing 1 replies (of 1 total)
  • The topic ‘DB errors with utf8 charset / utf8mb4 tables with MariaDB 10.3’ is closed to new replies.