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

March 25, 2017 1.9k views
CMS Ubuntu

Hi All,

This is first time I am trying to migrate my website from shared hosting to Digital Ocean.
I was following this guide for the same.

But when i run this command mysql -h localhost -u databaseuser -p databasename < backup_db.sql to restore database I am getting below error.

*ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4unicode520_ci'
*

I have selected ubuntu 14.04 and Ngnix

Could you please help

Thanks,
Avinash

1 Answer
hansen March 25, 2017
Accepted Answer

@avinashhrms
I'm pretty sure you're seeing this error because you're actually downgrading database.

I would highly recommend you setup 16.04 for WordPress, since it comes with a much newer Nginx, PHP and MySQL, which is also twice as fast.

So either use a newer setup (with MySQL 5.5.3+), or edit your backup_db.sql and do a search/replace of utf8mb4 to utf8
If you export the data via phpMyAdmin, you can change the compatibility mode to MySQL4. That would also solve it.

  • @avinashhrms

    @hansen is correct -- this would be to a difference in database versions where the latest version you're running does not support utf8mb4unicode520_ci.

    You can do a search and replace in the dump and replace instances of:

    utf8mb4unicode520_ci
    

    or

    utf8mb4_unicode_520_ci
    

    ...with

    utf8mb4_unicode_ci
    

    Then restore the database as you were. That should resolve the issue. I would check to make sure your database is 100% empty before trying to restore again though, just in case some of the tables from the previous attempts were successful.

Have another answer? Share your knowledge.