Rails ignores update to utf8mb4


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 :grinning:’) ActiveRecord::StatementInvalid /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- query' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- block in execute’ /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- block in log' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activesupport- instrument’ /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- log' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- execute’ /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- execute' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- exec_insert’ /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- insert' /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- insert’ /home/martin/.rvm/gems/ruby-1.9.3-p551/gems/activerecord- `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
  • restarting RubyMine (including the Rails server)
  • restarting my computer What do I miss?

I have:

  • Ubuntu 16.04 LTS, 64 bit
  • Rails
  • 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’

Found it…