We help IT Professionals succeed at work.

MySQL Collation

I am trying to import a .sql into a blank database in PHPMyAdmin
The Collation is utf8_unicode_ci

The database tables in the .sql file are:
ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I get a collation error telling me that the collation format is unrecognised

How do I proceed with this, please?
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
what's the version of your MySQL database?

Do you have the collation option utf8mb4_unicode_ci to be selected there?

if not mistaken there was some changes to the default collation in different version of MySQL, which changed from utf8 to utf8mb4

Author

Commented:
version 5.0.2-dev
Ryan ChongSoftware Team Lead

Commented:
Any intention to upgrade your MySQL 5.0.x to a higher version? MySQL 5.0.x is pretty obsolete now

Author

Commented:
Trouble is it's on a hosting site - Plesk control panel

Author

Commented:
The PHP version I am importing to is 7.3.1.4
That is where the error is

Author

Commented:
sql server version 5.5.55
phpMyAdmin 4.9.0.1

Author

Commented:
what happens if I just change the collation reference in the .sql file
Ryan ChongSoftware Team Lead

Commented:
are you able to create the blank database with collation utf8mb4_unicode_ci instead?

sql server version 5.5.55

you mean MySQL or SQL Server ver 5.5.55?

Author

Commented:
Mysql

Author

Commented:
How do I change the database collation
If I select another one in phpmyadmin it keeps reverting back

Author

Commented:
I change it in the Server connection collation in phpmyadmin
Just keeps reverting back
Fixer of Problems
Most Valuable Expert 2014
Commented:
The version of PHP isn't important because it doesn't respond to the COLLATE statement.  It sounds like you are on Shared hosting where you probably can't make permanent changes to things that are shared.  'utf8mb4' is not recognized in earlier versions of MySQL.  I was changing it to just 'utf8' when I was still using MySQL 5.0.  But you have to change all instances of 'utf8mb4' for that to work.

Author

Commented:
Perfect - now imported
Thanks all

Author

Commented:
Thanks again
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
You're welcome.