Hi all,
My migrations run fine in development (SQLite), but when I try to deploy in production, I get MySQL errors about the foreign keys being integers when the primary keys are bigints:
ActiveRecord::MismatchedForeignKey: Column `user_id` on table `topics` does not match column `id` on `users`, which has type `bigint`. To resolve this issue, change the type of the `user_id` column on `topics` to be :bigint. (For example `t.bigint :user_id`). (ActiveRecord::MismatchedForeignKey)
Original message: Mysql2::Error: Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_rails_c0bdb5458f' are incompatible.
/rails/db/schema.rb:272:in `block in <main>'
/rails/db/schema.rb:13:in `<main>'
I do not control the key type in the migrations:
t.references :user, null: false, foreign_key: true
Rails 8.0.0, Ruby 3.3.5, MySQL 8.0
Any pointers?
I believe you can set the type of the FK
t.references(:user)
t.belongs_to(:supplier, foreign_key: true)
t.belongs_to(:supplier, foreign_key: true, type: :integer)
In this case setting it to Bigint. Also, I’d consider using MYSQL Locally also so both systems are as similar as posible
I don’t get it. Why would rails create IDs as bigint and references as integers?
If that was the case for everybody, that error would be all over Google.
I take your point on running MySQL locally, I’ll do that ASAP. But now, I expect to have the same issue locally. Any other ideas on how to fix this?
I think this is related to the adapter you are using. Locally it is SQLite, and the id is set to an integer whereas in production it will create the id field as bigint because it is a MySQL adapter See Active Record Basics — Ruby on Rails Guides
You were right, the dual adapter was the problem.
I updated database.yml to use a local mysql server, removed the sqlite gem and deleted schema.rb
Running rails db:migrate created a new schema.rb with correct datatypes 
2 Likes