MySQL Mysql2::Error: 不正确的字符串值

Question

提问by Trt Trt

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.

我有一个在生产模式下运行的 rails 应用程序，但是当用户试图保存记录时，今天突然出现了这个错误。

Mysql2::Error: Incorrect string value

More details (from production log):

更多细节（来自生产日志）：

Parameters: {"utf8"=>"a<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显示这个：

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 ?

另外：这个问题可以用javascript解决吗？在发送之前转换它？

Thanks

谢谢

Answer 1

回答by Chuanpin Zhu

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

问题是由您的 mysql 服务器端的字符集引起的。您可以手动配置，如：

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

回答by Besi

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

我按照这篇博文设法存储了表情符号（占用 4 个字节）：

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 byteswhen 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 mysql2adapter about it:
development:
  adapter: mysql2
  database: db
  username: 
  password:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci
Hope this helps someone!

Rails 4、MySQL 和表情符号 ( Mysql2::Error: Incorrect string value error.)
您可能认为，当您指定字符集为utf-8. 然而，可悲的是，你错了。问题是 utf8 字符集存储在 VARCHAR 列中时会占用3 个字节。另一方面，表情符号字符占用4 个字节。
解决方案分为两部分：
更改表和字段的编码：
ALTER TABLE `[table]` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
MODIFY [column] VARCHAR(250)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
告诉mysql2适配器：
development:
  adapter: mysql2
  database: db
  username: 
  password:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci
希望这可以帮助某人！

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

回答by mfazekas

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

您可以使用这样的迁移将您的表转换为 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

回答by dtelaroli

If you want store emoji, you need:

如果你想要商店表情符号，你需要：

1) Create a migration (thanks @mfazekas)

1）创建迁移（感谢@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)

2) 将 rails 字符集更改为 utf8mb4（感谢 @selvamani-p）

production:
  encoding: utf8mb4

References:

参考：

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

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

Answer 5

回答by shilovk

Need to change CHARACTER SETand COLLATEfor already created database:

需要更改CHARACTER SET和COLLATE已创建的数据库：

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

回答by Paul Marclay

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 7

回答by mrturtle

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

刚刚发现并感谢@mfazekas 的回答。我在迁移中做了两个更改：一个是为了适应从 connection.current_database 中删除（至少在 Rails 5 中）和在不使用 MySQL 的情况下跳过 SQL 语句的能力（我仍然在开发中使用 SQLite，但需要运行迁移）。

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 8

回答by Dhepthi

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

从数据库中获取数据时，这似乎是一个编码问题。尝试将以下内容添加到您的 database.yml 文件中

   encoding: utf8

Hope this solves your issue

希望这能解决您的问题

MySQL Mysql2::Error: 不正确的字符串值

提问by Trt Trt

回答by Chuanpin Zhu

回答by Besi

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

Change the encoding of your table and fields:

Tell the `mysql2`adapter about it:

Rails 4、MySQL 和表情符号 ( `Mysql2::Error: Incorrect string value error.`)

更改表和字段的编码：

告诉`mysql2`适配器：

回答by mfazekas

回答by dtelaroli

回答by shilovk

回答by Paul Marclay

回答by mrturtle

回答by Dhepthi

相关推荐

最近更新

标签

MySQL Mysql2::Error: 不正确的字符串值

提问by Trt Trt

回答by Chuanpin Zhu

回答by Besi

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

Change the encoding of your table and fields:

Tell the mysql2adapter about it:

Rails 4、MySQL 和表情符号 ( Mysql2::Error: Incorrect string value error.)

更改表和字段的编码：

告诉mysql2适配器：

回答by mfazekas

回答by dtelaroli

回答by shilovk

回答by Paul Marclay

回答by mrturtle

回答by Dhepthi

相关推荐

MySQL XAMPP MySQL密码设置（在PHPMYADMIN中不能输入）

MySQL 批量插入或更新

MySQL 本机表'performance_schema'。'???' 结构错误

使用 MySQL 触发器插入新记录后更新表列

相关推荐

最近更新

标签

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

Tell the `mysql2`adapter about it:

Rails 4、MySQL 和表情符号 ( `Mysql2::Error: Incorrect string value error.`)

告诉`mysql2`适配器：