Rails ignores update to utf8mb4

[Martin L.]

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'

[Martin L.]

Found it..

https://stackoverflow.com/questions/22464011/mysql2error-incorrect-string-value

Tell the mysql2 adapter about it:

development:
  adapter: mysql2
  database: db
  username:
  password:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci