Collation (general and 520) mixed up by WP

  • Resolved Stefano Lissa

    (@satollo)


    6 years, 3 months ago

    Hi, I’m not sure if that issue has been resolved, but it is happening too often on installations I manage.

    The case: the original WP was installed time ago on database with support for utf8mb4 and utf8mb4_unicode_general_ci.

    Those blog has the collation (DB_COLLATE) set to an empty string.

    As soon as the provider upgrade the database, if new plugins are installed, their tables are created (they use the $charset_collate variable) with utf8mb4_unicode_520_ci but WP standard tables are NOT updated to that collation. So every possible SQL join using matching text fields returns an error.

    Would not be a good choice, when the collate is not specified, if WP converts it’s own tables? Otherwise there is even a discrepancy between the collate returned by $wpdb and the actual table collate.

    For a specific case, a new installation of WooCommerce has all the (extra) tables with 520 collate while the blog has general collate.

    Thank you, Stefano.

Viewing 2 replies - 1 through 2 (of 2 total)

  • Thank you for the suggestion, @satollo! I’ve opened a ticket to investigate it further, I think we can make the scenario you’re describing much smoother. ??

    It’d be super helpful if you could write about your current experience on the ticket: what size tables are you needing to convert, and how long does that take? What versions of MySQL are you upgrading from/to? Are there particular plugins that generate these failing JOIN queries?

    Thread Starter Stefano Lissa

    (@satollo)

    Hi, the plugin raising the error is a commercial component, so it’s not available in the WP.org repo, but it is joining the posts table (for the WooCommerce product post type) and the WooCommerce custom table containing the order items metadata.

    I’ll try to extract the exact query, anyway the collate mismatch is on the order items meta value which is a string with unicode_520 collation.

    I want to get deeper in this blog to understand is it’s a general case. It seems the standard wp-config.php which contains by default utf-8 as charset and and empty string for collation, could generate that when there is an update of the mysql version and subsequent new plugins installation which create their own tables.

    Just a question: shouldn’t WP migrate all tables to the unicode_520 collation when it detects support for that feature? Like the old one “maybe_convert_to_utf8mb4()”?

    Or the wp-config.php, once created, could be completed with the chosen first installation collation to keep it static.

    Thank you, Stefano.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Collation (general and 520) mixed up by WP’ is closed to new replies.