Home>

I am developing using Rails, Docker, and MySQL.
The version is as follows.

Development environment
Ruby vresion 2.7.2
Rails version 6.0.3.4
MySQL version 5.7

I'm currently trying to create a new table using a foreign key
docker-compose run web rails db: migrate
If i do, you will get an error.
Would you please help me?

docker-compose run web rails db: migrateIf i hit, the following error will appear.

== 20201223141709 CreateBookcases: migrating ==================================
--create_table (: bookcases)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:
Column `user_id` on table` bookcases` does not match column `id` on` users`, which has type `bigint (20)`. To resolve this issue, change the type of the `user_id` column on` bookcases` to be: bigint. (For example `t.bigint: user_id`).
Original message: Mysql2 :: Error: Cannot add foreign key constraint
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `_query'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:131: in `block in query'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `query'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:201: in `block (2 levels) in execute'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb: 200: in `block in execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:722: in `block (2 levels) in log'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:721:in `block in log'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:712:in `log'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:199:in `execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/mysql/database_statements.rb:41:in `execute'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract/schema_statements.rb:315:in `create_table'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/mysql/schema_statements.rb:81:in `create_table'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:890:in `block in method_missing'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb: 858: in `block in say_with_time'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb: 858: in `say_with_time'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:879:in `method_missing'
/share-read/db/migrate/20201223141709_create_bookcases.rb:3:in `change'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb: 828: in `exec_migration'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:812: in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:811: in `block in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:471:in `with_connection'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:810:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1002:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb1310: in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1363: in `ddl_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1309: in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1281: in `block in migrate_without_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1280:in `each'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1280:in `migrate_without_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1229: in `block in migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1229: in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1061: in `up'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/migration.rb:1036: in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:86:in `block (3 levels) in<main>'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:84:in `each'
/usr/local/bundle/gems/activerecord-6.0.3.4/lib/active_record/railties/databases.rake:84:in `block (2 levels) in<main>'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands/rake/rake_command.rb:23:in `block in perform'/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/command.rb:48:in `invoke'
/usr/local/bundle/gems/railties-6.0.3.4/lib/rails/commands.rb:18:in `<main>'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:23:in `require'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:23:in `block in require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/loaded_features_index.rb:92: in `register'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `require_with_bootsnap_lfi'
/usr/local/bundle/gems/bootsnap-1.5.1/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:31:in `require'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:324:in `block in require'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:291:in `load_dependency'
/usr/local/bundle/gems/activesupport-6.0.3.4/lib/active_support/dependencies.rb:324:in `require'
bin/rails: 4: in `<main>'
Corresponding source code

Migration file

class CreateBookcases</pre>
<p><br />
<strong>schema.rb (only applicable model listed)</strong></p>
<pre><code>create_table "books", primary_key: "isbn", options: "ENGINE = InnoDB DEFAULT CHARSET = utf8mb4", force :: cascade do | t |
    t.string "title"
    t.string "author"
    t.string "url"
    t.string "image_url"t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.string "book_genre_id"
  end
  create_table "users", options: "ENGINE = InnoDB DEFAULT CHARSET = utf8mb4", force:: cascade do | t |
    t.string "name"
    t.string "email"
    t.string "password_digest"
    t.string "user_image"
    t.integer "gender"
    t.date "birthday"
    t.text "introduction"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end
What I tried

I tried the following three things, but they didn't work.
① Restart docker
docker-compose downWhendocker-compose up -dI tried, but it didn't work.

(2) Specify the type of foreign key in the migration file
I specified type: bigint, but it didn't work.

t.references: user, type: bigint, null: false, foreign_key: true
 t.references: book, type: bigint, null: false, foreign_key: true


② Database reset
docker-compose run web rails db: resetI got an error and couldn't do it well.

Since I want to use it as a foreign key, I have not changed the description of t.references.

If i have any questions, I would appreciate it if you could comment.
Thank you.

Supplementary information

I am using a Mac as my PC.

  • Answer # 1

    I tried the following additionally and solved it (I got the above error several times after asking a question, so I tried various things).
    If you have a similar problem, please refer to it.
    ① Delete the corresponding table by operating mysql directly with docker-compose run web rails db
    ② mysql/volumes /*** ***_development (*Delete the corresponding file (ibd or rfm file) in (your app name)
    ③ Delete the database file, do docker-compose run web rails: create and docker-compose run web rails db: migrate and start over.