CODEWITHSUNDEEP
mysqlcharacter-encodingcollation
JI-Web
JI-Web

Reputation: 533

Mysql character set and collations - UTF8mb4 520

Problem: mysqldump will not import due to missing collation

1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

The Mysql server is (from phpMyAdmin): 

Server version: 5.5.40 - MySQL Community 
Server charset: UTF-8 Unicode (utf8)
Database client version: libmysql - 5.0.95

This is a centos 5.11 server.

mysql> show variables where Variable_name like 'character\_set\_%' or Variable_n                            ame like 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_general_ci |
| collation_server         | utf8_general_ci |
+--------------------------+-----------------+
10 rows in set (0.00 sec)

I have local server (xampp) which has 

> Server version: 10.1.9-MariaDB - mariadb.org binary distribution
> Server charset: UTF-8 Unicode (utf8)  Database client version:
> libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id:
> 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $

This imports fine. Looking at the final tables that are imported I see that the collation for all tables is utf8mb4_unicode_ci. The same happens (i.e. as my local which imports correctly) when I upload to a public server

Server version: 5.6.29 - MySQL Community Server (GPL)
Database client version: libmysql - 5.1.73

Why does my local database handle the 520 collation whereas the centos box does not?

Any pointers in the right direction are appreciated. All uploads are performed via phpMyAdmin with the specs above. I know I can 'update' the SQL dump and remove the '520' reference and just replace with utf8mb4_unicode_ci but if you have to do this every time it's not very economical.

Upvotes: 0

Views: 2501

Answers (1)

Chris Wiegman
Chris Wiegman

Reputation: 382

utf8mb4_unicode_520_ci was not introduced until MySQL 5.6 so the older 5.5 version on your CentOS box would explain why you don't have it.

Upvotes: 3

Related Questions