CODEWITHSUNDEEP
mysqlmariadbworkflowcollationddev
Len Lamberg
Len Lamberg

Reputation: 51

Collation error importing a database using DDEV

I am running a site locally using DDEV, the remote site is on shared hosting (siteground). I created an SQL dump using SSH on the remote site, then imported the database using ddev import-db. The import failed with the following message: ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

There are several answers related to the error message "ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'" Most of them involved using a text editor to replace "utf8mb4_0900_ai_ci" with "utf8mb4_general_ci". I did not want to do this, because I would have to edit the database every time I synced the site as part of my workflow.

I used phpadmin to look at the database on the remote site at Siteground, and discovered that they were using mysql 8.0. I looked at the file: .ddev/config.yaml and saw (line 11) database: /type: mariadb/ version: "10.4" I CHANGED that yaml file to read database: /type: mysql/ version: "8.0"

After that, I entered ddev restart

At that point, I was able to import the database using ddev import-db successfully. I entered ddev drush cr to clear the cache on the new database and the site worked correctly after that.

I hope that this helps other developers.

Upvotes: 5

Views: 352

Answers (1)

danblack
danblack

Reputation: 14736

If you just used mariadb:10.11 (or newer) you could have upgrade to utf8mb4_1400_ai_ci, a newer standard.

That would also have also solved without needing to downgrade the collation to something that isn't even a standard (utf8mb4_general_ci).

Also: MariaDB 10.4 just had its last release and is officially EOL next month.

Upvotes: 3

Related Questions