Cannot import a DB from a shared hosting at Bluehost to a DB on Vesta CP

May 28, 2019 198 views
Databases MySQL

Hi All,

I have tried to import a database from a shared hosting at Bluehost to a DB created in phpMyAdmin on Vesta CP on centos 7, but it generates this error:

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

Can you please help guide how to fix this?

I very appreciate your support.

Regards

Thon

2 Answers

@thonhak23

This error is caused when there is a difference in the MySQL server from which you have exported the database and the MySQL server to which you are importing.

To overcome this error, you will have to edit your SQL file and perform a search and replace, changing all instances of ‘utf8mb4unicode520ci’ to ‘utf8mb4unicode_ci’.

Utilising command line will be much easier here:

  1. Save the SQL file in a directory. eg /root

  2. Go to that directory using:

cd /root

  1. Use sed to replace the string:

sed -i 's/utf8mb4unicode520ci/utf8mb4unicode_ci/g' database.sql

Replace the filename database.sql with the exact name.

Try importing this SQL file again.

Keep us posted if you need any further guidance on this and if there are additional questions or concerns please don't hesitate to reply with them.

Regards,
Prakash Jha
Developer Support

Hi Prakash,

Thank you for your help.

I have tried two things:

  1. The way you mentioned above by replacing that utf8mb4unicode520_ci with utf8mb4unicode which is available in phpMyAdmin. Importing DB from Bluehost to VPS server at Digital Ocean is successful without an error. However, it does not work as my website shows 500 ERRORs.

  2. Upgrade MySQL v5.5 to MariaDB v10.3 using this guide:

https://www.mysterydata.com/update-upgrade-to-mariadb-10-3-on-vestacp-cwp-centos-7/

on CentOs 7. I can now create DB in phpMyAdmin on VestaCP and select utf8mb4unicode520_ci. Importing db from Bluehost without updating the string as in (1) is successful too. However, my website still shows 500 ERRORs.

Both the above debugings are not working for me.

Do you know what could cause that issue?

Cheers
Thon

Have another answer? Share your knowledge.