#1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel

Question

I have a WordPress database on my local machine that I want to transfer to a hosted phpMyAdmin on cPanel. However, when I try to import the database into the environment, I keep getting this error:

#1273 - Unknown collation: 'utf8mb4_unicode_ci'

I have tried to Google around and the only solution I can find is this one phpmysql error - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci' which as by now isn't much help. I have tried clearing the cookies but it still won't work. Please help!

What version of WordPress? See https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/ — Rick James, Nov 20 '18 at 18:43

Evster · Answer 1 · 2016-08-12T19:14:21.277

232

The technique in this post worked for me

1) Click the "Export" tab for the database

2) Click the "Custom" radio button

3) Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.

4) Scroll to the bottom and click "GO".

I'm not certain if doing this causes any data loss, however in the one time I've tried it I did not notice any. Neither did anyone who responded in the forums linked to above.

Edit 8/12/16 - I believe exporting a database in this way causes me to lose data saved in Black Studio TinyMCE Visual Editor widgets, though I haven't ran multiple tests to confirm.

edited Aug 12 '16 at 19:14

answered Apr 30 '15 at 18:08

Evster

2,552
2
16
14

didn't work for me, i got the error #1231 - Variable 'character_set_client' can't be set to the value of 'NULL' – nerdess Jun 26 '15 at 21:48
After multiple failed attempts at other solutions, this one worked perfectly on the first attempt. – Vincent Polisi Sep 09 '16 at 20:29
Does NOT work with some new Wordpress sites. When importing it on the other server, it generates this error on the very first table it tries to import (wp_commentmeta) #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM AUTO_INCREMENT=1' at line 19 – that-ben Oct 19 '17 at 15:53
this didn't seem to make any difference for me, still getting the same error messages – Oct 16 '19 at 08:41
you are the boss – Aseel Ashraf Apr 09 '20 at 18:00

score 184 · Answer 2 · edited Feb 03 '20 at 09:36

184

If you have already exported a .sql file, the best thing to do is to Find and Replace the following if you have them in your file:

utf8mb4_0900_ai_ci to utf8_unicode_ci
utf8mb4 to utf8
utf8_unicode_520_ci to utf8_unicode_ci

It will replace utf8mb4_unicode_ci to utf8_unicode_ci. Now you go to your phpMyAdmin cPanel and set the DB collation to utf8_unicode_ci through Operations > Collation.

If you are exporting to a .sql, it's better to change the format on how you're exporting the file. Check out Evster's anwer (it's in the same page as this)

edited Feb 03 '20 at 09:36

Cyrus V.

151
4
8

answered Jul 19 '15 at 23:01

Rangel R. Morais

2,048
1
12
11

2

it's working perfectly along with this one http://stackoverflow.com/a/30694416/1022726 – iurii Jul 13 '16 at 20:50
1

For all you unix folks: `sed -i.bak s/utf8mb4/utf8/g FILE_NAME`. This will find all occurrences of `utf8mb4` in FILE_NAME and replace it with `utf8` while saving a copy of the original file to FILE_NAME.bak. You may need to tweak it to specify the exact coalition in your case but this is a start :) – DaveLak Aug 06 '17 at 21:36
1

I also had to replace: utf8_unicode_520_ci with: utf8_unicode_ci – Nick Rivers Mar 01 '18 at 15:30
Or via vi: `vi dump.sql` and then in via do this: `:%s/uf8mb4/utf8/g`. – Valentin Grégoire Nov 02 '18 at 20:37
2

and replace `utf8_0900_ai_ci` with `utf8_unicode_ci` – Irfan Y Jul 11 '19 at 06:06

score 109 · Accepted Answer · edited Oct 06 '18 at 19:14

109

I had the same issue as all of our servers run older versions of MySQL. This can be solved by running a PHP script. Save this code to a file and run it entering the database name, user and password and it'll change the collation from utf8mb4/utf8mb4_unicode_ci to utf8/utf8_general_ci

<!DOCTYPE html>
<html>
<head>
  <title>DB-Convert</title>
  <style>
    body { font-family:"Courier New", Courier, monospace; }
  </style>
</head>
<body>

<h1>Convert your Database to utf8_general_ci!</h1>

<form action="db-convert.php" method="post">
  dbname: <input type="text" name="dbname"><br>
  dbuser: <input type="text" name="dbuser"><br>
  dbpass: <input type="text" name="dbpassword"><br>
  <input type="submit">
</form>

</body>
</html>
<?php
if ($_POST) {
  $dbname = $_POST['dbname'];
  $dbuser = $_POST['dbuser'];
  $dbpassword = $_POST['dbpassword'];

  $con = mysql_connect('localhost',$dbuser,$dbpassword);
  if(!$con) { echo "Cannot connect to the database ";die();}
  mysql_select_db($dbname);
  $result=mysql_query('show tables');
  while($tables = mysql_fetch_array($result)) {
          foreach ($tables as $key => $value) {
           mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
     }}
  echo "<script>alert('The collation of your database has been successfully changed!');</script>";
}

?>

edited Oct 06 '18 at 19:14

geisterfurz007

5,292
5
33
54

answered Apr 29 '15 at 09:41

Stephen

1,122
1
7
5

52

This seems like overkill vs `mysqldump --compatible=mysql4` – icc97 Aug 04 '15 at 20:32
2

For me it worked. Remember to name the file `db-convert.php` – Fred K Sep 04 '15 at 15:46
1

If you make 'localhost' an input it's perfect. – br4nnigan Feb 11 '16 at 16:59
3

`mysqldump --compatible=mysql4` or the answer below is a better option. I don't think it's a wise idea to change something like this on a live site. Better to export in the correct format, or if that's not an option, edit the exported file. – Tisch Apr 26 '16 at 21:12
This is amazing! Thank you so much. – itsricky Dec 16 '16 at 00:04
For me the command `mysqldump --compatible=no_table_options,mysql4` did the trick instead. – specialk1st Jan 23 '17 at 11:17
+1 I was able to use this answer's SQL query outside of the script context to "fix" each table and it worked for my problem. Then my table is the correct collation and I can import without having to remember to add flags – jamil Jan 26 '17 at 16:25
Check answer below so you can get a PHP7 version of this (new mysql library) – rmartrenado Feb 04 '21 at 12:41

score 52 · Answer 4 · edited Jun 12 '21 at 21:40

52

i use this in linux :

sed -i 's/utf8mb4/utf8/g' your_file.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_0900_ai_ci/utf8_general_ci/g' your_file.sql

then restore your_file.sql

mysql -u yourdBUser -p yourdBPasswd yourdB < your_file.sql

edited Jun 12 '21 at 21:40

Anoop Naik

335
7
15

answered Mar 07 '17 at 13:17

halilintar8

771
6
6

3

There's a comment on another answer, which bears repeating here. The OS X version of sed requires an extra argument after the -i flag. So `sed -i '' ....` works. – Kent May 30 '17 at 23:40
2

I had to run this as well: `sed -i 's/utf8_0900_ai_ci/utf8_general_ci/g' your_file.sql` – Michael Hays Jul 11 '19 at 15:04
The `-i` flag just mean replace in-place, so not to generate a new file – Sam Hammamy Aug 06 '21 at 16:52
another OSX gotcha: prepending `sed` with encoding info like `LC_ALL=C sed …` fixes the byte sequence error, see https://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel/42649372 – Paracetamol Mar 23 '23 at 10:32

score 30 · Answer 5 · answered May 14 '15 at 23:05

Wordpress 4.2 introduced support for "utf8mb4" character encoding for security reasons, but only MySQL 5.5.3 and greater support it. The way the installer (and updater) handles this is that it checks your MySQL version and your database will be upgraded to utfmb4 only if it's supported.

