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

Answer 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
• Did not work until I applied the fix described above.

Answer 3

You can use a migration like this to convert your tables to utf8:

class ConvertTablesToUtf8 < ActiveRecord::Migration
  def change_encoding(encoding,collation)
    connection = ActiveRecord::Base.connection
    tables = connection.tables
    dbname =connection.current_database
    execute <<-SQL
      ALTER DATABASE #{dbname} CHARACTER SET #{encoding} COLLATE #{collation};
    SQL
    tables.each do |tablename|
      execute <<-SQL
        ALTER TABLE #{dbname}.#{tablename} CONVERT TO CHARACTER SET #{encoding} COLLATE #{collation};
      SQL
    end
  end

  def change
    reversible do |dir|
      dir.up do
        change_encoding('utf8','utf8_general_ci')
      end
      dir.down do
        change_encoding('latin1','latin1_swedish_ci')
      end
    end
  end
end

Answer 4

If you want to the store emoji, you need to do the following:

1. Create a migration (thanks @mfazekas)

   class ConvertTablesToUtf8 < ActiveRecord::Migration def change_encoding(encoding,collation) connection = ActiveRecord::Base.connection tables = connection.tables dbname =connection.current_database execute <<-SQL ALTER DATABASE #{dbname} CHARACTER SET #{encoding} COLLATE #{collation}; SQL tables.each do |tablename| execute <<-SQL ALTER TABLE #{dbname}.#{tablename} CONVERT TO CHARACTER SET #{encoding} COLLATE #{collation}; SQL end end

   def change reversible do |dir| dir.up do change_encoding('utf8mb4','utf8mb4_bin') end dir.down do change_encoding('latin1','latin1_swedish_ci') end end end end

2. Change rails charset to utf8mb4 (thanks @selvamani-p)

   production: encoding: utf8mb4

References:

https://stackoverflow.com/a/39465494/1058096

https://stackoverflow.com/a/26273185/1058096

Answer 5

Need to change CHARACTER SET and COLLATE for already created database:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Or it was necessary to create a database with pre-set parameters:

CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 6

It seems like an encoding problem while getting data from database. Try adding the below to your database.yml file

   encoding: utf8 

Hope this solves your issue

Answer 7

Also, if you don't want to do changes in your database structure, you could opt by serializing the field in question.

class MyModel < ActiveRecord::Base
  serialize :content

  attr_accessible :content, :title
end