CODEWITHSUNDEEP
mysqlruby-on-railsrubyemoji
A. Jasinski
A. Jasinski

Reputation: 143

Ruby on Rails - Emoji not saved in MySQL

I'm working on a Rails app where users can upload photos to their profiles. Every photo has a title, which is supposed to support emoji. Despite changing encoding of the table to utf8mb4 and modifying database.yml when i try to save a photo with emoji in its title MySQL returns an error 'incorrect string value'.

The app is working on Rails 5.0.0.1 with Ruby 2.3.0, MySQL is working on version 5.7.16.

Migration file:

class CreateUserPhotos < ActiveRecord::Migration[5.0]
  def change
    create_table :user_photos do |t|
      t.string  :title
      t.string  :filename, null: false
      t.integer :visibility, null: false, default: 0
      t.belongs_to :user, foreign_key: true, index: true

      t.timestamps
    end

    reversible do |dir|
      dir.up do
        execute "ALTER TABLE `user_photos` CHANGE `title` `title` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
      end

      dir.down do
        execute "ALTER TABLE `user_photos` CHANGE `title` `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;"
      end
    end
  end
end

database.yml file:

production:
  adapter: mysql2
  encoding: utf8mb4
  reconnect: false
  database: app_db
  pool: 5
  username: app_user
  password: app_password
  host: localhost
  socket: /var/run/mysqld/mysqld.sock
  collation: utf8mb4_unicode_ci

Upvotes: 8

Views: 2517

Answers (1)

Vasili
Vasili

Reputation: 905

Try to set table's collation to utf8mb4_bin instead of utf8mb4_unicode_ci:

ALTER TABLE user_photos CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

And also change collation property value in database.yml

Upvotes: 5

Related Questions