Mysql2::Error: Incorrect string value: '\xE2\x80\xA8\x09

Question

I have a rails application. Sometimes, when a user writes in a text field and a query is executed to update this field in the MySQL database, I get this error log:

UPDATE boats SET description = 'Vive la experiencia única de navegar abordo de un clásico de madera de lujo como Mako. 
 Te emocionará.', updated_at = '2015-03-10 20:10:32' WHERE boats.id = 1

    E, [2015-03-10T20:10:32.223430 #20343] ERROR -- : Mysql2::Error: Incorrect string value: '\xE2\x80\xA8\x09Te...' for column 'description' at row 1: UPDATE boats SET description = 'Vive la experiencia única de navegar abordo de un clásico de madera de lujo como Mako. 
        Te emocionará.', updated_at = '2015-03-10 20:10:32' WHERE boats.id = 1

NOTE: Sorry, I´m not able to put the code above as code. There must be a special character.

I would like the user could add any character without errors.

I have a development and production environment. The error is only happening in production.

I saw this post that looks the same problem as mine: Mysql2::Error: Incorrect string value

I run this query show variables like 'char%'; to check the database character config and: Development:

'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'
'character_sets_dir', '/usr/local/Cellar/mysql/5.6.19/share/mysql/charsets/'

Production:

'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'
'character_sets_dir', '/usr/share/mysql/charsets/'

So, I executed ALTER DATABASE yanpyprod CHARACTER SET utf8 COLLATE utf8_general_ci; to update my database character set to utf8.

However, after the charecter set changed to utf8, I still get the same error.

Solution 1

It works if you run ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8 instead of the query to updated character set in the database above.

The solution is the the attached post, at the very end.

Solution 2

There are two ways to overcome this

1. Change the default character set of the table
2. Change the default character set of the specific field

Options 1
As in accepted answer:

ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8;

Option 2
If you need to keep the table's default character set, then you can modify the specific field or set of fields which are having the issue with.

I have got the same issue with two fields in a table, and those fields are storing content from rich-text fields values. Those fields are used to enter HRML as well as some contents which is causing the error.

So if the issue is with only in a field or set of fields you can set the character set of that specific field, following type of ALTER query can be used set the character set of a field.

ALTER TABLE your_db_name.table_name MODIFY COLUMN column_name text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

NOTE: Update the relevant names and values according to your requirements fitting to the environment. The only thing needs to highlight here is CHARACTER SET utf8

Solution 3

Issue for me was that for my DB connections i was using the utf8mb4 charset but the db tables were set to latin1. Once I matched these up then there were no issues.

I don't think you would have to convert your tables, just make sure the connection uses the same charset as the db.

Comments

[Yunnosch]

@Jayr Giving feedback by using your voting privilege is appreciated. Failing to do so and instead slipping with the language in a comment is not.