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

1 files changed, 18 insertions, 8 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: