ddev: Importing database: Use mysql_version to avoid `Unknown collation: 'utf8mb4_0900_ai_ci'`

Work around:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' 2019-10-26-prod.sql

Opened the dump.sql file in Notepad++ and hit CTRL+H to find and replace the string “utf8mb4_0900_ai_ci” and replaced it with “utf8mb4_general_ci“.

It worked for me ,hope it will work for u as well.

Work around:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' 2019-10-26-prod.sql

I’ve had a problem with the above command (sed: can’t read : No such file or directory) but sed -i myfilename.sql -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' worked for me.

Work around:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' 2019-10-26-prod.sql

I’ve had a problem with the above command (sed: can’t read : No such file or directory) but sed -i myfilename.sql -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' worked for me.

Both of the answers above should be correct, except for the slight difference in sed command parameters under MAC/Linux. Mac:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' 2019-10-26-prod.sql

Linux/Ubuntu:

sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i myfilename.sql

There’s no need for a workaround in ddev. Please use the mysql version that matches your server.

1. ddev stop --remove-data
2. Edit your .ddev/config.yaml to remove any mariadb_version. Add mysql_version: 5.7 or mysql_version: 8.0 or whatever matches your server version.
3. ddev start
4. ddev import-db --src=/path/to/your/sqldump.sql.gz

what worked for me: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' db.sql

Hope it helps 👍🏼

I updated my config to use mysql 8 and that solved the problem for me.

Insert a hint here, if you crash the error of MySQL server has gone away when importing data, it may lead to incomplete data import, and some contents are confused when visiting the website. Try to set max_allowed_packet as large as possible.

mysql> set global max_allowed_packet=10000000000;

That doesn’t sound like it has anything to do with this issue @mohkhz2001 - but it also sounds like it has nothing to do with ddev, but probably with your data. Please make sure you’re using the same database type (MySQL?) and version (8.0?) in ddev that you’re using on the server your dump came from. Then if you need help, please open an issue with the specifics, and preferably a test case.

I believe this error is caused because the local server and live server are running different versions of MySQL. To solve this:

Open the sql file in your text editor
Find and replace all utf8mb4_0900_ai_ci with utf8mb4_unicode_ci
Save and upload to a fresh mySql db

after that try again ,it works fine for me