COLLATE=utf8_unicode_ci getting removed from schema.rb after migrate

Question

Running rails 5.0.2

The tables in our schema.rb in source control seem to mostly have the format:

create_table "app_files", 
    force: :cascade, 
    options: "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" 
do |t|

Note the COLLATE=utf8_unicode_ci" at the end.

When I run migrations the generated schemaa.rb is mostly the same but chops off COLLATE=utf8_unicode_ci" from those lines so it now looks like:

create_table "app_files", 
    force: :cascade, 
    options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" 
do |t|

Based on other SO posts I've tried two things to fix this

1) in my /etc/mysql/my.cnf, I added:

[mysqld]
character-set-server  = utf8
collation-server = utf8_unicode_ci

2) in my database.yml ive added collation: utf8_general_ci to all of the relevant environments

I then restarted mysql, dropped, created and migrated my db but still the collate line disappears.

Any thoughts on what configuration I need to change to have that bit autogenerated ?

Answer 1

I had this issue, and resolved it by adding encoding and collation settings into my database.yml config file.

I wonder if yours are being ignored because they're the MySQL defaults. You should try using utf8mb4 and utf8mb4_unicode_ci, with the added benefit that they support 4-byte characters such as certain emoji characters.

Here's the default section of my database.yml file for reference:

default: &default
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: