Contrib.social

Upgrade D7->D9 works only on same collation

Open on Drupal.org →
Created on 8 December 2022, over 2 years ago
Updated 9 March 2023, over 2 years ago

Problem/Motivation

I wanted to migrate my website (multilingual D7 with utf8mb4_unicode_ci collation to D9)

Steps to reproduce

Create a Website with Drupal 7 and MySQL/MariaDB backend with default collation utf8mb4_unicode_ci three languages (German, English, Russian, Ukrainian or whatever) add multilingual content (menu, nodes, etc.). Then set up Drupal 9 with Migrate UI and try to migrate. Many migration steps fail as MariaDB does not allow cross-collation queries. After I figured out, that I can set up Drupal 9 with a predefined empty database mentioned in settings.php with a proper default collation, I managed to migrate most settings but two strange collation issues remained. Only after I added support for collation to the source database credential form (see attached patch) these two issues were resolved. Obviously the source connection should be made with the right collation in order to get the migration status tables right.

Proposed resolution

  • properly document how to deal with collations different from utf8mb4_general_ci without loosing functionality
  • allow the collation for the source connection during migration
  • avoid cross collation queries during migration, i.e. treat MariaDB⇒MariaDB migrations like migrations between different database types

Remaining tasks

  • Adapt the patch for tests and Drupal 9.5. The patch should be adapted to the changes from Issue 3096101 → .
  • Document proper collation handling in the upgrade tutorial. Simply replacing utf8mb4_unicode_ci by utf8mb4_general_ci is not a solution.

User interface changes

Add a field “collation” to the extended options section for setting up the MySQL/MariaDB source connection in https://site.name/upgrade.

API changes

None.

Data model changes

None.

Release notes snippet

Allow setting the source database collation in Migrate UI.

🐛 Bug report
Status

Postponed: needs info

Version

9.5

Component
MySQL driver  →

Last updated 18 days ago

Created by

🇩🇪Germany keinstein

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment over 2 years ago →
    🇩🇪Germany keinstein

    Actually I don't remember. I think nodes and Menu items were involved. I have German, English and Russian translations active. Most of the failed queries involved migration state tables. And I don't think that this issue can be solved only for some migrations.

    My solution was:
    Change all tables to utf8mb4_unicode_ci, and then I exposed the missing database parameters such as collation to the migration form, so that the new database can be generated as utf8mb4_unicode_ci instead of utf8mb4_general_ci. This is a way that worked for me.

    If you want to reproduce the issue try to set up a drupal site with real language content (umlauts, cyrillic letters, etc.) in utf8mb4_unicode_ci encoding and upgrade it using stock Drupal 9.

    Maybe the result changes depending whether you allow incremental upgrades. I had them enabled.

    I don't know how to create and execute a corresponding test case.

  • Comment 3 months ago →
    🇺🇸United States smustgrave
  • Comment 3 months ago →
    🇳🇿New Zealand quietone

    This looks like an edge case so documenting on the known issues → page is likely sufficient.

  • Comment 3 months ago →
    🇺🇸United States smustgrave

    I’m probably one of the last people to write that haha

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024