NoForeignKeysError and AmbiguousForeignKeysError after upgrade to 2.x

[andreasc-kuehn]

NoForeignKeysError and AmbiguousForeignKeysError after upgrade to 2.x

[zzzeek]

Am traveling this week. Will have time to look early next week if no one else is available to triage this

[zzzeek]

I apologize, if the given information is not complete from your point of view, but in this case, please give me a hint, what else information you need to pursue this observation.

I would need an MCVE.

Here's your program with the undefined relationship STATUS = relationship("StatusItem") removed since you did not provide it and a one line UserMixin class, also not provided. script runs fine and there are no changes in this area compared to 1.4 and 2.0. Please run the script below.

from datetime import datetime

from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()


class ContactStatus(Base):
    __bind_key__   = 'contact_bind'
    __tablename__  = "CONTACT_STATUS"
    __table_args__ = (
        ForeignKeyConstraint(name="CNTSTA_CNT", columns=["CONTACT_ID"], refcolumns=["contact.CONTACT.CONTACT_ID"], ondelete="CASCADE", onupdate="CASCADE")
        , ForeignKeyConstraint(name="CNTSTA_STAITM", columns=["STATUS_ID"], refcolumns=["contact.STATUS_ITEM.STATUS_ID"], ondelete="RESTRICT", onupdate="CASCADE")
        , ForeignKeyConstraint(name="CNTSTA_USR", columns=["SET_BY_USER_LOGIN_ID"], refcolumns=["auth.USER_LOGIN.USER_ID"], ondelete="RESTRICT", onupdate="CASCADE")
        ,{'schema':'contact'}
    )
    CONTACT_ID = Column("CONTACT_ID", Integer, nullable=False, primary_key=True, )
    STATUS_DATE = Column("STATUS_DATE", DateTime, nullable=False, primary_key=True, )
    STATUS_END_DATE = Column("STATUS_END_DATE", DateTime, nullable=True, )
    STATUS_ID = Column("STATUS_ID", String(length=20), nullable=False, primary_key=True, )
    SET_BY_USER_LOGIN_ID = Column("SET_BY_USER_LOGIN_ID", Integer, nullable=True, )
    SET_BY_USER_LOGIN = relationship("UserLogin")
    LAST_UPDATED_STAMP = Column("LAST_UPDATED_STAMP", DateTime, nullable=True, default=datetime.utcnow, onupdate=datetime.utcnow, )
    CREATED_STAMP = Column("CREATED_STAMP", DateTime, nullable=True, default=datetime.utcnow, )

class UserMixin:
    pass

class UserLogin(UserMixin, Base):
    __bind_key__   = 'auth_bind'
    __tablename__ = 'USER_LOGIN'
    __table_args__ = {'schema': 'auth'}

    USER_ID  = Column('USER_ID',Integer,nullable=False, autoincrement=True,primary_key=True,)


Base.registry.configure()

[andreasc-kuehn]

I will try to provide an MCVE, but there is not so much hope, I will be able to do so. The code I am showing above is inside a Flask application and I am not sure, if providing a "MCVE Flask App" will really help. I will work on that, but do not expect that I can do this quickly, since much work might be required for this. Maybe it is faster to spend the time on debugging...

Meanwhile I will provide the longer part of the exception, showing at the end the places where I tried to debug. If in the involved code nothing has changed, then the problem could be located in the internal storage of ForeignKeyConstraint. Since only relationships that go over "schema" boundaries are affected, I assume that a comparison of foreign key definition with the primary key in the target table fails due to not taking the schema name into account. I have no idea, how much Flask-SqlAlchemy is in between there. I will examine this further.

