Rober Rober - 1 year ago 193
MySQL Question

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

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download