#1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel

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.


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)


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>";
}

?>

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

Tags:

Mysql

Wordpress

Phpmyadmin

Cpanel

Recent Posts