raise error for invalid ForeignKeyConstraint definition

[Jorge Sierra]

conn_str = string.Template('db2+ibm_db://$user:$password@localhost:50000/Sample').substitute(user = user , password = password)

for t in metadata.sorted_tables: logger.info ("CreateTable %s %s" % (t.name, CreateTable(t).compile(db2)))

When trying to run CreateTable

compiler.py

around line 2588 under function

def visit_foreign_key_constraint(self, constraint):

this function is assuming f.parent is never 'None' and in my case it was, same as f.column

This is the fix below

list_text1 = [] 
for f in constraint.elements:
 if f.parent is not None:
   list_text1.append(preparer.quote(f.parent.name))

list_text2 = [] 
for f in constraint.elements:
 if hasattr(f, 'column'):
   if f.column is not None:
     list_text2.append(preparer.quote(f.column.name))           

text += "FOREIGN KEY(%s) REFERENCES %s (%s)" % (
      ', '.join(list_text1),
      self.define_constraint_remote_table(
          constraint, remote_table, preparer),
      ', '.join(list_text2))
text += self.define_constraint_match(constraint)
text += self.define_constraint_cascades(constraint)
text += self.define_constraint_deferrability(constraint)
return text

[Mike Bayer]

hello -

this bug report lacks necessary specifics. There is no way to generate the DDL for a ForeignKey element if it does not have a .column or .parent attribute set up - these are the columns which the element represents and are established when the ForeignKey is associated with a Column and a referred table, which is certainly the case if you are starting from a Table object.

I suspect that you are doing something like reflecting tables with DB2, and DB2 has either a bug or some odd construct that SQLAlchemy does not expect - would need to know what that construct is in order to take any steps here.

[Jorge Sierra]

The error can come from 3 places 1-DB2, 2 - ibm_db_sa or 3- sqlalchemy Just testing db2 with sqlalchemy All I do is connect to db2 sample database, is a demo database that IBM provide for DB2 ....testing...

conn_str = string.Template('db2+ibm_db://$user:$password@localhost:50000/Sample?').substitute(user = user , password = password)
db2 = sqlalchemy.create_engine(conn_str,encoding= 'utf-8')
metadata = MetaData()
metadata.reflect(bind=db2)

for t in metadata.sorted_tables:
   logger.info ("CreateTable %s %s" % (t.name, CreateTable(t).compile(db2)))

Let me see, if you need more help .. But taking a look at compile.py it does assume these variables and attributes are never 'None' in my case they are.

One way I guess for testing this is me providing a db2 connection, or download a docker container with db2 on it....I know db2 is not too popular....

[Mike Bayer]

here's a test case. can you change the URL to "db2:" and see if something changes or otherwise alter this test to show your error?

from sqlalchemy import *

e = create_engine("sqlite://", echo=True)

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

m.create_all(e)

m2 = MetaData()
m2.reflect(e)

from sqlalchemy.schema import CreateTable

print CreateTable(m2.tables['t2'])

[Jorge Sierra]

Ok...lets work on this...give me a couple of days....If I cant duplicate with SQLite...will try to provide a full script to do it with db2.....

[Jorge Sierra]

Here I created a GitHub project to help duplicate this issue

https://github.com/asierra01/sqlalchemy_db2_bug

Any questions let me know.... With sqlite, there is no problem.

[Mike Bayer]

hi there -

The original bug here is a very simple condition where you have a Table object with a ForeignKey that's in an invalid state from reflection. The way reflection works, this should not be possible. The test case would need to be literally the string DDL of such a table in DB2 so I can at least see if there's some odd special feature in use, which almost certainly would lead this to be a bug in db2's third party driver in any case. I'm not the DB2 maintainer so running a whole docker setup is way out of scope for me, please report this to the ibm_db_sa group.

[Mike Bayer]

this is a bug in downstream DB2 driver, unless DB2 developers can illustrate a simple API case for me.

[Mike Bayer]

Issue #3998 was marked as a duplicate of this issue.

[Mike Bayer]

here we are:

from sqlalchemy import *

m = MetaData()

t = Table('t', m, Column('q', Integer))

Table('z', m, Column('q', Integer))

const = ForeignKeyConstraint(['q'], ['z.q', 'z.x'], None, link_to_name=True)
t.append_constraint(const)


# fails
from sqlalchemy import schema

print schema.CreateTable(t)

I can make this raise a nicer error for now.

[Mike Bayer]

Raise if ForeignKeyConstraint created with different numbers of local and remote columns.

An :class:.ArgumentError is now raised if a :class:.ForeignKeyConstraint object is created with a mismatched number of "local" and "remote" columns, which otherwise causes the internal state of the constraint to be incorrect. Note that this also impacts the condition where a dialect's reflection process produces a mismatched set of columns for a foreign key constraint.

Downstream DB2 dialect has been reported as potentially causing this scenario.

Change-Id: Id51c34a6c43749bb582639f9c1dc28723482f0e5 Fixes: #3949 References: #3998

→ <<cset a78718b9340e>>

[Mike Bayer]

Raise if ForeignKeyConstraint created with different numbers of local and remote columns.

An :class:.ArgumentError is now raised if a :class:.ForeignKeyConstraint object is created with a mismatched number of "local" and "remote" columns, which otherwise causes the internal state of the constraint to be incorrect. Note that this also impacts the condition where a dialect's reflection process produces a mismatched set of columns for a foreign key constraint.

Downstream DB2 dialect has been reported as potentially causing this scenario.

Change-Id: Id51c34a6c43749bb582639f9c1dc28723482f0e5 Fixes: #3949 References: #3998 (cherry picked from commit a78718b9340e9840a470300932af178ce57c0f7d)

→ <<cset 0769b41cf291>>

[Mike Bayer]

that fix should give you a stack trace right where it's going wrong

[Jorge Sierra]

Thanks for all the effort, I will take a look at the solution and I realized that the error was too ambiguous, instead I should have focused on the specific table inside the SAMPLE db2 database that was causing this issue to help duplicate the problem.