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

Question

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

Duplicated [https://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors](https://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors) — YouneL, Nov 04 '17 at 11:48
what's the character_set for that specific column? run this `SHOW FULL COLUMNS FROM table_name;` and past the output. — Ali, Nov 04 '17 at 11:48

score 2 · Accepted Answer · answered Nov 04 '17 at 12:04

2

I think that are 2 things that you can check...

1) email content charset

what is the email content charset... utf-8 too?
if not, did you tried to convert it to utf-8?

2) sql mode - there are some default changes from MySql 5.5 to 5.7

more info you can find : - https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html - https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html

answered Nov 04 '17 at 12:04

shushu304

1,506
1
7
15

Disabling the stric mode as pointed on serverpilot worked for me! – petekaner Nov 04 '17 at 12:09
@petekaner the only problem with that is the fact that the server with strict SQL mode *enabled* is the one that is *correct*. The data is invalid and you're masking the real problem (and the column may in fact be blank). `0xC7` is not a valid character representation in utf8, but it's the latin1 representation of `Ç`. The problem is in your code, not your server. – Michael - sqlbot Nov 04 '17 at 20:39

score 0 · Answer 2 · answered Nov 05 '17 at 01:33

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.

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

2 Answers2