Rails ignores update to utf8mb4

rubyonrails-talk
1

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 :grinning:’) 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’
2

Found it…