CPanel Data Collation Error with WordPress

frustration

Panic! You have your WordPress (version 4.6.1) site ready to upload from your localhost to your host provider. Your WordPress folders have transferred successfully but when you try to upload your database into your hosts CPanel you get the dreaded collation error:

**ERROR** database error write ‘Unknown collation: ‘utf8mb4_unicode_520_ci”

collation-error

The error message means your database is using a collation which the MySQL server does not understand. This can happen when you use a database which has been created with a newer version of MySQL (for me this was 5.6 in WordPress) , with an older MySQL server version (host provider: version 5.3).  Online searches would suggest badgering your host provider to update their MySQL database to a newer version (which I did!) but when they are not able to update you need to find a workaround. The answer is relatively simple. You can view the video and/or follow the steps below.


STEP 1

On your computer (localhost) go to phpMyAdmin and find your WordPress database on the left of the screen:

phpmyadmin1

STEP 2

Once the database has been selected go along the top bar and choose the EXPORT tab. Scroll down and select ‘Export Method’ as CUSTOM.

phpMyAdmin-export

 

Scroll down the screen until you find the section ‘Format-specific options‘; from the drop-down list choose MYSQL40. The purpose of MYSQL40 is that it increases compatibility when moving a newer database to an older database.

phpMyAdmin-MYSQL40

STEP 3

Next, scroll to the bottom of the screen and press GO. You will be prompted to save your file. Make a note of the name and where you have saved it onto your computer.

STEP 4

You now need to login into your CPanel, where your site is hosted. For me this was HostGator. In your main panel scroll down until you find ‘Databases’ . Select phpMyAdmin.

CPanel

 

STEP 5

You should now be redirected to you phpMyAdmin account. Ensure that you select the database where you want to load you SQL file into. This will be on the left of the screen:

phpmyadmin-hostgator1

With the correct database selected choose IMPORT (along top bar) and then scroll down to “Format-Specific Options” and choose from the dropdown list MYSQL40. Once done you can then click GO.

phpmyadmin-hostgator2

 

STEP 6

All being well you should receive a message…

collation-success

Scroll Up