Friday, November 20, 2015

Unknown collation: 'utf8mb4_unicode_ci' Cpanel

http://stackoverflow.com/questions/29916610/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!
shareimprove this question

8 Answers

up vote 26 down vote accepted
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>";
}

?>
shareimprove this answer

    
Helped me a lot. spent nearly days to get this fixed on my old server. you are life saver indeed. ;) – Keep Coding Jun 25 at 19:20
3  
This seems like overkill vs mysqldump --compatible=mysql4 – icc97 Aug 4 at 20:32
    
For me it worked. Remember to name the file db-convert.php – Fred K Sep 4 at 15:46
up vote 58 down vote
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.
shareimprove this answer

    
Worked for me too, thanks! – nach May 2 at 17:10
    
Thanks for it's work fine – Mansukh Khandhar Jun 26 at 11:22
    
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 at 21:48
    
G-e-n-i-u-s!!!! – Fred K Jul 3 at 8:02
    
This is absolute amazing. It worked perfectly. +1 – Student T Oct 1 at 10:55
up vote 9 down vote
The best thing to do is export your database to .sql, open it on notepad++ and go to "search and replace". Then you put "mb4" on search and nothing on replace. It will replace the utf8mb4_unicode_ci to utf8_unicode_ci. Now you go to your phpmyadmin( destination ) and set the db collation to utf8_unicode_ci. ( Operations > Collation ).
WORKS PERFECTLLY
shareimprove this answer

    
Wow, this actually worked perfectly for me! – Luuk Van Dongen Jul 30 at 10:06
    
thanks - got me out of a hole – maxelcat Sep 8 at 15:49
up vote 7 down vote
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).
shareimprove this answer

1  
You can check your version of MySQL via command line with "mysql -V" – Edd Smith Apr 29 at 9:51
up vote 5 down vote
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.
shareimprove this answer

    
Changing compatibility to "MYSQL 40" totally worked for me. – Keryn Gill Jul 29 at 18:34
    
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 4 at 21:27
up vote 3 down vote
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
shareimprove this answer

up vote 1 down vote
After the long time research i have found the solution for above:
  1. Firstly you change the wp-config.php> Database DB_CHARSET default to "utf8"
  2. Click the "Export" tab for the database
  3. Click the "Custom" radio button
  4. 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.
  5. Scroll to the bottom and click go
Then you are on.
shareimprove this answer

    
This worked for me, thanks! – Darren Riches Nov 2 at 15:24
up vote 0 down vote
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.
shareimprove this answer

Posted by at
Labels: , , ,