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 ?

Did you ever figure this out? – stephenmurdoch Sep 18 '19 at 16:19 — stephenmurdoch, Sep 18 '19 at 16:19
@stephenmurdoch unfortunately not – Andrew Sep 19 '19 at 21:11 — Andrew, Sep 19 '19 at 21:11

score 3 · Answer 1 · answered Sep 24 '19 at 14:58

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:

score 0 · Answer 2 · answered Sep 15 '22 at 10:20

I think this is removed because it's the default. I could find a reference in the MySQL docs that says:

For example, the default collations for latin1 and utf8 are latin1_swedish_ci and utf8_general_ci, respectively.

So having CHARSET=utf8 already implies COLLATE=utf8_unicode_ci unless otherwise specified.

So it is safe to commit this change.

COLLATE=utf8_unicode_ci getting removed from schema.rb after migrate

2 Answers2