CODEWITHSUNDEEP
mysql
raffaele parlato
raffaele parlato

Reputation: 81

"COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'"

I am not an expert in mysql I have looked in other posts for the solution to this problem but I cannot solve it, could someone help me to solve this problem?

I'm migrating a web and when I import the database I get this error.

thanks a lot

Upvotes: 8

Views: 34560

Answers (5)

kenorb
kenorb

Reputation: 166909

If you're importing your database from the file, you can use the following workaround with sed command to correct it:

sed 's/utf8mb4_0900_ai_ci/utf8_general_ci/g;s/utf8mb4/utf8/g' file.sql > newfile.sql

If you're using ddev, here is the example of usage:

zcat file.sql.gz | sed 's/utf8mb4_0900_ai_ci/utf8_general_ci/g;s/utf8mb4/utf8/g' | ddev import-db

In case if above still causes issues, here is more sophisticated pattern in form of shell alias.

alias mysql-fix-collation='sed "s/utf8mb4_0900_ai_ci\|utf8mb4_unicode_ci/utf8_general_ci/g;s/\butf8mb[34]/utf8/g"'

Usage:

zcat file.sql.gz | mysql-fix-collation | ...

Upvotes: 2

Andrey Chesnakov
Andrey Chesnakov

Reputation: 170

If you just need to execute specific query without modifying SQL or charset settings, then adding

SET CHARSET "utf8";

before SQL query will solve this error probably.

Upvotes: 0

ntk4
ntk4

Reputation: 1307

This question has already been answered here: I have that error"#1273 - Unknown collation: 'utf8mb4_0900_ai_ci'"

NOTE: COLLATE=utf8mb4_0900_ai_ci replace with COLLATE=utf8mb4_general_ci.

Upvotes: 2

Raees
Raees

Reputation: 29

You can change the collation of the database(where you are importing) to utf8mb4

See here

Upvotes: 1

Luis H Cabrejo
Luis H Cabrejo

Reputation: 324

I found it very easy fix. Make sure you backup your database before, just in case you need to change to another collation. Go to PHPMYADMIN, select the database, and hit OPERATIONS, there at the end, find COLLATION select a collation what will work for you 'utf8mb4_unicode_ci' for example, then check both 'Change all tables collations' and 'Change all tables columns collations' hit GO. My database worked perfectly.

Upvotes: 1

Related Questions