1

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?

Improve this question
3
  • Did you try this stuff? stackoverflow.com/questions/29916610/…
    – Dave Romsey
    Commented Dec 24, 2016 at 3:58
  • "I believe Bluehost uses 5.3.28." There was no MySQL version 5.3.x. There was, however, MariaDB 5.3.x, compatible with data exported from MySQL but not always the other way around, because MariaDB has addional features -- but it never went as high as 5.3.28... so you really might want to check this again, SELECT @@VERSION, @@VERSION_COMMENT;.
    – Michael - sqlbot
    Commented Dec 24, 2016 at 5:05
  • Possible duplicate of error importing localhost export file to server
    – Tunji
    Commented Dec 24, 2016 at 13:16

1 Answer 1

Reset to default
1

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.