Rober Rober - 3 months ago 6
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

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.

Comments