14.x [database] - Charset / collation issues

[Djennez]

I have seen a few reports (but not many) about problems with the creation or alteration of our new 14.x tables. It seems that we're not catching edgecases with our Ruckusing adapter.

For example this configuration barely works with normal WordPress usage:

/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', '' );

/** The Database Collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', 'utf8_general_ci' );

But our Ruckusing adapter now tries to execute the following query: ALTER TABLE wp_yoast_migrations CONVERT TO COLLATE utf8_general_ci This errors out with:

[You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLLATE utf8_general_ci' at line 1]

The other way around:

/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8_general_ci' );

/** The Database Collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );

Is also barely working (by some miracle) with normal WP usage. Our migration now tries to execute: ALTER TABLE wp_yoast_migrations CONVERT TO CHARACTER SET utf8_general_ci This errors with:

[Unknown character set: 'utf8_general_ci']

Seeing as I have not seen a lot of these errors popping up in the wild, and it's very strange configuration, I'm not giving this a high severity.

[herregroen]

Option here to avoid things like this might be to actually simply read the existing charset and collation of the wp_posts table as opposed to trusting the values from $wpdb.

For reference, we're already getting our values directly from $wpdb as follows: $this->wpdb->charset.

This issue seems to be specific for our migration that alters table collation.

[monbauza]

One of our customers is getting the notice "Yoast SEO was unable to create the database tables required and as such will not function correctly" and the error below. I think it's related to the issue described in this bug report.

Erreur de la base de données WordPress : [Unknown character set: 'utf8mb4_unicode_ci'] CREATE TABLE wp_wpfm_backup ( id int(11) NOT NULL AUTO_INCREMENT, backup_name text NULL, backup_date text NULL, PRIMARY KEY (id) ) DEFAULT CHARACTER SET utf8mb4_unicode_ci COLLATE utf8mb4_unicode_ci

Erreur de la base de données WordPress : [Unknown character set: 'utf8mb4_unicode_ci'] ALTER TABLE wp_yoast_migrations CONVERT TO CHARACTER SET utf8mb4_unicode_ci COLLATE utf8mb4_unicode_ci

wp-config.php define('DB_CHARSET', 'utf8mb4_unicode_ci'); define('DB_COLLATE', 'utf8mb4_unicode_ci');

Tables information

Site: https://www.martin-transports.ch/ Dev site: https://dev.martin-transports.ch/ Site health info: site-health-martin-transports.txt debug.log debug-martin-transports.log

[Djennez]

@monbauza that is a configuration error on his site as described here: https://wordpress.org/support/topic/yoast-seo-was-unable-to-create-the-database-tables-required-and-as-such-will-not-6/#post-12800665

utf8mb4_unicode_ci is not a charset but a collation. The charset should be utf8mb4 in his case, and the collation utf8mb4_unicode_ci. These are set in the wp-config.php as per my comment in the link above :)

[monbauza]

Thanks for looking into this @Djennez ;-) I'll let the customer know. Feel free to remove my comments in this issue if they interfere with the original issue.

[Pcosta88]

related https://github.com/Yoast/wordpress-seo/issues/15151

[monbauza]

+1 HS Ticket #607324. This customer hadn't defined a CHARSET in wp-config.php and was getting the following error:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE utf8_unicode_ci At line 1 for query ALTER TABLE wp_yoast_migrations CONVERT TO COLLATE utf8_unicode_ci