題名通り今回はMySQLで躓きすぎたので備忘録として、今後もしまた同じ目に遭ったとしても対処できるように備忘録として残す事にしました。

#使用環境
Ubuntu : 18.04.3 LTS \n \l
Rails : 6.1.3.2
MySQL : Ver 14.14 Distrib 5.7.34
Vagrant : 2.2.17

#エラー内容
今回のエラーは、MySQLの環境構築の完了後マイグレーションしようとした時に出てきたエラーになっております。

#結論
結論として、今回のエラーは「schema.rb」の環境がどうやら悪さをしていたみたいなので、今まで使用していた「schema.rb」を消して新しい「schema.rb」を作る事によって、無事MySQLのエラーをクリアすることができました。

#エラー文の全容

マイグレーションしようとしたら出てきたエラー

ターミナル

$ rails db:migrate
== 20200209055424 DeviseCreateUsers: migrating ================================
-- create_table(:users, {})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

you can't define an already defined column 'name'.
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:36:in `block in change'
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:5:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
ArgumentError: you can't define an already defined column 'name'.
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:36:in `block in change'
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:5:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

まず「devise_create_users」の36行目と5行目のカラム'name'が二つありますよというエラー文になります。
ということはカラム'name'を消せば解決です。

2 rails db:migrate:statusでマイグレーションの状態を確認する

ターミナル

$ rails db:migrate:status
database: hoge_development
 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20200208124233  Create hoges
  down    20200209055424  Devise create users
  down    20200220052712  Add hoges tosees
  down    20201207042223  Add hoge to hoges
  down    20201210015310  Add user id to hoges
  down    20201222045544  Create hoges
  down    20210303012038  Add hoge to users
  down    20210714052813  Create hoges

一応、rails db:migrate:statusで確認をしてからカラム'name'を消しました。
(statusがdownだと手動で直すことができます)

3.再びrails db:migrateをする

ターミナル

$ rails db:migrate
== 20200209055424 DeviseCreateUsers: migrating ================================
-- create_table(:users, {})
   -> 0.0673s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0530s
-- add_index(:users, :email, {:unique=>true})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Duplicate key name 'index_users_on_email'
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:41:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Duplicate key name 'index_users_on_email'
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:41:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Duplicate key name 'index_users_on_email'
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:41:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

成功した！と思ったのも束の間、新たなエラーが、、、。
今度は、devise_create_users.rbの41行目とMySQLにエラー。。。

4.rails db:dropをしてみた
devise_create_users.rbの41行目は直しても、MySQLかぁ。。。と思ったので、一度dropしてみました。

ターミナル

$ rails db:drop
rails aborted!
ActiveRecord::NoEnvironmentInSchemaError: 

Environment data not found in the schema. To resolve this issue, run: 

        bin/rails db:environment:set RAILS_ENV=development

/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:drop => db:check_protected_environments
(See full trace by running task with --trace)

※rails db:dropとは一度データベースを消すときに使います。
「bin/rails db:environment:set RAILS_ENV=development」というのが出ましたが、今はやめときました。

5.rails db:createしてみた

ターミナル

$ rails db:create
Database 'hoge_development' already exists
Database 'hoge_test' already exists

既に存在している模様

6.もう一度rails db:migrateしてみた

ターミナル

$ rails db:migrate
== 20200209055424 DeviseCreateUsers: migrating ================================
-- create_table(:users, {})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'users' already exists
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:5:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'users' already exists
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:5:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Table 'users' already exists
/vagrant/hoge/db/migrate/20200209055424_devise_create_users.rb:5:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

若干変わったけど進展なし。。。

7.rails db:resetで実行

ターミナル

$ rails db:reset
rails aborted!
ActiveRecord::NoEnvironmentInSchemaError: 

Environment data not found in the schema. To resolve this issue, run: 

        bin/rails db:environment:set RAILS_ENV=development

/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:reset => db:drop => db:check_protected_environments
(See full trace by running task with --trace)

実行できない。。。（泣）
おのれschemaめ。。。

８. bin/rails db:environment:set RAILS_ENV=development

ターミナル

$ bin/rails db:environment:set RAILS_ENV=development
vagrant@ubuntu-bionic:/vagrant/hoge$

お！いけるか！

９.rails db:reset

ターミナル

