Incorrect string value: '\xC2\x9Fe 10...' for column

Question

We have a Old 5.1 Mysql server running on server 2003. Recently we move to a newer environment with Mysql 5.6 and server 2008. Now on the new server we keep getting errors when inserting special chars like 'Ã'.

Now I have checked the source encoding and it is UTF-8. But the old Mysql server was configured as latin1(Server / tables / colonms) with collation latin_swedish_ci and we did not receive any errors on the old environment.

Now I have done some testing since we are not live on the new environment. I have tried setting all tables to tables / colonms as well as latin1. In both cases I keep getting these errors.

What I noticed is that on the old server the servers default char-set is latin1 and on the new server its utf-8. Could that be the problem? I find this very strange because the source is utf-8.

Is there maybe some option to handle this that could be turned on on the old environment? I'm not sure if something like that exists. I did compare the settings within the mysql admin tool and apart from the default char-set it looks the same.

EDIT:

SHOW VARIABLES LIKE 'char%';

Old server:

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          | *
| character_set_connection | utf8                                          | *
| character_set_database   | latin1                                        |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          | *
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |

New Server:

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8mb4                                       | *
| character_set_connection | utf8mb4                                       | *
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8mb4                                       | *
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |

As far as I understand from the article over at the MySQL site utf8mb4 is a super-set of utf8 this should not create a problem for encoding I think since they are basically identical on encoding right?

Answer 1

The old UTF-8 of MySQL was not real UTF-8. If you try "special" characters (japanese or chinese) you'll probably end up with squares or question marks on your old server.

Your new server is now really using UTF-8 (mb4 stands for multi-bytes 4). The server receives UTF-8 characters but, obviously, can not store UTF-8 characters because your table are not using UTF-8. Convert all the tables to UTF-8 and the database to UTF-8 and you'll solve your problem.

You can do this with :

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Don't forget to backup before.

Source : https://stackoverflow.com/a/6115705/1980659