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

If you want store emoji, you need:

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 2

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

Answer 3

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 4

Just came upon this and appreciate @mfazekas's answer. I made two changes in the migration: one to accommodate the removal of from connection.current_database (at least in Rails 5) and the ability to skip the SQL statements if not using MySQL (I still use SQLite in development but need the migration to run).

class ConvertTablesToUtf8 < ActiveRecord::Migration[5.2]
  def change_encoding(encoding,collation)
    # Allow for different adapter in different environment
    return unless ActiveRecord::Base.connection_config[:adapter] == "mysql"
    tables = connection.tables
    dbname = ActiveRecord::Base.connection_config[: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 5

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 6

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

查看更多

0人赞 添加讨论(0) 举报