This sounds great in theory but the problem (as you've discovered) is when you are migrating databases from a MySQL server that supports utf8mb4 to one that doesn't. While the other way around should work, it's basically a one-way operation.

As pointed out by Evster you might have success using PHPMYAdmin's "Export" feature. Use "Export Method: Custom" and for the "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".

For a command line export using mysqldump. Have a look at the flag:

$ mysqldump --compatible=mysql4

Note: If there are any 4-byte characters in the database they will be corrupted.

Lastly, for anyone using the popular WP Migrate DB PRO plugin, a user in this Wordpress.org thread reports that the migration is always handled properly but I wasn't able to find anything official.

The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL

At this time, there doesn't appear to be a way to opt out of the database update. So if you are using a workflow where you are migrating a site from a server or localhost with MySQL > 5.5.3 to one that uses an older MySQL version you might be out of luck.

If you then try and import the mysql4 compatible dump into a post v5.5.3 database (I'm using 5.5.28) then it fails because the script includes `TYPE=MyISAM` that was removed in v5.1. Do a search and replace with `ENGINE=MyISAM`. I couldn't see a way around this using the `mysqldump` output options. — icc97, Aug 04 '15 at 21:27

SherylHohman · Answer 6 · 2017-07-03T03:56:43.280

27

In my case it turns out my
new server was running MySQL 5.5,
old server was running MySQL 5.6.
So I got this error when trying to import the .sql file I'd exported from my old server.

MySQL 5.5 does not support utf8mb4_unicode_520_ci, but
MySQL 5.6 does.

Updating to MySQL 5.6 on the new server solved collation the error !

If you want to retain MySQL 5.5, you can:
- make a copy of your exported .sql file
- replace instances of utf8mb4unicode520_ci and utf8mb4_unicode_520_ci
...with utf8mb4_unicode_ci
- import your updated .sql file.

edited Jul 03 '17 at 03:56

answered Jan 07 '17 at 13:07

SherylHohman

16,580
17
88
94

1

Yes - uploading to 5.6 is the simplest solution for this (and Ubuntu has a `mysql-server-5.6` package you can install that will remove 5.5 for you automatically). – William Turrell Dec 28 '17 at 17:27

score 16 · Answer 7 · edited May 23 '17 at 12:26

16

There is a line in wp-config.php:

define('DB_CHARSET', 'utf8mb4');

If you follow Markouver's / Evster's instructions, don't forget to change this line on production server to

define('DB_CHARSET', 'utf8');

in order to fix broken 4-byte characters

edited May 23 '17 at 12:26

Community

1
1

answered Jun 07 '15 at 14:04

Isk1n

371
2
6

1

Also don't forget to modify `define('DB_COLLATE', 'utf8_general_ci');` as well. This helped for me. – Abduhafiz Jul 16 '16 at 16:58

score 11 · Answer 8 · edited Aug 19 '15 at 11:41

After the long time research i have found the solution for above:

Firstly you change the wp-config.php> Database DB_CHARSET default to "utf8"
Click the "Export" tab for the database
Click the "Custom" radio button
Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
Scroll to the bottom and click go

Then you are on.

flomei · Answer 9 · 2015-05-05T22:07:40.330

7

Seems like your host does not provide a MySQL-version which is capable to run tables with utf8mb4 collation.

The WordPress tables were changed to utf8mb4 with Version 4.2 (released on April, 23rd 2015) to support Emojis, but you need MySQL 5.5.3 to use it. 5.5.3. is from March 2010, so it should normally be widely available. Cna you check if your hoster provides that version?

If not, and an upgrade is not possible, you might have to look out for another hoster to run the latest WordPress versions (and you should always do that for security reasons).

edited May 05 '15 at 22:07

answered Apr 28 '15 at 10:26

flomei

860
1
11
36

1

You can check your version of MySQL via command line with "mysql -V" – Edd Aslin Apr 29 '15 at 09:51

score 2 · Answer 10 · edited May 23 '17 at 12:10

2

So I solved in this way, from MySQL 5.6 to MySQL 5.5:

$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql
$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql

(Optional) Create a .sql.gz file:

$ gzip database_name.sql

Explanation

$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql

As explained in this answer, this is just the equivalent of this options from phpMyAdmin: "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".

$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql

We needs this, to solve this issue:

ERROR 1064 (42000) at line 18: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 9

edited May 23 '17 at 12:10

Community

1
1

answered Dec 23 '16 at 11:29

Andrea

15,900
18
65
84

1

Note if you're using OS X's version of sed: `$ sed -i'' 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql` . If you want to keep a copy as `database_name.sql.bak` before the sed replace: `$ sed -i'bak' 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql` – Justin Fortier Jan 25 '17 at 12:33
you can also inline `sed`. – davidbitton Jun 06 '17 at 15:32
Also if you're running mysqldump on MySQL 8 you need to use `--compatible=ansi` option – AbstractVoid Apr 29 '19 at 13:15

score 1 · Answer 11 · answered Apr 28 '15 at 10:47

1

I also experienced this issue. Solution which worked for me was opening local database with Sequel Pro and update Encoding and Collation to utf8/utf8_bin for each table before importing.

answered Apr 28 '15 at 10:47

Damian Walsh

11
2

score 1 · Answer 12 · answered Dec 23 '16 at 09:31

The easiest way to do is export your database to .sql, open it on Notepad++ and "Search and Replace" the utf8mb4_unicode_ci to utf8_unicode_ci and also replace utf8mb4 to utf8. Also don't forget to change the database collation to utf8_unicode_ci (Operations > Collation).

score 0 · Answer 13 · answered Apr 26 '18 at 03:29

0

open the sql file on Notepad++ and ctrl + H. Then you put "utf8mb4" on search and "utf8" on replace. The issue will be fixed then.

answered Apr 26 '18 at 03:29

Nur Uddin

1,798
1
28
38

It says "unknown collation utf8" – Christian Vincenzo Traina Jun 17 '18 at 13:57

score 0 · Answer 14 · answered Feb 04 '21 at 12:42

If you are using PHP7, then you would need a PHP script like this one in order to migrate your collation:

<!DOCTYPE html>
<html>
<head>
  <title>DB-Convert</title>
  <style>
    body { font-family:"Courier New", Courier, monospace; }
  </style>
</head>
<body>

<h1>Convert your Database to utf8_general_ci!</h1>

<form action="db-convert.php" method="post">
  dbname: <input type="text" name="dbname"><br>
  dbuser: <input type="text" name="dbuser"><br>
  dbpass: <input type="text" name="dbpassword"><br>
  <input type="submit">
</form>

</body>
</html>
<?php
if ($_POST) {
  $dbname = $_POST['dbname'];
  $dbuser = $_POST['dbuser'];
  $dbpassword = $_POST['dbpassword'];

  $mysqli = new mysqli('db',$dbuser,$dbpassword);
  if ($mysqli -> connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
    exit();
  }
  $mysqli -> select_db($dbname);
  $result= $mysqli->query('show tables');
  while($tables = $result->fetch_array) {
          foreach ($tables as $key => $value) {
           $mysqli->query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
     }}
  echo "<script>alert('The collation of your database has been successfully changed!');</script>";
}

?>

#1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel

14 Answers14

Explanation

Linked