$ bin/rails db:environment:set RAILS_ENV=development
vagrant@ubuntu-bionic:/vagrant/hoge$ rails db:reset
Dropped database 'hoge_development'
Dropped database 'hoge_test'
Created database 'hoge_development'
Created database 'hoge_test'
rails aborted!
ActiveRecord::MismatchedForeignKey: Column `hoge_id` on table `hoges` does not match column `id` on `hoges`, which has type `bigint(20)`. To resolve this issue, change the type of the `hoge_id` column on `comments` to be :bigint. (For example `t.bigint :hoge_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:reset => db:setup => db:schema:load
(See full trace by running task with --trace

...。

10.　念の為MySQLのバージョンを確認

ターミナル

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

１１. rails db:migrate

ターミナル

$ rails db:migrate
== 20200208124233 Createhoges: migrating ======================================
-- create_table(:hoges, {})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'hoges' already exists
/vagrant/hoge/db/migrate/20200208124233_create_hoges.rb:3:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'hoges' already exists
/vagrant/hoge/db/migrate/20200208124233_create_blogs.rb:3:in `change'
/vagrant/hege/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Table 'blogs' already exists
/vagrant/hoge/db/migrate/20200208124233_create_hoges.rb:3:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

エラーの内容が変わりました。
MySQLの何かが悪さをしています。

１２. rails db:reset

ターミナル

$ rails db:reset
Dropped database 'hoge_development'
Dropped database 'hoge_test'
Created database 'hoge_development'
Created database 'hoge_test'
rails aborted!
ActiveRecord::MismatchedForeignKey: Column `hoge_id` on table `hoges` does not match column `id` on `hoges`, which has type `bigint(20)`. To resolve this issue, change the type of the `hoge_id` column on `hoges` to be :bigint. (For example `t.bigint :hoge_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:reset => db:setup => db:schema:load
(See full trace by running task with --trace)

どうやら一つはdatabase.ymlの入力がよろしくないようですね。

13.database.ymlを変更して再度rails db:reset

ターミナル

$ rails db:reset
Dropped database 'hoge_development'
Dropped database 'hoge_test'
Created database 'hoge_development'
Created database 'hoge_test'
rails aborted!
ActiveRecord::MismatchedForeignKey: Column `hoge_id` on table `hoges` does not match column `id` on `hoges`, which has type `bigint(20)`. To resolve this issue, change the type of the `hoge_id` column on `hoges` to be :bigint. (For example `t.bigint :hoge_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/schema.rb:57:in `block in <top (required)>'
/vagrant/hoge/db/schema.rb:13:in `<top (required)>'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:reset => db:setup => db:schema:load
(See full trace by running task with --trace)

変わらないな・・・。
最終手段でschemaを古いものを消して、新しくschemaを作る事にしました。

１４. 一度まっさらな状態にして$ rails db:migrate:statusを見てみた

ターミナル

$ rails db:migrate:status
Schema migrations table does not exist yet.

一応schemaファイルは無くなっていますね

１５. schemaファイルを作り直してbin/rails db:migrate

ターミナル

$ bin/rails db:migrate
== 20200208124233 Createhoges: migrating ======================================
-- create_table(:hoges, {})
   -> 0.0566s
== 20200208124233 Createhoges: migrated (0.0569s) =============================

== 20200209055424 DeviseCreateUsers: migrating ================================
-- create_table(:users, {})
   -> 0.0592s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0587s
-- add_index(:users, :reset_password_token, {:unique=>true})
   -> 0.0553s
== 20200209055424 DeviseCreateUsers: migrated (0.1741s) =======================

== 20200220052712 AddBlogsTosees: migrating ===================================
== 20200220052712 AddBlogsTosees: migrated (0.0000s) ==========================

== 20201207042223 AddNameToBlogs: migrating ===================================
-- add_column(:hoges, :name, :string)
   -> 0.1584s
== 20201207042223 AddNameToHoges: migrated (0.1588s) ==========================

== 20201210015310 AddUserIdToHoges: migrating =================================
-- add_column(:hoges, :user_id, :integer)
   -> 0.1395s
== 20201210015310 AddUserIdToHoges: migrated (0.1397s) ========================

== 20201222045544 Createhoges: migrating ===================================
-- create_table(:hoges, {})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Column `hoge_id` on table `hoges` does not match column `id` on `hoges`, which has type `bigint(20)`. To resolve this issue, change the type of the `hoge_id` column on `hoges` to be :bigint. (For example `t.bigint :hoge_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/vagrant/onsen/db/migrate/20201222045544_create_hoges.rb:3:in `change'
/vagrant/onsen/bin/rails:9:in `<top (required)>'
/vagrant/onsen/bin/spring:15:in `<top (required)>'

Caused by:
ActiveRecord::MismatchedForeignKey: Column `hoge_id` on table `hoges` does not match column `id` on `hoges`, which has type `bigint(20)`. To resolve this issue, change the type of the `hoge_id` column on `hoges` to be :bigint. (For example `t.bigint :hoge_id`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/migrate/20201222045544_create_hoges.rb:3:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'

Caused by:
Mysql2::Error: Cannot add foreign key constraint
/vagrant/hoge/db/migrate/20201222045544_create_hoges.rb:3:in `change'
/vagrant/hoge/bin/rails:9:in `<top (required)>'
/vagrant/hoge/bin/spring:15:in `<top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

おしい・・・！
どうやらhogesカラムのidをbigintに変えればいけそうな感じですね。
※（t.bigint :hoge_id）に変えればよい

16.修正して再度bin/rails db:migrate

ターミナル

$ bin/rails db:migrate
== 20201222045544 CreateHoges: migrating ===================================
-- create_table(:hoges, {})
   -> 0.0619s
== 20201222045544 CreatHoges: migrated (0.0620s) ==========================

== 20210303012038 AddProfileImageToUsers: migrating ===========================
-- add_column(:users, :profile_image, :string)
   -> 0.1556s
== 20210303012038 AddProfileImageToUsers: migrated (0.1557s) ==================

== 20210714052813 CreateHoges: migrating ==================================
-- create_table(:hoges)
   -> 0.0691s
== 20210714052813 CreateHoges: migrated (0.0694s) =========================

通った〜！！
疲れた。。。
MySQLは本当に面倒なので皆様も似たような現象がありましたら、「schema.rb」を見てみてください。

見てくださった皆様、お付き合いありがとうございました。