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, 80 insertions, 3 deletions

diff --git a/activerecord/test/cases/adapters/mysql2/mysql2_adapter_test.rb b/activerecord/test/cases/adapters/mysql2/mysql2_adapter_test.rb
index 7bc86476b6..704c84abc9 100644
--- a/activerecord/test/cases/adapters/mysql2/mysql2_adapter_test.rb
+++ b/activerecord/test/cases/adapters/mysql2/mysql2_adapter_test.rb
@@ -56,7 +56,7 @@ class Mysql2AdapterTest < ActiveRecord::Mysql2TestCase
       @conn.columns_for_distinct("posts.id", [order])
   end
 
-  def test_errors_for_bigint_fks_on_integer_pk_table
+  def test_errors_for_bigint_fks_on_integer_pk_table_in_alter_table
     # table old_cars has primary key of integer
 
     error = assert_raises(ActiveRecord::MismatchedForeignKey) do
@@ -64,9 +64,86 @@ class Mysql2AdapterTest < ActiveRecord::Mysql2TestCase
       @conn.add_foreign_key :engines, :old_cars
     end
 
-    assert_match "Column `old_car_id` on table `engines` has a type of `bigint(20)`", error.message
+    assert_includes error.message, <<~MSG.squish
+      Column `old_car_id` on table `engines` does not match column `id` on `old_cars`,
+      which has type `int(11)`. To resolve this issue, change the type of the `old_car_id`
+      column on `engines` to be :integer. (For example `t.integer :old_car_id`).
+    MSG
     assert_not_nil error.cause
-    @conn.exec_query("ALTER TABLE engines DROP COLUMN old_car_id")
+  ensure
+    @conn.execute("ALTER TABLE engines DROP COLUMN old_car_id") rescue nil
+  end
+
+  def test_errors_for_bigint_fks_on_integer_pk_table_in_create_table
+    # table old_cars has primary key of integer
+
+    error = assert_raises(ActiveRecord::MismatchedForeignKey) do
+      @conn.execute(<<~SQL)
+        CREATE TABLE activerecord_unittest.foos (
+          id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
+          old_car_id bigint,
+          INDEX index_foos_on_old_car_id (old_car_id),
+          CONSTRAINT fk_rails_ff771f3c96 FOREIGN KEY (old_car_id) REFERENCES old_cars (id)
+        )
+      SQL
+    end
+
+    assert_includes error.message, <<~MSG.squish
+      Column `old_car_id` on table `foos` does not match column `id` on `old_cars`,
+      which has type `int(11)`. To resolve this issue, change the type of the `old_car_id`
+      column on `foos` to be :integer. (For example `t.integer :old_car_id`).
+    MSG
+    assert_not_nil error.cause
+  ensure
+    @conn.drop_table :foos, if_exists: true
+  end
+
+  def test_errors_for_integer_fks_on_bigint_pk_table_in_create_table
+    # table old_cars has primary key of bigint
+
+    error = assert_raises(ActiveRecord::MismatchedForeignKey) do
+      @conn.execute(<<~SQL)
+        CREATE TABLE activerecord_unittest.foos (
+          id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
+          car_id int,
+          INDEX index_foos_on_car_id (car_id),
+          CONSTRAINT fk_rails_ff771f3c96 FOREIGN KEY (car_id) REFERENCES cars (id)
+        )
+      SQL
+    end
+
+    assert_includes error.message, <<~MSG.squish
+      Column `car_id` on table `foos` does not match column `id` on `cars`,
+      which has type `bigint(20)`.  To resolve this issue, change the type of the `car_id`
+      column on `foos` to be :bigint. (For example `t.bigint :car_id`).
+    MSG
+    assert_not_nil error.cause
+  ensure
+    @conn.drop_table :foos, if_exists: true
+  end
+
+  def test_errors_for_bigint_fks_on_string_pk_table_in_create_table
+    # table old_cars has primary key of string
+
+    error = assert_raises(ActiveRecord::MismatchedForeignKey) do
+      @conn.execute(<<~SQL)
+        CREATE TABLE activerecord_unittest.foos (
+          id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
+          subscriber_id bigint,
+          INDEX index_foos_on_subscriber_id (subscriber_id),
+          CONSTRAINT fk_rails_ff771f3c96 FOREIGN KEY (subscriber_id) REFERENCES subscribers (nick)
+        )
+      SQL
+    end
+
+    assert_includes error.message, <<~MSG.squish
+      Column `subscriber_id` on table `foos` does not match column `nick` on `subscribers`,
+      which has type `varchar(255)`. To resolve this issue, change the type of the `subscriber_id`
+      column on `foos` to be :string. (For example `t.string :subscriber_id`).
+    MSG
+    assert_not_nil error.cause
+  ensure
+    @conn.drop_table :foos, if_exists: true
   end
 
   def test_errors_when_an_insert_query_is_called_while_preventing_writes