Marco Maffei Marco Maffei - 4 months ago 17
SQL Question

Moving from a database written in mysql 5.5 to one written in mysql 5.0

I need to change the direct access propriety to all my databases because I need to automatically back them up with a plugin in an external cloud in order to restore my websites faster if my website will be hacked. The problem is that I'm using an older version of an hosting service (godaddy) so my databases are in mysql version 5.5 but in order to make them accessible I need the 5.0 version. So when I import them in the older version I receive this error:

1273 - Unknown collation: 'utf8mb4_unicode_ci'



Does anyone have experienced a situation like that?

Answer

utf8mb4 is not supported in MySQL 5.0.

You could change your database to use utf8 on 5.5, before you back it up, so you could restore it on MySQL 5.0.

Use ALTER TABLE and ALTER DATABASE. Remember you have to change the database default character set, the table default character set for each table, and also the character set for each string column in each table!

ALTER DATABASE dbname DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci;
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- repeat for each table

But in my humble opinion, it's ridiculous to use a hosting service or any environment that won't upgrade their MySQL. Version 5.0 is not supported anymore.

Even MySQL 5.1 is newer, and it's not supported anymore either. The documentation for both 5.0 and 5.1 has been taken down from their website.

You're really reaching back into history to use MySQL 5.0. You're talking about software that is two generations past end-of-life.

Switch hosting providers.

Comments