Mysql2::Error: Incorrect string value

问题:

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

回答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

回答2:

I managed to store emojis (which take up 4 bytes) by following this blog post:

Rails 4, MySQL, and Emoji (Mysql2::Error: Incorrect string value error.)

You might think that you’re safe inserting most utf8 data in to mysql when you’ve specified that the charset is utf-8. Sadly, however, you’d be wrong. The problem is that the utf8 character set takes up 3 bytes when stored in a VARCHAR column. Emoji characters, on the other hand, take up 4 bytes.

The solution is in 2 parts:

Change the encoding of your table and fields:

ALTER TABLE `[table]` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
MODIFY [column] VARCHAR(250)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

Tell the mysql2 adapter about it:

development:
  adapter: mysql2
  database: db
  username: 
  password:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

Hope this helps someone!

Then I had to restart my app and it worked. Please note that some emojis will work without this fix, while some won't:

• ➡️ Did work