Mysql2::Error: Incorrect string value

Question

I have a rails application running on production mode, but all of the sudden this error came up today when a user tried to save a record.

Mysql2::Error: Incorrect string value 

More details (from production log):

Parameters: {"utf8"=>"â<9c><93>" ...   Mysql2::Error: Incorrect string value: '\xC5\x99\xC3\xA1k   Mysql2::Error: Incorrect string value: '\xC5\x99\xC3\xA1k  

Now I saw some solutions that required dropping the databases and recreating it, but I cannot do that.

Now mysql shows this:

mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name            | Value                      | +--------------------------+----------------------------+ | 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/ | +--------------------------+----------------------------+ 8 rows in set (0.04 sec) 

What is wrong and how can I change it so I do not have any problems with any characters?

Also: Is this problem solvable with javascript? Convert it before sending it ?

Thanks

Answer 1

the problem is caused by charset of your mysql server side. You can config manually like:

ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8 

or drop the table and recreate it like:

rake db:drop rake db:create rake db:migrate 

references:

https://stackoverflow.com/a/18498210/2034097

https://stackoverflow.com/a/16934647/2034097

UPDATE

the first command only affect specified table, if you want to change all the tables in a database, you can do like

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci; 

reference:

https://stackoverflow.com/a/6115705/2034097