#1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel

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!

Solutions

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)

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.

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

?>

Similar questions

problem with Mysql Unknown error starting mysql and Unknown error starting apache
I have problem with all version of bitnami. When I shut down or restart my pc, WAMP can't connect with MySQL. I closed skype but still have the same problem. I also changed to XAMPP but the problem still there when I shut down. In the end, I revert to bitnami and I still have the same problem. I've watched all the troubleshooting video and do somet...
Default table collation on plugin activation?
I'm following this to make my plugin auto-create a table when the plugin is activated, but what happens is that while all the tables (the whole db) are utf8_general_ci, the newly created table is latin1_swedish_ci... Why isn't it also utf8? I thought it would also be utf8 by default since I have: in my wp-config.php... I have everything exactly the...
What is the correct way for plugins to create tables with special charset/collation considerations?
I'm using WordPress >=3.8. The default charset/collation values in wp-config.php are I am in a situation where utf8_general_ci would be adequate, but something more specific (utf8_danish_ci) will always be able to describe my data better. It is also the case that the data, which is fetched from an external source, will always be UTF-8. I am develop...
database collation differences
I have a database for a site I am developing in WAMP. The collation for the database is latin1_swedish_ci but every table in the database is utf84mb_unicode_ci. Should I be worried about this? In the future I can use utf84mb_unicode_ci for both, but what about the databases I already have content in?
How to stop WordPress using utf8mb4_unicode_520_ci collation?
Recently I've installed PHP7 on my MAMP and updated to newest version of WordPress. Every time I do a new site all my tables are using collation utf8mb4_unicode_520_ci I have tried setting define('DB_COLLATE', 'utf8_general_ci'); in my wp-config.php which worked fine until Gravity Forms ignored it and created all it's tables in utf8mb4_unicode_520_...
Please explain how Wordpress works with MySQL character set and collation at a low level
As the question title suggests, I'm looking to understand how Wordpress works with MySQL character sets and collation options. As I will show below, things don't make much sense to me... I installed Wordpress by following the instructions on their installation page: https://codex.wordpress.org/Installing_WordPress As part of the instructions, I fol...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.