[Error] Mysql2 :: Error: Specified key was too long; max key length is 767 bytes

Error memorandum

error contents

When I did rails db: migrate, I got the following error statement. (Mysql error)

Mysql2::Error: Specified key was too long; max key length is 767 bytes

The error is The specified key is too long. Adding various columns to the table would exceed the key limit of 767 bytes. This error will occur.

Cause

config/database.yml

  default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

I get an error because of the description ʻencoding: utf8mb4`.

Solution

Rewrite to ʻencoding: utf8`.

config/database.yml

  default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

I don't know the difference between utf8mb4 and utf8, but it seems that utf8mb4 can store 1 to 4 bytes and utf8 can store 1 to 3 bytes. Therefore, with utf8, pictograms etc. cannot be saved, but the capacity may increase instead. I guessed.

If you have already created the database with rails db: create,

% rails db:drop
% rails db:create

Or

% rails db:reset

So, we need to recreate the database again.

in conclusion

If you have any mistakes, I would appreciate it if you could point out. Thank you for reading!