MySQL incorrect string value error on MySQL 5.7 but not on 5.5

user_2882899 0
Viewed 1450

In a PHP application I'm trying to save the content of an email on the database (table is utf8mb4 and collation utf8mb4_general_ci). When doing tests on my dev enviroment it works fine but on productions I keep getting errors like this one:

General error: 1366 Incorrect string value: '\xC7ALHO-...' for column 'content_html' at row 1

I checked and I realized I had MySQL 5.5 on dev and 5.7 on prod, I upgraded mysql on my dev and now I get the error on dev as well. The problem is I don't understand why I'm getting this errors, the content is a very standard email with nothing much but a header logo. Any idea why this is failing on 5.7 and not in 5.5 and if there's any workaround on this?

Update: here's the SHOW FULL COLUMNS of the table

ysql> SHOW FULL COLUMNS FROM received_email;
+-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field           | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id              | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| skill_id        | int(11)      | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| agent_id        | int(11)      | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| message_id      | longtext     | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| received_date   | datetime     | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| downloaded_date | datetime     | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| from_name       | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| from_email      | varchar(255) | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| subject         | longtext     | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| content_html    | longtext     | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| content_plain   | longtext     | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| recipient       | varchar(255) | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| created_at      | datetime     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at      | datetime     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| case_detail_id  | int(11)      | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
+-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

Thanks in advance

utf-8 character-encoding mysql php
2 Answers
user_88848920

State that your client has latin1 bytes. Do this in the connection string, or right afterwards via SET NAMES latin1.

MySQL will convert the C7 to the utf8 equivalent "on the wire".

Do not use any conversion functions, that is likely to only make things worse, or at least harder to unravel.

user_17668310
Related