Unknown collation when I import a dump from an existing site into a development database?

I exported the sql from an existing database for an existing site that's hosted on Bluehost.

I'd like to be able to import it into the development environment without hitting an issue; I'm flexible, I can change the MySQL version I'm using in the development environment; my goal is to make it as easy as possible to import the exported sql code into the development environment without all these errors. That said is there anything I can do about this? I keep getting the following:

ERROR 1273 (HY000) at line 2071: Unknown collation: 'utf8mb4_unicode_520_ci'

I'm using the mysql-server package 5.5.53-0ubuntu0.14.04.1 at present, I believe Bluehost uses 5.3.28.

Is there a specified way of fixing this? Also if I were to import that data back into the site, would it cause an issue?

Topic collation mysql import export Wordpress

Category Web

1
answered at

This issue is as a result of your server not supporting the utf8mb4_unicode_520_ci collation type.

To resolve this you should convert the collation for all tables with utf8mb4_unicode_520_ci to utf8_general_ci

If you're exporting through phpmyadmin, you can:

  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".

OR run the following query on each of the affected tables:

ALTER TABLE myTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.