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    39 utf8mb4 general ci  39  which as by now isn t much help  I have tried clearing the cookies but it still won t work  Please help

User · Answer

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.

User · Answer

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   then restore your file sql  mysql -u yourdBUser -p yourdBPasswd yourdB  lt  your file sql

User · Answer

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

User · Answer

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   lt  DOCTYPE html gt   lt html gt   lt head gt     lt title gt DB-Convert lt  title gt     lt style gt      body   font-family  Courier New   Courier  monospace       lt  style gt   lt  head gt   lt body gt    lt h1 gt Convert your Database to utf8 general ci  lt  h1 gt    lt form action  db-convert php  method  post  gt    dbname   lt input type  text  name  dbname  gt  lt br gt    dbuser   lt input type  text  name  dbuser  gt  lt br gt    dbpass   lt input type  text  name  dbpassword  gt  lt br gt     lt input type  submit  gt   lt  form gt    lt  body gt   lt  html gt   lt  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   gt   value               mysql query  ALTER TABLE  value CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci              echo   lt script gt alert  The collation of your database has been successfully changed     lt  script gt         gt

User · Answer

If you are using PHP7  then you would need a PHP script like this one in order to migrate your collation   lt  DOCTYPE html gt   lt html gt   lt head gt     lt title gt DB-Convert lt  title gt     lt style gt      body   font-family  quot Courier New quot   Courier  monospace       lt  style gt   lt  head gt   lt body gt    lt h1 gt Convert your Database to utf8 general ci  lt  h1 gt    lt form action  quot db-convert php quot  method  quot post quot  gt    dbname   lt input type  quot text quot  name  quot dbname quot  gt  lt br gt    dbuser   lt input type  quot text quot  name  quot dbuser quot  gt  lt br gt    dbpass   lt input type  quot text quot  name  quot dbpassword quot  gt  lt br gt     lt input type  quot submit quot  gt   lt  form gt    lt  body gt   lt  html gt   lt  php if    POST       dbname     POST  dbname       dbuser     POST  dbuser       dbpassword     POST  dbpassword        mysqli   new mysqli  db   dbuser  dbpassword     if   mysqli - gt  connect errno        echo  quot Failed to connect to MySQL   quot     mysqli - gt  connect error      exit           mysqli - gt  select db  dbname      result   mysqli- gt query  show tables      while  tables    result- gt fetch array              foreach   tables as  key   gt   value                mysqli- gt query  quot ALTER TABLE  value CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci quot              echo  quot  lt script gt alert  The collation of your database has been successfully changed     lt  script gt  quot        gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

So I solved in this way  from MySQL 5 6 to MySQL 5 5     mysqldump -u username -p --compatible mysql4 database name  gt  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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] #1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel

Examples related to mysql

Examples related to wordpress

Examples related to phpmyadmin

Examples related to cpanel