Mysql2::Error: Incorrect string value
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
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.
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