Hi,
I had this problem with 4-byte encoded UTF8 values:
Mysql2::Error: Incorrect string value: ‘\xF0\x9F\x98\x80’ for column ‘remark’ at row 1: INSERT INTO order_notes
(change_type
, created_at
, created_by
, order_id
, remark
) VALUES (‘BEMERKUNG’, ‘2017-08-23 15:56:12’, ‘Martin’, 118360, ‘test with smiley ’) ActiveRecord::StatementInvalid
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in
query' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in
block in execute’
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract_adapter.rb:280:in block in log' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activesupport-3.2.22.2/lib/active_support/notifications/instrumenter.rb:20:in
instrument’
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract_adapter.rb:275:in log' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in
execute’
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/mysql2_adapter.rb:213:in execute' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/mysql2_adapter.rb:236:in
exec_insert’
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/database_statements.rb:90:in insert' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in
insert’
/home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord-3.2.22.2/lib/active_record/relation.rb:66:in `insert’
…
I also got this error when I executed the query in phpMyAdmin directly.
So I migrated my database from utf8 / utf8_general_ci to utf8mb4 / utf8mb4_unicode_ci according to https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc:
For each database: ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
For each table: ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
For each column: ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
And set character_set_server=utf8mb4
``
in /etc/mysql/mysql.conf.d/mysqld.cnf.
After “etc/init.d/mysql restart”, that solved the problem for queries in phpMyAdmin, but not for my Rails application in development mode. And none of the following did help:
- sudo service apache2 restart
- rake tmp:clear
- mysql> RESET QUERY CACHE
- mysql> FLUSH QUERY CACHE
- restarting RubyMine (including the Rails server)
- restarting my computer What do I miss?
I have:
- Ubuntu 16.04 LTS, 64 bit
- Rails 3.2.22.2
- Ruby 1.9.3-p551
- Gems e.g.
- gem ‘mysql2’, ‘0.3.18’
- gem ‘i18n’, ‘0.7.0’
- gem ‘rack-utf8_sanitizer’, ‘1.3.2’
- gem ‘thin’, ‘1.6.3’