Traceback (most recent call last):
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 1498, in call
return self.wsgi_app(environ, start_response)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 1476, in wsgi_app
response = self.handle_exception(e)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 1473, in wsgi_app
response = self.full_dispatch_request()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 882, in full_dispatch_request
rv = self.handle_user_exception(e)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
rv = self.dispatch_request()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/views.py", line 110, in view
return current_app.ensure_sync(self.dispatch_request)(**kwargs) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib/python3.12/site-packages/flask/views.py", line 191, in dispatch_request
return current_app.ensure_sync(meth)(**kwargs) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/auth/routes.py", line 115, in get
self.initialize()
File "/home/develop/Projects/Python/ibench1/app/common/MethodViews.py", line 358, in initialize
self.add_views()
File "/home/develop/Projects/Python/ibench1/app/auth/routes.py", line 85, in add_views
fbtns.add_custom_button(icon=None, text=_('Continue') + ' ', fmt=StyleConfig.BTN_SECONDARY)
File "/home/develop/Projects/Python/ibench1/app/common/ButtonFactories.py", line 423, in add_custom_button
self._add_buttondef(icon=icon, text=text, cdata=cdata, attributes=attributes, cssclass=self.fmt(fmt))
^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/common/ViewFormat.py", line 8, in fmt
return self._cssprovider.fmt(name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/common/CssProvider.py", line 63, in fmt
return self._get_fmt(name)
^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/common/CssProvider.py", line 45, in _get_fmt
fmt = self._load_fmt(name)
^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/common/CssProvider.py", line 23, in _load_fmt
s = FormatStyleDatabase.get_format_style(name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/app/resource/repository/FormatStyleRepository.py", line 23, in get_format_style
return db.session.get(FormatStyle, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/scoping.py", line 1060, in get
return self._proxied.get(
^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/session.py", line 3637, in get
return self._get_impl(
^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/session.py", line 3817, in _get_impl
return db_load_fn(
^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/loading.py", line 694, in load_on_pk_identity
session.execute(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
result = conn.execute(
^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
return meth(
^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/engine/base.py", line 1636, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/elements.py", line 702, in _compile_w_cache
compiled_sql = self._compiler(
^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/elements.py", line 315, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 1429, in init
Compiled.init(self, dialect, statement, **kwargs)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 870, in init
self.string = self.process(self.statement, **compile_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
return obj._compiler_dispatch(self, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/compiler.py", line 4674, in visit_select
compile_state = select_stmt._compile_state_factory(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/sql/base.py", line 684, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/context.py", line 1091, in create_for_statement
_QueryEntity.to_compile_state(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/context.py", line 2546, in to_compile_state
_MapperEntity(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/context.py", line 2626, in init
entity._post_inspect
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 1253, in get
obj.dict[self.name] = result = self.fget(obj)
^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2711, in _post_inspect
self._check_configure()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2388, in _check_configure
_configure_registries({self.registry}, cascade=True)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4203, in _configure_registries
_do_configure_registries(registries, cascade)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4244, in _do_configure_registries
mapper._post_configure_properties()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2405, in _post_configure_properties
prop.init()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/interfaces.py", line 584, in init
self.do_init()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1644, in do_init
self._setup_join_conditions()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1884, in _setup_join_conditions
self._join_condition = jc = JoinCondition(
^^^^^^^^^^^^^^
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2310, in init
self._determine_joins()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2444, in _determine_joins
raise sa_exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship ContactStatus.SET_BY_USER_LOGIN - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

[andreasc-kuehn]

I just run a diff between "relationships.py" in version 1.4.51 and the newest version 2.0.29, so it is not true that nothing has changed there. Just for having mentionned.

[zzzeek]

I just run a diff between "relationships.py" in version 1.4.51 and the newest version 2.0.29, so it is not true that nothing has changed there.

by "nothing has changed" we mean that a program that runs on 1.4.51 without error will also run on 2.0.29 without error, there are no backwards-incompatible API changes.

this is a very basic setup issue which really should be a discussion since there is unlikely to be any bug here. Try running the script I have above. Does it run? OK. now transplant that definition into a clean flask program. does that work? OK. now add more elements to resemble your program, one at a time. that's how you debug issues like this if you have nothing else to go on.