[Mysql2] Mysql2 :: Error: Specified key was too long; max key length is 767 bytes → Solved by creating a new file with character restrictions

About the outline

I understood this error as "Mysql2 can store up to 767 bytes of character data! You can't migrate to Mysql as it is!".

Based on that error, this article has practiced the following two methods.

-How to "create a new file with character restrictions" while database.yml is "utf8mb4" (Thanks to @ terufumi1122's article, I was able to solve it) --How to rewrite database.yml to "utf8" without creating "new file with character restrictions" (I learned that this method should not be done)

The most important thing on top of that is when using MySQL. It means "whether or not you can use pictograms is decided when defining requirements."

Let's go into the details! !! !!

About the error content

The error "Mysql2 :: Error: Specified key was too long; max key length is 767 bytes" occurred.

【Terminal】

〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:reset                          
Dropped database 'devise_app_development'
Dropped database 'devise_app_test'
Created database 'devise_app_development'
Created database 'devise_app_test'
== 20200925212154 DeviseCreateUsers: migrating ================================
-- create_table(:users)
   -> 0.0174s
-- add_index(:users, :email, {:unique=>true})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Specified key was too long; max key length is 767 bytes
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:39:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:39:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
Mysql2::Error: Specified key was too long; max key length is 767 bytes
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:39:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:migrate:reset => db:migrate
(See full trace by running task with --trace)

About the situation where the error occurred

environment#####

--Using Rails 6.0.0 --Mysql2 is used for the database (application is Sequel Pro) --Use a gem called devise (so the model was created with rails g devise user) --In devise, I was trying to create a column of string type (example: t.string: email) in the migration file. --When I created a migration file and executed rails db: migrate, an error occurred.

At first I couldn't understand the situation and did more of the following:

--Check with rails db: migrate: status → It is down --Again, rails db: migrate → error --rails db: migrate: reset → Error * The above is this screen!

Before going to the solution, what is the character code in the first place? What are "utf8" and "utf8mb4"? What is a byte?

I didn't understand the concept, and I understood it as follows.

――The character code is the code assigned to the character, such as No. 1 for "A" and No. 2 for "I". (Actually, it seems to be more complicated by using base numbers etc ...)

―― “Utf8” is the most popular character code in the world. "Utf8" expresses characters in 1 to 4 bytes, but MySQL can only handle up to 3 bytes.

--"Utf8mb4" is a character code for handling in the database MySQL. In Mysql, pictograms correspond to 4 bytes of "utf8", so pictograms can only be handled with "utf8mb4".

--In MySQL, the maximum number of characters that can be saved is "255 characters".

I quoted from here

-About the difference between "utf8" and "utf8mb4" -What is the character code -Maximum key length of single column index is 767 bytes -What is VARCHAR type in Mysql -Default string value in Rails up to 255 characters

What I understood by combining the above

In other words, in MySQL

--utf8 is the character code that handles characters, and utf8bm4 is the character code that handles pictograms.

The number of characters that can be

--Since "utf8" uses "3 bytes", "767 bytes ➗ 3 bytes = up to 255 characters"

--Since "utf8mb4" uses "4 bytes", "767 bytes ➗ 4 bytes = up to 191 characters"

Since the default value for string is 255 characters

--"3 bytes ❌ 255 characters = 765 bytes" (utf8)

--"4 bytes ❌ 255 characters = 1020 bytes" (utf8bm4): point_right_tone3: It exceeds 767 bytes that can be saved by MySQL! !! : point_right_tone3: This time I'm in this state and it's an error

I understand that.

Then, go to the error resolution method

Now that the cause of the error has been clarified, let's talk about the essential solution.

As I mentioned at the beginning,

-How to "create a new file with character restrictions" while database.yml is "utf8mb4" (Article by @ terufumi1122)

First of all, I solved it by the above method! !! !! (Thank you)

If you give an overview about the solution,

【problem】 "4 bytes ❌ 255 characters = 1020 bytes" (utf8bm4): point_right_tone3: It exceeds 767 bytes that can be saved by MySQL! !! : point_right_tone3: This time I'm in this state and it's an error

[Thinking about it] Then, let's set the upper limit of the number of characters to 191 characters so that it does not exceed 767 bytes ("4 bytes ❌ 191 characters = 764 bytes")! It is a method. (How to use utf8bm4 as it is so that you can use pictograms)

Create the following mysql.rb under config / initializer. (Including the code below, it is quoted from @ terufumi1122's article.)

config/initializer/mysql.rb
require 'active_record/connection_adapters/abstract_mysql_adapter'

module ActiveRecord
  module ConnectionAdapters
    class AbstractMysqlAdapter
      NATIVE_DATABASE_TYPES[:string] = { :name => "varchar", :limit => 191 }
    end
  end
end

After entering the above file

【Terminal】
rails db:migrate

When I did, it was successfully migrated! !! !! (When I looked at Sequel Pro, which is MySQL, there was a column.)

From there, what I considered

Well, I solved it safely, but I thought that I could think of the following way of thinking about the method that does not exceed 767 bytes for the problem.

【problem】 "4 bytes ❌ 255 characters = 1020 bytes" (utf8bm4): point_right_tone3: It exceeds 767 bytes that can be saved by MySQL! !! : point_right_tone3: This time I'm in this state and it's an error

[Thinking about it] Then, if you don't use pictograms in the application, why not change it to "3 bytes ❌ 255 characters = 765 bytes" (utf8)? In other words, instead of creating a file with 191 characters or less, you should rewrite the character code described in "config> database.yml" from "utf8bm4" to "utf8" and migrate! ??

I thought.

I will practice it from here, but from the conclusion, I learned that "it will be recreated from the model (it is not enough to rewrite the migration)". (That is, it is troublesome and difficult.)

"I can't rewrite the migration" means that a mysterious migration file (not instructed) appears with "rails db: migrate" and that migration file is unnecessary, so change it to a fictitious file and drop or reset it. But even if I try to erase it, it does not disappear.

I erased the model with "rails d devise user" and recreated it with "rails g devise user", and finally I got an application with "utf8" character code without error.

Now, practice! (The above consideration is practiced from the situation where the error was resolved earlier)

① Roll back once
〇〇@〇〇noMacBook-Air devise_app % rails db:rollback       
== 20200925212154 DeviseCreateUsers: reverting ================================
-- remove_index(:users, {:column=>:reset_password_token})
   -> 0.0099s
-- remove_index(:users, {:column=>:email})
   -> 0.0074s
-- drop_table(:users)
   -> 0.0040s
== 20200925212154 DeviseCreateUsers: reverted (0.0250s) =======================
② Check the migration status (it's down, so it's okay!)
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:status 

database: devise_app_development

 Status   Migration ID    Migration Name
--------------------------------------------------
  down    20200925212154  Devise create users

③ Execute migration (OK because the error could be reproduced!)
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate        
== 20200925212154 DeviseCreateUsers: migrating ================================
-- create_table(:users)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'users' already exists
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:5:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'users' already exists
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:5:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'

Caused by:
Mysql2::Error: Table 'users' already exists
/Users/〇〇/projects/devise_app/db/migrate/20200925212154_devise_create_users.rb:5:in `change'
/Users/〇〇/projects/devise_app/bin/rails:9:in `<top (required)>'
/Users/〇〇/projects/devise_app/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
④ Rewrite config> database.yml to utf8
default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock
⑤ Execute rails db: reset (good feeling!)
〇〇@〇〇noMacBook-Air devise_app % rails db:reset  
Dropped database 'devise_app_development'
Dropped database 'devise_app_test'
Created database 'devise_app_development'
Created database 'devise_app_test'
You have 1 pending migration:
  20200925212154 DeviseCreateUsers
Run `rails db:migrate` to update your database then try again.
⑥ Execute rails db: migrate (good feeling!)
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate
== 20200925212154 DeviseCreateUsers: migrarails db:migrateting ================================
-- create_table(:users)
   -> 0.0109s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0087s
-- add_index(:users, :reset_password_token, {:unique=>true})
   -> 0.0085s
== 20200925212154 DeviseCreateUsers: migrated (0.0283s) =======================
⑥ rails db: migrate: status (Oh? What is a 000 file?)
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:status 

database: devise_app_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             ********** NO FILE **********
   up     20200925212154  Devise create users
⑦ Since it has become "** NO FILE *", even if I modify the migration to a fictitious file (Sample) and roll back, 000 files do not go down
〇〇@〇〇noMacBook-Air devise_app % rails db:rollback STEP=2
〇〇@〇〇noMacBook-Air devise_app % 
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:status 

database: devise_app_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             Sample
  down    20200925212154  Devise create users
⑦ Reset and check the status, but 000 files do not go down
〇〇@〇〇noMacBook-Air devise_app % rails db:reset          
Dropped database 'devise_app_development'
Dropped database 'devise_app_test'
Created database 'devise_app_development'
Created database 'devise_app_test'
You have 1 pending migration:
  20200925212154 DeviseCreateUsers
Run `rails db:migrate` to update your database then try again.
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:status 

database: devise_app_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             ********** NO FILE **********
  down    20200925212154  Devise create users

⑧ Delete the model
〇〇noMacBook-Air devise_app % rails d devise user   
Running via Spring preloader in process 10337
      invoke  active_record
      remove    db/migrate/20200925212154_devise_create_users.rb
      remove    app/models/user.rb
      invoke    test_unit
      remove      test/models/user_test.rb
      remove      test/fixtures/users.yml
       route  devise_for :users
⑨ Remake the model
〇〇noMacBook-Air devise_app % rails g devise user    
Running via Spring preloader in process 10485
      invoke  active_record
      create    db/migrate/20200926073222_devise_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml
      insert    app/models/user.rb
       route  devise_for :users
⑩ Perform migration
〇〇noMacBook-Air devise_app % rails db:migrate        
== 20200926073222 DeviseCreateUsers: migrating ================================
-- create_table(:users)
   -> 0.0275s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0434s
-- add_index(:users, :reset_password_token, {:unique=>true})
   -> 0.0108s
== 20200926073222 DeviseCreateUsers: migrated (0.0821s) =======================
11 Check the status (finally done !!!!!!)
〇〇noMacBook-Air devise_app % rails db:migrate:status 

database: devise_app_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20200926073222  Devise create users

that's all.

At the end

I tried to practice the consideration, It turns out that to change the information in database.yml, you need to remodel the model instead of using a roll bag.

This time I didn't write much about the model, so it didn't affect me, If this is to recreate the table, column, record, migration, etc. including the model, it will be troublesome and difficult.

I learned that it is important to set the database.yml editing including utf8 after defining the requirements well (before doing "rails db: create" at the beginning).

After that, I considered a method that does not exceed "767 bytes" this time, but @ terufumi1122's article also said that it is allowed to exceed "767 bytes".

In order to resolve the error, I realized again that it is necessary to flexibly look at the application from various directions according to the purpose, and that the viewpoint of "WHY" is important to increase the number of withdrawals.

Thank you for watching. If you know the cause of the mystery file or if you have posted something wrong, please let me know.

Recommended Posts

[Mysql2] Mysql2 :: Error: Specified key was too long; max key length is 767 bytes → Solved by creating a new file with character restrictions
[Error] Mysql2 :: Error: Specified key was too long; max key length is 767 bytes