MySQL dialect throws CompileError when unsupported foreign key options are specified, as MySQL silently ignores them [ticket 2841]pr/38

2 files changed, 41 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index d0f654fe2..8f5c0ac33 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1451,6 +1451,20 @@ class MySQLCompiler(compiler.SQLCompiler):
 class MySQLDDLCompiler(compiler.DDLCompiler):
     def create_table_constraints(self, table):
         """Get table constraints."""
+
+        # For foreign keys, MySQL silently fails or drops other FK attributes
+        # if MATCH, DEFERRABLE, or INITIALLY are defined. Warn on 0.8, raise
+        # an error on 0.9
+        # https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html
+        for fk in table.foreign_keys:
+            for attr in ('match', 'deferrable', 'initially'):
+                if getattr(fk, attr) is not None:
+                    raise exc.CompileError(
+                        "MySQL does not support specifying MATCH, DEFERRABLE, "
+                        "or INITIALLY on foreign keys, but a foreign key on "
+                        "table '%s' defines at least one." % table.name
+                    )
+
         constraint_string = super(
                         MySQLDDLCompiler, self).create_table_constraints(table)
 
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 21c050915..675f3b7d0 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -638,6 +638,33 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
                             "a foreign key to target column 'pkg_id'",
                               metadata.create_all)
 
+    @testing.only_on('mysql')
+    def test_fk_attribute_error(self):
+        """
+        MySQL FKs cannot handle certain attributes... throw a CompileError
+        """
+        meta = MetaData(testing.db)
+        Table('users', meta,
+            Column('id', sa.Integer, primary_key=True),
+            Column('name', sa.String(30)),
+            test_needs_fk=True)
+        Table('addresses', meta,
+            Column('id', sa.Integer, primary_key=True),
+            Column('user_id', sa.Integer, sa.ForeignKey(
+                'users.id',
+                name = 'addresses_user_id_fkey',
+                match = 'FULL',
+                onupdate='CASCADE',
+                ondelete='CASCADE'
+            )),
+            test_needs_fk=True)
+
+        assert_raises_message(sa.exc.CompileError,
+            "MySQL does not support specifying MATCH, DEFERRABLE, or "
+            "INITIALLY on foreign keys, but a foreign key on table "
+            "'addresses' defines at least one.",
+            meta.create_all)
+
     def test_composite_pks(self):
         """test reflection of a composite primary key"""