Fix the regex that extract mismatched foreign key information

The CI failure for `test_errors_for_bigint_fks_on_integer_pk_table` is
due to the poor regex that extract all ``` `(\w+)` ``` like parts from
the message (`:foreign_key` should be `"old_car_id"`, but `"engines"`):

https://travis-ci.org/rails/rails/jobs/494123455#L1703

I've improved the regex more strictly and have more exercised mismatched
foreign key tests.

Fixes #35294

2 files changed, 35 insertions, 20 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 569c146f92..246c6b5123 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -795,17 +795,27 @@ module ActiveRecord
         end
 
         def mismatched_foreign_key(message, sql:, binds:)
-          parts = sql.scan(/`(\w+)`[ $)]/).flatten
-          MismatchedForeignKey.new(
-            self,
+          match = %r/
+            (?:CREATE|ALTER)\s+TABLE\s*(?:`?\w+`?\.)?`?(?<table>\w+)`?.+?
+            FOREIGN\s+KEY\s*\(`?(?<foreign_key>\w+)`?\)\s*
+            REFERENCES\s*(`?(?<target_table>\w+)`?)\s*\(`?(?<primary_key>\w+)`?\)
+          /xmi.match(sql)
+
+          options = {
             message: message,
             sql: sql,
             binds: binds,
-            table: parts[0],
-            foreign_key: parts[1],
-            target_table: parts[2],
-            primary_key: parts[3],
-          )
+          }
+
+          if match
+            options[:table] = match[:table]
+            options[:foreign_key] = match[:foreign_key]
+            options[:target_table] = match[:target_table]
+            options[:primary_key] = match[:primary_key]
+            options[:primary_key_column] = column_for(match[:target_table], match[:primary_key])
+          end
+
+          MismatchedForeignKey.new(options)
         end
 
         def integer_to_sql(limit) # :nodoc:
diff --git a/activerecord/lib/active_record/errors.rb b/activerecord/lib/active_record/errors.rb
index 0858af3874..48c0e8bbcc 100644
--- a/activerecord/lib/active_record/errors.rb
+++ b/activerecord/lib/active_record/errors.rb
@@ -126,16 +126,26 @@ module ActiveRecord
 
   # Raised when a foreign key constraint cannot be added because the column type does not match the referenced column type.
   class MismatchedForeignKey < StatementInvalid
-    def initialize(adapter = nil, message: nil, sql: nil, binds: nil, table: nil, foreign_key: nil, target_table: nil, primary_key: nil)
-      @adapter = adapter
+    def initialize(
+      message: nil,
+      sql: nil,
+      binds: nil,
+      table: nil,
+      foreign_key: nil,
+      target_table: nil,
+      primary_key: nil,
+      primary_key_column: nil
+    )
       if table
-        msg = +<<~EOM
-          Column `#{foreign_key}` on table `#{table}` has a type of `#{column_type(table, foreign_key)}`.
-          This does not match column `#{primary_key}` on `#{target_table}`, which has type `#{column_type(target_table, primary_key)}`.
-          To resolve this issue, change the type of the `#{foreign_key}` column on `#{table}` to be :integer. (For example `t.integer #{foreign_key}`).
+        type = primary_key_column.bigint? ? :bigint : primary_key_column.type
+        msg = <<~EOM.squish
+          Column `#{foreign_key}` on table `#{table}` does not match column `#{primary_key}` on `#{target_table}`,
+          which has type `#{primary_key_column.sql_type}`.
+          To resolve this issue, change the type of the `#{foreign_key}` column on `#{table}` to be :#{type}.
+          (For example `t.#{type} :#{foreign_key}`).
         EOM
       else
-        msg = +<<~EOM
+        msg = <<~EOM.squish
           There is a mismatch between the foreign key and primary key column types.
           Verify that the foreign key column type and the primary key of the associated table match types.
         EOM
@@ -145,11 +155,6 @@ module ActiveRecord
       end
       super(msg, sql: sql, binds: binds)
     end
-
-    private
-      def column_type(table, column)
-        @adapter.columns(table).detect { |c| c.name == column }.sql_type
-      end
   end
 
   # Raised when a record cannot be inserted or updated because it would violate a not null constraint.