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.

Answer 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.