SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

[Kashif Iftikhar]

Foreign Key constraints in MySQL don't support the DEFERRABLE clause/keyword, however if a FK constraint contains deferrable=True, DEFERRABLE keyword is emitted in the table create DDL. This should not be the case since table creation fails for table containing FKs with deferrable=True.

[Mike Bayer]

OK, though that's an odd case that you need your FK's to be DEFERRABLE, yet your app runs on MySQL (using MyISAM or InnoDB) ? That's kind of why the keyword isn't skipped right now - it assumes if you put "DEFERRABLE", you actually need that functionality and if the DB doesn't support it, an error should be raised.

[Kashif Iftikhar]

Actually ATM we're using both mysql and postgres, some environments running on mysql and some on postgres. Needed to defer FK checks since postgres seems to be more strict and there is no "SET FOREIGN_KEY_CHECKS=0;" for postgres. I was loading data into a table containing self-referening FKs.

So when I got everything working with postgres with all tests passing, mysql started objecting. For the time being I've worked around that using

if db.engine.url.drivername.startswith('postgresql'):

but would be nice if SQLAlchemy could do that :-)

Thank you for your prompt response. Love SQLA and would love it to be even better :-)

[Mike Bayer]

OK so you are still getting a rough equivalent of "deferrable" with MySQL here, which was what I was curious about. Since MySQL's foreign keys are kind of fungible I can see how its fine to skip the keyword on this platform.

[Kashif Iftikhar]

Yes, if no deferrable or deferred words are present in the CREATE TABLE statement for mysql then it would start working fine again.

[Mike Bayer]

oh also here's a workaround for you, just override visit_foreign_key_constraint, should work in 0.7 too:

from sqlalchemy.schema import ForeignKeyConstraint
from sqlalchemy.ext.compiler import compiles

@compiles(ForeignKeyConstraint, "mysql")
def visit_foreign_key_constraint(element, compiler, **kw):
    deferrable = getattr(element, "deferrable", None)
    if deferrable is True:
        element.deferrable = None
    text = compiler.visit_foreign_key_constraint(element, **kw)
    if deferrable is True:
        element.deferrable = True
    return text

if __name__ == '__main__':
    from sqlalchemy import MetaData, Column, Table, create_engine, ForeignKey, Integer

    m = MetaData()

    t1 = Table('t1', m, Column('id', Integer, primary_key=True))
    t2 = Table('t2', m, Column('id', Integer, ForeignKey('t2.id', deferrable=True), primary_key=True))

    e1 = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
    e2 = create_engine("mysql://scott:tiger@localhost/test", echo=True)

    m.create_all(e1)
    m.create_all(e2)

    m.drop_all(e1)
    m.drop_all(e2)

[Kashif Iftikhar]

Thanks :-)

[Mike Bayer]

cc1ea5da10d2424ea2887a68fddfe07a3928c616 0.8

ada19275299f0105f4aaed5bbe 0.9

[Mike Bayer]

I'm going to have to give you a hard change on this one for 0.9. Silently ignoring keywords that make no sense on a MySQL FK, or in the case of MATCH actually break MySQL (see "important" at https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html) is not a good solution here, if a user puts deferrable or initially on their FK and doesn't know about MySQL's FK behavior they get silent failure here. see #2841.

I think going forward, if you are knowingly disabling FKs on your MySQL database, you should use this recipe to disable generation of FKs:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import ForeignKeyConstraint

@compiles(ForeignKeyConstraint, "mysql")
def process(element, compiler, **kw):
    return ""    # or blank out "deferrable"

[Mike Bayer]

cf1ac72bca8b0bc28e09cdb4cdf052bcf82e5076 warning + tests + docs in 0.8

ca02882c6a0d66562d86bf55d5449a04825fa354 error + tests + docs in master

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)