This, if anything, is a note to future me. I have come across an issue a few times where Rails can be pretty bad at bubbling up the actual underlying cause of an error that MySQL is panicking about and I always have to spend an annoying amount of time debugging it. This is the error and stacktrace we get when we attempted to run a migration recently.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
Column `some_uuid` on table `some_table` does not match column `uuid` on `different_table`, which has type `varchar(36)`. To resolve this issue, change the type of the `some_uuid` column on `some_table` to be :string. (For example `t.string :some_uuid`).
Original message: Mysql2::Error: Cannot add foreign key constraint
/Users/tom.gamon/development/some_repo/db/migrate/20210818004750_add_foo_foreign_key.rb:4:in `change'
/Users/tom.gamon/development/some_repo/bin/rails:9:in `<top (required)>'
/Users/tom.gamon/development/some_repo/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Caused by:
ActiveRecord::MismatchedForeignKey: Column `some_uuid` on table `some_table` does not match column `uuid` on `different_table`, which has type `varchar(36)`. To resolve this issue, change the type of the `some_uuid` column on `some_table` to be :string. (For example `t.string :some_uuid`)
.Original message: Mysql2::Error: Cannot add foreign key constraint
/Users/tom.gamon/development/some_repo/db/migrate/20210818004750_add_foo_foreign_key.rb:4:in `change'
/Users/tom.gamon/development/some_repo/bin/rails:9:in `<top (required)>'
/Users/tom.gamon/development/some_repo/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Caused by:
Mysql2::Error: Cannot add foreign key constraint
/Users/tom.gamon/development/some_repo/db/migrate/20210818004750_add_foo_foreign_key.rb:4:in `change'
/Users/tom.gamon/development/some_repo/bin/rails:9:in `<top (required)>'
/Users/tom.gamon/development/some_repo/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)
|
However, the types of the columns do match. They are both varchar(36). Luckily, we can look under the hood at what the exception that MySQL itself is reporting.
First, we need to spin up MySQL console. For me in this scenario, that is the following command, but your mileage will vary depending on your local setup.
1
|
$ mysql some_repo_development -u root
|
Once your console is up, execute this command.
1
|
SHOW ENGINE INNODB STATUS;
|
You should see a ton of output, but if you search up you can find the heading LATEST FOREIGN KEY ERROR. This is a record of, you guessed it, the last foreign key error that MySQL encountered.
1
2
3
4
5
6
7
8
9
10
11
12
|
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-08-18 11:37:45 0x18905f000 Error in foreign key constraint of table some_repo_development/#sql-5e9_7:
FOREIGN KEY (`some_uuid`) REFERENCES `some_repo_development`.`different_table` (`uuid`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
|
We can see in this scenario the underlying complaint was that we didn’t have an index on the column we were referencing, but Rails reports it as a mismatched column type.