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! !! !!
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)
--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.
--Check with rails db: migrate: status → It is down --Again, rails db: migrate → error --rails db: migrate: reset → Error * The above is this screen!
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".
-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
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.
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.)
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.
〇〇@〇〇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) =======================
〇〇@〇〇noMacBook-Air devise_app % rails db:migrate:status
database: devise_app_development
Status Migration ID Migration Name
--------------------------------------------------
down 20200925212154 Devise create users
〇〇@〇〇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)
default: &default
adapter: mysql2
encoding: utf8
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
password:
socket: /tmp/mysql.sock
〇〇@〇〇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
== 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) =======================
〇〇@〇〇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
〇〇@〇〇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
〇〇@〇〇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
〇〇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
〇〇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
〇〇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) =======================
〇〇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.
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.