Why is my table's encoding wrong?

Question

I'm using Rails 5 and MySQL 5.7. I have the following in my database.yml:

default: &default
  adapter: mysql2
  pool: 5
  encoding: utf8mb4
  charset: utf8mb4
  collation: utf8mb4_unicode_ci
  socket: /tmp/mysql.sock

development:
  <<: *default
  database: [...]
  username: [...]
  password: [...]

I run rake db:reset to recreate my database and I run show create table users and I see CREATE TABLEusers([...]) ENGINE=InnoDB DEFAULT CHARSET=utf8.

Why does my CHARSET=utf8 and how can I fix it (make it equal utf8mb4)? Thanks.

score 3 · Accepted Answer · answered Aug 16 '16 at 00:13

rake db:reset is wrong. rake db:drop && rake db:create && rake db:migrate is correct.

The reset just created the tables from the already stored schema which had the wrong charset and collation. But re-creating them from the migrations created them with the new charset and collation.

score 1 · Answer 2 · answered Aug 15 '16 at 23:52

1

Be explicit when you create a table:

CREATE TABLE users (
        name VARCHAR(99)  NOT NULL,
        ...
    ) ENGINE=InnoDB  DEFAULT CHARACTER SET utf8mb4;

Another technique is to ignore the table default, and be explicit on each column:

CREATE TABLE users (
        name VARCHAR(99)  CHARACTER SET utf8mb4  NOT NULL,
        ...
    ) ENGINE=InnoDB;

Probably what happened is that you specified the charset in neither place, but the DEFAULT on the database was utf8.

answered Aug 15 '16 at 23:52

Rick James

135,179
13
127
222

Actually the default on the database is utf8mb4. `| character_set_database | utf8mb4` But Rails is not respecting this. It's a Rails question as much as anything; I can be explicit every time but I don't want to. – nc. Aug 15 '16 at 23:54
Does Rails create the table for you? (I don't know RoR.) – Rick James Aug 16 '16 at 00:08
yup, it does create the table for you, if you use the usual workflow. See my answer below for more info. – nc. Aug 16 '16 at 00:13
Thanks for the info. – Rick James Aug 16 '16 at 00:21

score 1 · Answer 3 · answered Sep 23 '20 at 18:41

1

The problem is db:reset calls db:schema:load. If your db/schema.rb file has bad encoding values, it will load up the bad stuff. You can fix your db/schema.rb to have correct encoding before running db:reset

answered Sep 23 '20 at 18:41

konyak

10,818
4
59
65

Why is my table's encoding wrong?

3 Answers3