here's an issue with a demonstration script to reproduce this error message:
https://bitbucket.org/zzzeek/sqlalchemy/issues/4266/automapprepare-creates-classes-that-are we can add the mutex in, also it's an RLock so if you have the workaround in place your code will be forwards-compatible. On Sun, May 27, 2018 at 12:31 PM, Mike Bayer <[email protected]> wrote: > so the stack trace shows that your application has *other* mappings in > it that are *not* coming from this automap block, which I assumed was > not the case. I continue to have a very vague idea of the scope and > design of this application so I have to guess a lot. > > What we will do is absolultely prevent automap from having a > non-mapped class in play while any part of the process might call > configure mappers. This could also be patched into automap. > > Add this import to the top: > > from sqlalchemy.orm.mapperlib import _CONFIGURE_MUTEX > > > Change the prepare line to be like this: > > > _CONFIGURE_MUTEX.acquire() > try: > base.prepare( > name_for_scalar_relationship=name_for_scalar_relationship) > finally: > _CONFIGURE_MUTEX.release() > > > This will prevent any configuration from occurring while automap > creates classes and maps them. if that fixes the problem I can add > this to automap.prepare(). > > > > > > On Sun, May 27, 2018 at 2:59 AM, Dave Mittner <[email protected]> wrote: >> Well, thus far the locking/collecting solution doesn't seem to be doing the >> trick. >> >> Here's my revised code. Note that I haven't instituted any specified table >> exclusion, and my caching is temporarily disabled. Also note I made the >> automap_mutex a global as a static attribute on the DB class. If it's >> defined per-thread then the lock only applies for that thread, which kind of >> defeated the purpose. >> >>> db_url = engine.url.URL(drivername = self.drivername, >>> username = self.username, >>> password = self.password, >>> host = self.host, >>> database = self.database, >>> query = {'charset':'utf8'}) >>> self.engine = >>> create_engine(db_url,encoding='utf8',convert_unicode=True) >>> self.session = Session(self.engine) >>> self.connection = self.engine.connect() >>> id = (self.drivername,self.host,self.database) >>> app.logger.debug('Locking') >>> DB.automap_mutex.acquire() >>> app.logger.debug('Locked') >>> try: >>> if id not in DB.tables: >>> base = automap_base() >>> try: >>> base.prepare( >>> self.engine, >>> reflect = True, >>> name_for_scalar_relationship = >>> name_for_scalar_relationship >>> ) >>> configure_mappers() >>> except: >>> del base >>> gc.collect() >>> raise >>> else: >>> self.tables = base.classes >>> #DB.tables[id] = base.classes >>> finally: >>> app.logger.debug('Unlocking') >>> DB.automap_mutex.release() >>> app.logger.debug('Unlocked') >>> #self.tables = DB.tables[id] >> >> >> The locking does appear to be working. I can tell that much from the log >> outputs. Here are dual threads trying to establish connections within close >> proximity of each other. >> >> [2018-05-26 23:16:00,024] [23223/139778217465600] [DEBUG] CORE Locking >> [2018-05-26 23:16:00,025] [23223/139778217465600] [DEBUG] CORE Locked >> [2018-05-26 23:16:00,026] [23223/139777630271232] [DEBUG] CORE Locking >> [2018-05-26 23:16:00,392] [23223/139778217465600] [DEBUG] CORE Unlocking >> [2018-05-26 23:16:00,392] [23223/139778217465600] [DEBUG] CORE Unlocked >> [2018-05-26 23:16:00,392] [23223/139777630271232] [DEBUG] CORE Locked >> [2018-05-26 23:16:00,800] [23223/139777630271232] [DEBUG] CORE Unlocking >> [2018-05-26 23:16:00,800] [23223/139777630271232] [DEBUG] CORE Unlocked >> >> But the locking isn't preventing the initial "UnmappedClassError", nor the >> subsequent and repeating "InvalidRequestError: One or more mappers failed to >> initialize" exceptions. >> >>> Traceback (most recent call last): >>> File "/home/dmittner/loci2/app/jobs.py", line 507, in start >>> result = self.callable(self,**self.parameters) >>> File "/home/dmittner/loci2/app/jobs.py", line 406, in >>> job_update_organizations >>> Core(db=job.db).update_organizations_from_oracle() >>> File "/home/dmittner/loci2/app/api/mod_system/models.py", line 2281, in >>> update_organizations_from_oracle >>> party_name=data['party_name'] >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", >>> line 1909, in merge >>> _resolve_conflict_map=_resolve_conflict_map) >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", >>> line 2019, in _merge >>> for prop in mapper.iterate_properties: >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/mapper.py", >>> line 1920, in iterate_properties >>> configure_mappers() >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/mapper.py", >>> line 3013, in configure_mappers >>> mapper._post_configure_properties() >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/mapper.py", >>> line 1811, in _post_configure_properties >>> prop.init() >>> File >>> "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/interfaces.py", line >>> 184, in init >>> self.do_init() >>> File >>> "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/relationships.py", >>> line 1655, in do_init >>> self._process_dependent_arguments() >>> File >>> "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/relationships.py", >>> line 1712, in _process_dependent_arguments >>> self.target = self.mapper.mapped_table >>> File >>> "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py", >>> line 767, in __get__ >>> obj.__dict__[self.__name__] = result = self.fget(obj) >>> File >>> "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/relationships.py", >>> line 1634, in mapper >>> configure=False) >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/base.py", >>> line 426, in class_mapper >>> raise exc.UnmappedClassError(class_) >>> sqlalchemy.orm.exc.UnmappedClassError: Class >>> 'sqlalchemy.ext.automap.geo_country_map_data' is not mapped >> >> >> >>> Traceback (most recent call last): >>> File "/home/dmittner/loci2/app/jobs.py", line 507, in start >>> result = self.callable(self,**self.parameters) >>> File "/home/dmittner/loci2/app/jobs.py", line 434, in >>> job_update_geo_database >>> Core(db=job.db).update_geo() >>> File "/home/dmittner/loci2/app/api/mod_system/models.py", line 4018, in >>> update_geo >>> database = app.config['GEO_SQL_DB'] >>> File "/home/dmittner/loci2/app/api/mod_system/models.py", line 790, in >>> __init__ >>> configure_mappers() >>> File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/mapper.py", >>> line 3010, in configure_mappers >>> raise e >>> sqlalchemy.exc.InvalidRequestError: One or more mappers failed to >>> initialize - can't proceed with initialization of other mappers. Triggering >>> mapper: 'Mapper|geo_country_label_data|geo_country_label_data'. Original >>> exception was: Class 'sqlalchemy.ext.automap.geo_country_map_data' is not >>> mapped >> >> >> Based on the above code I really don't think I'd have any references to >> anything persisting. My only thought was that maybe the engine is being >> given a reference when it's passed into prepare(), but I didn't notice >> anything like that when I glanced at the source code. >> >> >> On Sat, May 26, 2018 at 3:47 PM, Mike Bayer <[email protected]> >> wrote: >>> >>> >>> >>> On Sat, May 26, 2018, 1:49 PM Dave Mittner <[email protected]> wrote: >>>>> >>>>> # 3. configure now, instead of waiting to do it during a query. >>>> >>>> >>>> That right there is the reason this problem seems so hard to track down. >>>> I wasn't seeing any error at connection-time, but rather at query time. And >>>> that query could be triggered on any connection, in any area of the code. >>>> In >>>> theory it wouldn't even necessarily happen at the same time that >>>> connections >>>> are being established. So I'd get an error at Point A when the root cause >>>> actually tracks back to Point B, and this was made possibly because of the >>>> use of the global _mapper_registry; it enabled a problem based on one >>>> connection to trigger an error in another connection. >>>> >>>> So by locking threading and triggering the error via configure_mappers(), >>>> I'm able to see the error within the proper connection context. >>>> >>>> I guess what I don't understand is why you do the mapping at query-time >>>> and not connection-time. >>> >>> >>> Well this is not "the mapping", this is the point at which the disparate >>> mappers all over the application are configured against each other, namely >>> the relationships defined between them as well as any event handlers that >>> work post-configure. The the configure_mappers() step is called >>> automatically both when a query is built up as well as when a mapped object >>> is constructed. Both of these things happen before any database connection >>> is necessarily present. >>> >>> It is not uncommon that an application that has a well-defined point at >>> which things start up would call configure_mappers explicitly, which fits >>> with what you're doing. SQLAlchemy could also have decided to make this >>> explicitness required, but we've made it automatic and usually with no >>> problems. But the case I think you've found here warrants some >>> consideration, since automap does create mappings within a function, rather >>> than at module import time as is more typical. >>> >>> >>> >>>> With the "base.classes" attribute being immediately available after >>>> base.prepare, I believed the mapping was already being done at >>>> connection-time, and therefor there should have been an exception throwing >>>> then, and this problem wouldn't exist. Of course you might also need to >>>> detect if threading is being used and lock it, but this could all be >>>> behavior triggered by a threading=True parameter on the "base.prepare" >>>> call. >>>> >>>> Anyway, I'll apply these conventions and see how it goes. >>>> >>>> On Sat, May 26, 2018 at 9:44 AM, Mike Bayer <[email protected]> >>>> wrote: >>>>> >>>>> On Sat, May 26, 2018 at 12:19 AM, Dave Mittner <[email protected]> >>>>> wrote: >>>>> >> Can you elaborate on this system? There's some open source code >>>>> >> that >>>>> >> creates SQLAlchemy mappings which you have no control over ? What >>>>> >> do >>>>> >> you mean "on the database" ? >>>>> > >>>>> > >>>>> > I mean something must be happening on the database server, itself, >>>>> > causing >>>>> > SQLAlchemy's automap process to fail. Or there's a network problem. Or >>>>> > somewhere else in the line. I can say with certainty that there are no >>>>> > conditions in my application that vary from connection to connection. >>>>> > Every >>>>> > attempt to connect in my code is identical, yet sometimes it fails. >>>>> > >>>>> > And even if there were a race condition causing a mapping to fail, >>>>> > that >>>>> > still shouldn't prevent all future attempts by the application to >>>>> > automap >>>>> > other databases. This is the inherent problem of using the global >>>>> > space -- >>>>> > it affects the entire application and not just one instantiation of a >>>>> > class. >>>>> > Each connection/engine isn't isolated from each other, so a failure on >>>>> > any >>>>> > one can impact others. A bug in one module of an application can shut >>>>> > down >>>>> > the entire application indefinitely. >>>>> > >>>>> > That brings us to this comment you made in one of your examples: >>>>> > >>>>> >> the program is not expected to be able to run with bad mappings in >>>>> >> it. >>>>> > >>>>> > >>>>> > But why not just allow exceptions to serve their purpose? If you throw >>>>> > an >>>>> > exception when a mapping fails, rather than keeping it stored in >>>>> > _mapper_registry in a failed state, then other connections are free to >>>>> > function. A failed mapping will only cause issues with the one >>>>> > connection >>>>> > and only with other mappings that reference it. My application, for >>>>> > example, >>>>> > has no such mappings that would fail, yet my application is unable to >>>>> > proceed with other functions due to how the mapping failure is >>>>> > handled. >>>>> >>>>> >>>>> I understand the temptation to see that this part of the mapping >>>>> system uses a global registry and then jump ahead to the common advice >>>>> that "global variables are bad", and therefore this is an >>>>> inappropriate architecture. However, there is a context, a history, >>>>> and a purpose for why the system is architected this way, and I've >>>>> shared an example program I wrote to illustrate one particular reason >>>>> why this is, however I didn't provide a full narrative explanation. >>>>> >>>>> I'll also note again that automap itself is not the standard way >>>>> SQLAlchemy was intended to be used, and if the ORM were entirely >>>>> focused on automap from the ground up, the mapper configuration >>>>> process would likely work differently. In particular, your >>>>> references to "connections/engines not isolated from each other" >>>>> illustrates a misunderstanding that I am assuming originates from >>>>> looking at things in an "automap" perspective, because there is not >>>>> actually any relationship between a SQLAlchemy engine and a class >>>>> mapping in any way whatsoever. A network error cannot cause the >>>>> problem you describe, for example, because the reflection process >>>>> completes fully before any mappers are constructed. automap itself >>>>> does not anticipate a concurrency use case as you are doing, so at the >>>>> very least the documentation needs to include examples on how to >>>>> accommodate for this use case which likely would have prevented this >>>>> issue from happening in the first place. >>>>> >>>>> I'll reiterate as well that the reflection process here can be >>>>> tailored to exclude tables you don't care about, and that you can >>>>> remove failed mappers from _mapper_registry as demonstrated in a >>>>> second example program I've shared, however I perhaps have not stated >>>>> clearly enough that this should be within the scope of a cleanup >>>>> process that you take when mappings fail. That your program starts >>>>> to work eventually is very likely because Python's garbage collector >>>>> eventually runs. My suggestion is to run it immediately in the >>>>> failure case. >>>>> >>>>> Within the scope of suggestions I've made like using mutexes, limiting >>>>> the reflection process, and using garbage collection, these are all >>>>> efforts to provide things you can try to help solve your problem. In >>>>> an effort to make this as easy as possible, below is the snippet of >>>>> code you've given me with these techniques integrated, including >>>>> comments with my latest thinking on what exactly the problem you are >>>>> seeing likely is. I hope that you can work with these techniques to >>>>> see if it solves your issue. >>>>> >>>>> from sqlalchemy.orm import configure_mappers >>>>> import threading >>>>> import gc >>>>> >>>>> automap_mutex = threading.Lock() >>>>> >>>>> def automap_mapping_thing(): >>>>> db_url = engine.url.URL(drivername = self.drivername, >>>>> username = self.username, >>>>> password = self.password, >>>>> host = self.host, >>>>> database = self.database, >>>>> query = {'charset':'utf8'}) >>>>> self.engine = >>>>> create_engine(db_url,encoding='utf8',convert_unicode=True) >>>>> self.session = Session(self.engine) >>>>> >>>>> self.connection = self.engine.connect() >>>>> >>>>> # Automap functionality has a lot of overhead, so cache the >>>>> results on a per-host/database basis >>>>> id = (self.drivername,self.host,self.database) >>>>> >>>>> try: >>>>> # look in the dictionary. 99% of the time the id will be >>>>> # present, since this is a cache, so don't waste time with 'in' >>>>> check >>>>> self.tables = DB.tables[id] >>>>> except KeyError: >>>>> # more expensive KeyError catch for the 1% of the time the id >>>>> # isn't there. also allows the check for the key to be more >>>>> or less atomic >>>>> >>>>> # step 1, lock. The race you are getting is, one automap >>>>> process >>>>> # has created a class but not mapped it yet, while some other >>>>> thread >>>>> # calls configure_mappers() which hits it. automap does not >>>>> # anticipate this use case right now. >>>>> automap_mutex.acquire() >>>>> >>>>> # try/finally block for the mutex >>>>> try: >>>>> >>>>> # 1. since we are mutexing, multiple threads may have >>>>> reached >>>>> # here, and we might not be the first, so, check if someone >>>>> already >>>>> # did the work: >>>>> >>>>> if id in DB.tables: >>>>> return >>>>> >>>>> # 2. reflect tables, exclude tables you don't need >>>>> def exclude_tables_we_dont_need(table_name, metadata): >>>>> return table_name not in ( >>>>> 'geo_state_map_data', 'other_table', 'etc') >>>>> >>>>> # or, inclusive >>>>> # return table_name in ( >>>>> # 'table_i_care_about', 'other_table_i_care_about') >>>>> >>>>> # do the reflection >>>>> base = automap_base() >>>>> base.metadata.reflect( >>>>> self.connection, only=exclude_tables_we_dont_need) >>>>> >>>>> # try/except block for ensuring bad mappers are removed >>>>> try: >>>>> # 2. prepare mappings from reflected tables. >>>>> base.prepare( >>>>> >>>>> name_for_scalar_relationship=name_for_scalar_relationship) >>>>> >>>>> # 3. configure now, instead of waiting to do it during a >>>>> query. >>>>> configure_mappers() >>>>> except: >>>>> >>>>> # 4. something went wrong w/ the prepare or configure. >>>>> # clean up >>>>> >>>>> log.error("Error occurred", exc_info=True) >>>>> >>>>> # remove all the new mappers we just made from memory >>>>> del base >>>>> >>>>> # gc collect. _mapper_registry will be emptied of these >>>>> # mappers >>>>> gc.collect() >>>>> >>>>> # re-raise, or not. whatever you want to do. >>>>> raise >>>>> else: >>>>> # fully mapped classes ready to go. >>>>> DB.tables[id] = base.classes >>>>> finally: >>>>> # leave critical section >>>>> automap_mutex.release() >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> > >>>>> > >>>>> > In any case, I have my application attempting the mapping anew every >>>>> > minute >>>>> > and it's gone for a few hours now without the problem being triggered. >>>>> > I'm >>>>> > really just shooting in the dark here, though. I'm hoping that I can >>>>> > find >>>>> > something else going on at the same time as the failure to explain >>>>> > what's >>>>> > triggering it, but I'm not hopeful. And without knowing how it's being >>>>> > triggered, it's nigh impossible to intentionally trigger it to test >>>>> > fixes or >>>>> > bypasses. That's why my focus has been on the underlying conceptual >>>>> > issue of >>>>> > SQLAlchemy's error handling method blocking things altogether. And >>>>> > conceptually, I don't think it should matter what causes an error; as >>>>> > a >>>>> > matter of principle I think the existing mechanism is presumptuous in >>>>> > not >>>>> > allowing any subsequent mappers from working. >>>>> > >>>>> > >>>>> > On Fri, May 25, 2018 at 2:54 PM, Mike Bayer <[email protected]> >>>>> > wrote: >>>>> >> >>>>> >> On Fri, May 25, 2018 at 3:21 PM, Dave Mittner >>>>> >> <[email protected]> >>>>> >> wrote: >>>>> >> >> This statement does not make sense without more information. >>>>> >> >> automap >>>>> >> >> either succeeds against a given database, or it fails. It is not >>>>> >> >> easy >>>>> >> >> to produce a situation where it fails "only rarely". Things >>>>> >> >> that >>>>> >> >> could cause this are: 1. the database you are automapping against >>>>> >> >> is >>>>> >> >> constantly changing, 2. the mapping is relying upon some subtle >>>>> >> >> hash >>>>> >> >> ordering or 3. automap is being used in a multi-threaded context >>>>> >> >> where >>>>> >> >> more than one automap tries to hit the same tables at the same >>>>> >> >> time. >>>>> >> >> When something happens "only rarely", that points to race >>>>> >> >> conditions >>>>> >> >> or sometimes hash ordering issues. To rule out #2, you would >>>>> >> >> create a test script that does your automap by itself and try to >>>>> >> >> run >>>>> >> >> automap hundreds of times against the particular database that >>>>> >> >> caused >>>>> >> >> a failure, if under Python 2 you'd want to set >>>>> >> >> PYTHONHASHSEED='random' >>>>> >> >> in the environment. But the error you showed looks more like a >>>>> >> >> race >>>>> >> >> of two automaps hitting the same table. >>>>> >> > >>>>> >> > >>>>> >> > That's my point though, if there's a condition causing the failure, >>>>> >> > it's >>>>> >> > not >>>>> >> > in my code. It's on the database. A third party system. >>>>> >> >>>>> >> Can you elaborate on this system? There's some open source code >>>>> >> that >>>>> >> creates SQLAlchemy mappings which you have no control over ? What >>>>> >> do >>>>> >> you mean "on the database" ? >>>>> >> >>>>> >> >>>>> >> > >>>>> >> > It comes down to this: SQLAlchemy doesn't handle that kind of >>>>> >> > problem >>>>> >> > gracefully. >>>>> >> >>>>> >> I love to support new use cases if I can get a complete definition of >>>>> >> what it is you're trying to do and why my suggestions can't be taken. >>>>> >> You showed me the code that is causing the problem, and I suggested >>>>> >> using a mutex and I can show you specifically how to fix what might >>>>> >> be >>>>> >> causing the problem. Your description of this code is "Here's the >>>>> >> gist of my connection initiation code that's run upon instantiation >>>>> >> of >>>>> >> the DB class. Mind you, a prior coworker wrote this and there's so >>>>> >> many ways of interfacing with SQLAlchemy that I'm not even sure where >>>>> >> he got it from." - now you're saying this is third party code you >>>>> >> can't change? >>>>> >> >>>>> >> >>>>> >> > If something happens in an external system that causes problems >>>>> >> > in mine, that's something I should be able to handle and move on. >>>>> >> > If the >>>>> >> > same problem happens 10,000 times, every time I try it, that's >>>>> >> > fine. >>>>> >> > I'll >>>>> >> > catch the exception, log it, abort that thread, and other processes >>>>> >> > will >>>>> >> > continue on. Connections to other databases will still function. >>>>> >> > >>>>> >> > But that isn't the case here. One failure prevents all future >>>>> >> > mapping >>>>> >> > operations, even to other databases that would work. >>>>> >> >>>>> >> if you pull in third party code that places an event within >>>>> >> SQLAlchemy >>>>> >> against all new mappings, and that code is broken and fails, it will >>>>> >> break your entire application. It is not controversial that >>>>> >> installing broken code in your applciation in the same process space >>>>> >> will break that process altogether. Perhaps I have not been clear, >>>>> >> but it is not normal for automap to "fail occasionally". If used >>>>> >> correctly, it will not fail. I've offered to help you repair that >>>>> >> system. >>>>> >> >>>>> >> >>>>> >> > >>>>> >> > Regardless, I don't want the development overhead of having to >>>>> >> > maintain >>>>> >> > static mappings and I don't have the time or manpower to go back >>>>> >> > and >>>>> >> > replace >>>>> >> > ORM use with more direct query text building, >>>>> >> >>>>> >> This has not been suggested as your only option. >>>>> >> >>>>> >> > so I'll probably just dig in >>>>> >> > and find a way to undo what SQLAlchemy is doing in the global space >>>>> >> > -- >>>>> >> > maybe >>>>> >> > remove any mappers in _mapper_registry that have _configure_failed >>>>> >> > as >>>>> >> > true, >>>>> >> > when these exceptions occur, since that seems to be precisely >>>>> >> > what's >>>>> >> > preventing subsequent attempts from even being tried. >>>>> >> >>>>> >> That's a system that's been that way for about 9 years, and like any >>>>> >> system it certainly can be changed if a new use case is introduced >>>>> >> which has no reasonable alternative, but to undertake such a change >>>>> >> would need a well defined rationale which I haven't gotten here. >>>>> >> >>>>> >> >>>>> >> >>>>> >> >>>>> >> > >>>>> >> > On Fri, May 25, 2018 at 6:44 AM, Mike Bayer >>>>> >> > <[email protected]> >>>>> >> > wrote: >>>>> >> >> >>>>> >> >> Here's a second demo why _mapper_registry exists and has to be >>>>> >> >> global: >>>>> >> >> >>>>> >> >> from sqlalchemy import * >>>>> >> >> from sqlalchemy.orm import * >>>>> >> >> from sqlalchemy.ext.declarative import declarative_base >>>>> >> >> from sqlalchemy import inspect >>>>> >> >> >>>>> >> >> Base = declarative_base() >>>>> >> >> >>>>> >> >> >>>>> >> >> class A(Base): >>>>> >> >> __tablename__ = 'a' >>>>> >> >> >>>>> >> >> id = Column(Integer, primary_key=True) >>>>> >> >> data = Column(String) >>>>> >> >> >>>>> >> >> >>>>> >> >> class B(Base): >>>>> >> >> __tablename__ = 'b' >>>>> >> >> >>>>> >> >> id = Column(Integer, primary_key=True) >>>>> >> >> aid = Column(ForeignKey('a.id')) >>>>> >> >> data = Column(String) >>>>> >> >> a = relationship("A", backref="bs") >>>>> >> >> >>>>> >> >> >>>>> >> >> e = create_engine("sqlite://", echo=True) >>>>> >> >> Base.metadata.create_all(e) >>>>> >> >> >>>>> >> >> # let's assume SQLAlchemy did not keep a global list of all >>>>> >> >> mappers. >>>>> >> >> # let's take away "configure_mappers" and instead only configure >>>>> >> >> mappers >>>>> >> >> # as the program refers to them. Patch out configure_mappers to >>>>> >> >> do >>>>> >> >> nothing. >>>>> >> >> from sqlalchemy.orm import mapperlib >>>>> >> >> mapperlib.configure_mappers = lambda: None >>>>> >> >> >>>>> >> >> >>>>> >> >> # here's the specific steps configure_mappers() needs to do for a >>>>> >> >> particular >>>>> >> >> # mapper. >>>>> >> >> def configure_mapper(cls): >>>>> >> >> a_mapper = inspect(cls).mapper >>>>> >> >> a_mapper._post_configure_properties() >>>>> >> >> a_mapper._expire_memoizations() >>>>> >> >> a_mapper.dispatch.mapper_configured( >>>>> >> >> a_mapper, a_mapper.class_) >>>>> >> >> >>>>> >> >> # about to use "A". Let's configure: >>>>> >> >> configure_mapper(A) >>>>> >> >> a1 = A() >>>>> >> >> >>>>> >> >> # check the a1.bs collection. Except. There isn't one :) >>>>> >> >> mappers >>>>> >> >> often >>>>> >> >> # contain instructions to add attributes to other mappers. if the >>>>> >> >> program >>>>> >> >> # refers to those dependent mappers first, we need to have >>>>> >> >> configured >>>>> >> >> # all mappers. if one of the mappers is failing, that's a bug in >>>>> >> >> the >>>>> >> >> program. >>>>> >> >> # the program is not expected to be able to run with bad mappings >>>>> >> >> in >>>>> >> >> it. >>>>> >> >> assert a1.bs == [] >>>>> >> >> >>>>> >> >> # program has failed >>>>> >> >> >>>>> >> >> # maybe we could use the "Base" here as the collection, not global >>>>> >> >> _mapper_registry. >>>>> >> >> # but we support relationships and other linkages between mappers >>>>> >> >> that >>>>> >> >> have different >>>>> >> >> # "bases", and declarative is only an extension to the core >>>>> >> >> mapping >>>>> >> >> system in any case. >>>>> >> >> # perhaps a "SQLAlchemy 2" can tighten and modernize this system >>>>> >> >> so >>>>> >> >> that all mappers >>>>> >> >> # are truly segmented into independent namespaces but this can >>>>> >> >> also >>>>> >> >> cause more confusion >>>>> >> >> # too. Overall, programs just aren't expected to have invalid >>>>> >> >> mappings. >>>>> >> >> >>>>> >> >> >>>>> >> >> a1bs = a1.bs >>>>> >> >> >>>>> >> >> # about to use "B". Let's configure: >>>>> >> >> configure_mapper(B) >>>>> >> >> a1bs.append(B()) >>>>> >> >> >>>>> >> >> >>>>> >> >> >>>>> >> >> On Fri, May 25, 2018 at 9:24 AM, Mike Bayer >>>>> >> >> <[email protected]> >>>>> >> >> wrote: >>>>> >> >> > Here's a demo, you want to also call gc_collect() when you >>>>> >> >> > dispose of >>>>> >> >> > your failed mappers: >>>>> >> >> > >>>>> >> >> > from sqlalchemy import * >>>>> >> >> > from sqlalchemy.orm import * >>>>> >> >> > from sqlalchemy.ext.declarative import declarative_base >>>>> >> >> > from sqlalchemy import exc >>>>> >> >> > >>>>> >> >> > Base = declarative_base() >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > class A(Base): >>>>> >> >> > __tablename__ = 'a' >>>>> >> >> > >>>>> >> >> > id = Column(Integer, primary_key=True) >>>>> >> >> > data = Column(String) >>>>> >> >> > bs = relationship("B") >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > class B(Base): >>>>> >> >> > __tablename__ = 'b' >>>>> >> >> > >>>>> >> >> > id = Column(Integer, primary_key=True) >>>>> >> >> > aid = Column(ForeignKey('a.id')) >>>>> >> >> > data = Column(String) >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > # bad mapper >>>>> >> >> > class C(Base): >>>>> >> >> > __tablename__ = 'c' >>>>> >> >> > >>>>> >> >> > id = Column(Integer, primary_key=True) >>>>> >> >> > >>>>> >> >> > # nonexistent relationship >>>>> >> >> > q = relationship("q") >>>>> >> >> > >>>>> >> >> > try: >>>>> >> >> > configure_mappers() >>>>> >> >> > except exc.InvalidRequestError as err: >>>>> >> >> > print("First exception: %s" % err) >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > class D(Base): >>>>> >> >> > __tablename__ = 'd' >>>>> >> >> > id = Column(Integer, primary_key=True) >>>>> >> >> > aid = Column(ForeignKey('a.id')) >>>>> >> >> > a = relationship("A") >>>>> >> >> > >>>>> >> >> > # other mappers are blocked >>>>> >> >> > try: >>>>> >> >> > Session().query(D) >>>>> >> >> > except exc.InvalidRequestError as err: >>>>> >> >> > print("Second exception: %s" % err) >>>>> >> >> > >>>>> >> >> > # delete offending class >>>>> >> >> > del C >>>>> >> >> > >>>>> >> >> > # garbage collect, as mappings contain cycles >>>>> >> >> > import gc >>>>> >> >> > gc.collect() >>>>> >> >> > >>>>> >> >> > # mapper is gone >>>>> >> >> > print(Session().query(D)) >>>>> >> >> > print("it worked!") >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > >>>>> >> >> > On Fri, May 25, 2018 at 9:16 AM, Mike Bayer >>>>> >> >> > <[email protected]> >>>>> >> >> > wrote: >>>>> >> >> >> On Fri, May 25, 2018 at 7:11 AM, Dave Mittner >>>>> >> >> >> <[email protected]> >>>>> >> >> >> wrote: >>>>> >> >> >>>> So that is already a new fact (which i sort of guessed might >>>>> >> >> >>>> be in >>>>> >> >> >>>> play) that this is a multi-tenant system. How many databases >>>>> >> >> >>>> are >>>>> >> >> >>>> we >>>>> >> >> >>>> talking about that are accessed by a single application? >>>>> >> >> >>>> What do >>>>> >> >> >>>> the >>>>> >> >> >>>> tables in these databases look like, are they all the same >>>>> >> >> >>>> across >>>>> >> >> >>>> all >>>>> >> >> >>>> DBs (in which case use fixed mappings) or are they totally >>>>> >> >> >>>> different? >>>>> >> >> >>>> if all the DBs have the same table structure then you should >>>>> >> >> >>>> use >>>>> >> >> >>>> only >>>>> >> >> >>>> a single table/mapping structure for each DB. >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> There's probably something like 5 or 6 distinct MySQL >>>>> >> >> >>> databases I >>>>> >> >> >>> connect >>>>> >> >> >>> to, each on a separate host, each unique. That's why I cache >>>>> >> >> >>> the >>>>> >> >> >>> automapped >>>>> >> >> >>> classes on a per-server-per-host basis.. >>>>> >> >> >> >>>>> >> >> >> the critical questions are: 1. do each of these databases >>>>> >> >> >> have the >>>>> >> >> >> same table structures? or if not 2. are these *fixed* >>>>> >> >> >> structures >>>>> >> >> >> that you could map statically without using reflection? >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >>> >>>>> >> >> >>>> I've never seen that before but I might guess that you have >>>>> >> >> >>>> multiple threads reflecting tables and creating classes of >>>>> >> >> >>>> the >>>>> >> >> >>>> identical name in different threads at the same time? You >>>>> >> >> >>>> definitely >>>>> >> >> >>>> can't do that without modifying how the >>>>> >> >> >>>> sqlalchemy.orm.mapper._mapper_registry works. You need to >>>>> >> >> >>>> either >>>>> >> >> >>>> ensure these names are unique at all times, and if you expect >>>>> >> >> >>>> multiple >>>>> >> >> >>>> threads to access the same names, you need to use a mutex to >>>>> >> >> >>>> prevent >>>>> >> >> >>>> them from doing so concurrently. >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> I'm not entirely sure I understand what you're getting at >>>>> >> >> >>> here. >>>>> >> >> >>> Before >>>>> >> >> >>> I >>>>> >> >> >>> even added the caching, I was fully able to automap the same >>>>> >> >> >>> database >>>>> >> >> >>> over >>>>> >> >> >>> and over again on each new connection without any issue. And >>>>> >> >> >>> it >>>>> >> >> >>> functions >>>>> >> >> >>> perfectly fine now with multiple threads running multiple >>>>> >> >> >>> connections >>>>> >> >> >>> to the >>>>> >> >> >>> same database. The only problem I'm having is after an automap >>>>> >> >> >>> fails >>>>> >> >> >>> to >>>>> >> >> >>> properly read a database structure and map it to classes, >>>>> >> >> >>> which >>>>> >> >> >>> happens only >>>>> >> >> >>> rarely. >>>>> >> >> >> >>>>> >> >> >> This statement does not make sense without more information. >>>>> >> >> >> automap >>>>> >> >> >> either succeeds against a given database, or it fails. It is >>>>> >> >> >> not >>>>> >> >> >> easy >>>>> >> >> >> to produce a situation where it fails "only rarely". Things >>>>> >> >> >> that >>>>> >> >> >> could cause this are: 1. the database you are automapping >>>>> >> >> >> against is >>>>> >> >> >> constantly changing, 2. the mapping is relying upon some >>>>> >> >> >> subtle >>>>> >> >> >> hash >>>>> >> >> >> ordering or 3. automap is being used in a multi-threaded >>>>> >> >> >> context >>>>> >> >> >> where >>>>> >> >> >> more than one automap tries to hit the same tables at the same >>>>> >> >> >> time. >>>>> >> >> >> When something happens "only rarely", that points to race >>>>> >> >> >> conditions >>>>> >> >> >> or sometimes hash ordering issues. To rule out #2, you >>>>> >> >> >> would >>>>> >> >> >> create a test script that does your automap by itself and try >>>>> >> >> >> to run >>>>> >> >> >> automap hundreds of times against the particular database that >>>>> >> >> >> caused >>>>> >> >> >> a failure, if under Python 2 you'd want to set >>>>> >> >> >> PYTHONHASHSEED='random' >>>>> >> >> >> in the environment. But the error you showed looks more like >>>>> >> >> >> a >>>>> >> >> >> race >>>>> >> >> >> of two automaps hitting the same table. >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >> What you're suggesting seems to indicate I should be having >>>>> >> >> >> problems >>>>> >> >> >>> constantly, but I'm not. >>>>> >> >> >> >>>>> >> >> >> the prevalence of a race condition is proportional to how >>>>> >> >> >> unlikely >>>>> >> >> >> the >>>>> >> >> >> race is in the first place and how much concurrency is in play >>>>> >> >> >> on a >>>>> >> >> >> given basis. Whether it happens every minute or once a month >>>>> >> >> >> doesn't >>>>> >> >> >> really matter. >>>>> >> >> >> >>>>> >> >> >>> >>>>> >> >> >>>> How does your program know exactly how to interact with these >>>>> >> >> >>>> automap >>>>> >> >> >>>> databases if it knows nothing of what tables are present or >>>>> >> >> >>>> their >>>>> >> >> >>>> structure? If your application *does* know these things, >>>>> >> >> >>>> then >>>>> >> >> >>>> you >>>>> >> >> >>>> should tell automap about it. >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> Well, the entire point of using automap is to make the data >>>>> >> >> >>> easily >>>>> >> >> >>> accessible through the mapped objects, so the higher level >>>>> >> >> >>> code >>>>> >> >> >>> certainly >>>>> >> >> >>> knows what it's doing. This low-level DB class, however, is >>>>> >> >> >>> only >>>>> >> >> >>> meant >>>>> >> >> >>> to >>>>> >> >> >>> expedite the connection configuration process. Ostensibly the >>>>> >> >> >>> higher >>>>> >> >> >>> level >>>>> >> >> >>> code could pass more information into the lower level class in >>>>> >> >> >>> terms >>>>> >> >> >>> of >>>>> >> >> >>> tables it'll be using and so forth, but that's a pretty >>>>> >> >> >>> expensive >>>>> >> >> >>> way >>>>> >> >> >>> to >>>>> >> >> >>> bypass this problem. >>>>> >> >> >> >>>>> >> >> >> If the schemas of these databases are fixed, it's typical to >>>>> >> >> >> have >>>>> >> >> >> them >>>>> >> >> >> mapped up front. Since you have a finite, small number of >>>>> >> >> >> databases, >>>>> >> >> >> you should look into having them mapped either up front or >>>>> >> >> >> within a >>>>> >> >> >> section that is mutexed by a threading.Lock, and in either case >>>>> >> >> >> you >>>>> >> >> >> call configure_mappers() as soon as they are all set up. >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >>> >>>>> >> >> >>>> As far as legitimate mappings failing afterwards, that's only >>>>> >> >> >>>> if >>>>> >> >> >>>> you >>>>> >> >> >>>> don't get rid of these failed mappers. If you "del" a mapper >>>>> >> >> >>>> that >>>>> >> >> >>>> failed to configure and make sure it is garbage collected, it >>>>> >> >> >>>> will >>>>> >> >> >>>> not >>>>> >> >> >>>> interfere with subsequent mappings. so you probably want to >>>>> >> >> >>>> call >>>>> >> >> >>>> configure_mappers(), then if it fails, make sure you lose >>>>> >> >> >>>> references >>>>> >> >> >>>> to those mappers that failed. >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> As I indicated in my original post, it's not my code that's >>>>> >> >> >>> holding >>>>> >> >> >>> on >>>>> >> >> >>> to >>>>> >> >> >>> the fact that a mapping failed. It's being done in the global >>>>> >> >> >>> space >>>>> >> >> >>> within >>>>> >> >> >>> SQLAlchemy, itself. That's what I find utterly mind boggling. >>>>> >> >> >>> I >>>>> >> >> >>> don't >>>>> >> >> >>> know >>>>> >> >> >>> why it would hold on to anything in the global space and >>>>> >> >> >>> certainly >>>>> >> >> >>> not >>>>> >> >> >>> trigger failures on subsequent mapping attempts if a past >>>>> >> >> >>> attempt >>>>> >> >> >>> failed, >>>>> >> >> >>> but that's exactly what it's doing. >>>>> >> >> >>> >>>>> >> >> >>> You can see the code here: >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/mapper.py#L3017 >>>>> >> >> >> >>>>> >> >> >> SQLAlchemy does not hold onto mappers that your program does >>>>> >> >> >> not >>>>> >> >> >> refer >>>>> >> >> >> towards. I can demonstrate this if you like. The code at >>>>> >> >> >> 3017 is >>>>> >> >> >> only invoked if the mapper is still in the _mapper_registry, >>>>> >> >> >> which >>>>> >> >> >> is >>>>> >> >> >> a WeakKeyDictionary. SQLAlchemy only maintains weak references >>>>> >> >> >> to a >>>>> >> >> >> particular mapper. >>>>> >> >> >> >>>>> >> >> >> Now, there are a lot of challenges in getting your program to >>>>> >> >> >> no >>>>> >> >> >> longer refer to a particular mapper, depending on what you're >>>>> >> >> >> doing. >>>>> >> >> >> If other mappers extend from your mapper, that's a strong >>>>> >> >> >> reference. >>>>> >> >> >> If other mappers refer to your mapper via relationship(), thats >>>>> >> >> >> a >>>>> >> >> >> strong reference. But here, if you are keeping these >>>>> >> >> >> per-connection >>>>> >> >> >> mappings all independent of each other, they should get garbage >>>>> >> >> >> collected. But if there's a bug in the weak referencing in >>>>> >> >> >> SQLAlchemy or there's a particularly difficult mapping pattern >>>>> >> >> >> in >>>>> >> >> >> your >>>>> >> >> >> program you need help releasing, I'd need to see specific >>>>> >> >> >> scripts >>>>> >> >> >> that >>>>> >> >> >> illustrate this. >>>>> >> >> >> >>>>> >> >> >> >>>>> >> >> >>> >>>>> >> >> >>> On Thu, May 24, 2018 at 8:11 PM, Mike Bayer >>>>> >> >> >>> <[email protected]> >>>>> >> >> >>> wrote: >>>>> >> >> >>>> >>>>> >> >> >>>> On Thu, May 24, 2018 at 10:34 PM, Dave Mittner >>>>> >> >> >>>> <[email protected]> >>>>> >> >> >>>> wrote: >>>>> >> >> >>>> > Automap was, at the most regular, only ever used upon >>>>> >> >> >>>> > connection >>>>> >> >> >>>> > creation to >>>>> >> >> >>>> > a given database. (obvious implication is that DB changes >>>>> >> >> >>>> > within >>>>> >> >> >>>> > a >>>>> >> >> >>>> > connection might be problematic? not an applicable scenario >>>>> >> >> >>>> > thus >>>>> >> >> >>>> > far in >>>>> >> >> >>>> > our >>>>> >> >> >>>> > code) >>>>> >> >> >>>> >>>>> >> >> >>>> So that is already a new fact (which i sort of guessed might >>>>> >> >> >>>> be in >>>>> >> >> >>>> play) that this is a multi-tenant system. How many databases >>>>> >> >> >>>> are >>>>> >> >> >>>> we >>>>> >> >> >>>> talking about that are accessed by a single application? >>>>> >> >> >>>> What do >>>>> >> >> >>>> the >>>>> >> >> >>>> tables in these databases look like, are they all the same >>>>> >> >> >>>> across >>>>> >> >> >>>> all >>>>> >> >> >>>> DBs (in which case use fixed mappings) or are they totally >>>>> >> >> >>>> different? >>>>> >> >> >>>> if all the DBs have the same table structure then you should >>>>> >> >> >>>> use >>>>> >> >> >>>> only >>>>> >> >> >>>> a single table/mapping structure for each DB. >>>>> >> >> >>>> >>>>> >> >> >>>> If I am reading the error you are getting over at >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> https://stackoverflow.com/questions/50123090/application-process-unusable-after-cant-proceed-with-initialization-of-o >>>>> >> >> >>>> correctly, I've never seen that before but I might guess that >>>>> >> >> >>>> you >>>>> >> >> >>>> have >>>>> >> >> >>>> multiple threads reflecting tables and creating classes of >>>>> >> >> >>>> the >>>>> >> >> >>>> identical name in different threads at the same time? You >>>>> >> >> >>>> definitely >>>>> >> >> >>>> can't do that without modifying how the >>>>> >> >> >>>> sqlalchemy.orm.mapper._mapper_registry works. You need to >>>>> >> >> >>>> either >>>>> >> >> >>>> ensure these names are unique at all times, and if you expect >>>>> >> >> >>>> multiple >>>>> >> >> >>>> threads to access the same names, you need to use a mutex to >>>>> >> >> >>>> prevent >>>>> >> >> >>>> them from doing so concurrently. >>>>> >> >> >>>> >>>>> >> >> >>>> > >>>>> >> >> >>>> > Here's the gist of my connection initiation code that's run >>>>> >> >> >>>> > upon >>>>> >> >> >>>> > instantiation of the DB class. >>>>> >> >> >>>> > Mind you, a prior coworker wrote this and there's so many >>>>> >> >> >>>> > ways >>>>> >> >> >>>> > of >>>>> >> >> >>>> > interfacing with SQLAlchemy that I'm not even sure where he >>>>> >> >> >>>> > got >>>>> >> >> >>>> > it >>>>> >> >> >>>> > from. >>>>> >> >> >>>> > >>>>> >> >> >>>> >> db_url = engine.url.URL(drivername = >>>>> >> >> >>>> >> self.drivername, >>>>> >> >> >>>> >> username = >>>>> >> >> >>>> >> self.username, >>>>> >> >> >>>> >> password = >>>>> >> >> >>>> >> self.password, >>>>> >> >> >>>> >> host = self.host, >>>>> >> >> >>>> >> database = >>>>> >> >> >>>> >> self.database, >>>>> >> >> >>>> >> query = >>>>> >> >> >>>> >> {'charset':'utf8'}) >>>>> >> >> >>>> >> self.engine = >>>>> >> >> >>>> >> create_engine(db_url,encoding='utf8',convert_unicode=True) >>>>> >> >> >>>> >> self.session = Session(self.engine) >>>>> >> >> >>>> >> self.connection = self.engine.connect() >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> # Automap functionality has a lot of overhead, >>>>> >> >> >>>> >> so >>>>> >> >> >>>> >> cache the >>>>> >> >> >>>> >> results on a per-host/database basis >>>>> >> >> >>>> >> id = (self.drivername,self.host,self.database) >>>>> >> >> >>>> >> if id not in DB.tables: >>>>> >> >> >>>> >> base = automap_base() >>>>> >> >> >>>> >> base.prepare(self.engine, reflect=True, >>>>> >> >> >>>> >> name_for_scalar_relationship=name_for_scalar_relationship) >>>>> >> >> >>>> >> DB.tables[id] = base.classes >>>>> >> >> >>>> >> self.tables = DB.tables[id] >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> How does your program know exactly how to interact with these >>>>> >> >> >>>> automap >>>>> >> >> >>>> databases if it knows nothing of what tables are present or >>>>> >> >> >>>> their >>>>> >> >> >>>> structure? If your application *does* know these things, >>>>> >> >> >>>> then >>>>> >> >> >>>> you >>>>> >> >> >>>> should tell automap about it. Especially if only care about >>>>> >> >> >>>> three >>>>> >> >> >>>> tables, use metadata.reflect() and pass those names to >>>>> >> >> >>>> "only": >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=metadata%20reflect#sqlalchemy.schema.MetaData.reflect.params.only >>>>> >> >> >>>> . Further, your application, assuming it isn't just letting >>>>> >> >> >>>> users >>>>> >> >> >>>> query whatever they want, would need to know about specific >>>>> >> >> >>>> fields >>>>> >> >> >>>> and >>>>> >> >> >>>> columns on these tables in order to work with queries and >>>>> >> >> >>>> mapped >>>>> >> >> >>>> objects effectively. You can map these fields and columns up >>>>> >> >> >>>> front >>>>> >> >> >>>> since you know what they are, without using automap. then >>>>> >> >> >>>> you >>>>> >> >> >>>> add >>>>> >> >> >>>> unit tests for these mappings to make sure they work. that's >>>>> >> >> >>>> the >>>>> >> >> >>>> typical structure of a live production application. >>>>> >> >> >>>> >>>>> >> >> >>>> automap probably can use another green warning box at >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#module-sqlalchemy.ext.automap >>>>> >> >> >>>> but the idea is that it's intended for expedient access to a >>>>> >> >> >>>> particular database in an "offline", ad-hoc context. It >>>>> >> >> >>>> isn't >>>>> >> >> >>>> oriented towards high capacity use in a production >>>>> >> >> >>>> application >>>>> >> >> >>>> against >>>>> >> >> >>>> databases of unknown structure, because that's not generally >>>>> >> >> >>>> useful >>>>> >> >> >>>> anyway, a high capacity production application would have a >>>>> >> >> >>>> more >>>>> >> >> >>>> formalized notion of its schema. automap expects to fail >>>>> >> >> >>>> against >>>>> >> >> >>>> an >>>>> >> >> >>>> unknown database until it is customized to work around the >>>>> >> >> >>>> issues >>>>> >> >> >>>> in >>>>> >> >> >>>> that DB, such as the functions for resolving naming conflicts >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> (http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#handling-simple-naming-conflicts). >>>>> >> >> >>>> >>>>> >> >> >>>> As far as legitimate mappings failing afterwards, that's only >>>>> >> >> >>>> if >>>>> >> >> >>>> you >>>>> >> >> >>>> don't get rid of these failed mappers. If you "del" a mapper >>>>> >> >> >>>> that >>>>> >> >> >>>> failed to configure and make sure it is garbage collected, it >>>>> >> >> >>>> will >>>>> >> >> >>>> not >>>>> >> >> >>>> interfere with subsequent mappings. so you probably want to >>>>> >> >> >>>> call >>>>> >> >> >>>> configure_mappers(), then if it fails, make sure you lose >>>>> >> >> >>>> references >>>>> >> >> >>>> to those mappers that failed. >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > On Thu, May 24, 2018 at 5:50 PM, Mike Bayer >>>>> >> >> >>>> > <[email protected]> >>>>> >> >> >>>> > wrote: >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> On Thu, May 24, 2018 at 5:40 PM, Dave Mittner >>>>> >> >> >>>> >> <[email protected]> >>>>> >> >> >>>> >> wrote: >>>>> >> >> >>>> >> > Also posted here: >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > https://stackoverflow.com/questions/50123090/application-process-unusable-after-cant-proceed-with-initialization-of-o >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > I have a multithreaded application that runs various >>>>> >> >> >>>> >> > jobs in >>>>> >> >> >>>> >> > threads. >>>>> >> >> >>>> >> > One of >>>>> >> >> >>>> >> > these jobs goes out to various data sources to query for >>>>> >> >> >>>> >> > data. >>>>> >> >> >>>> >> > On >>>>> >> >> >>>> >> > occasion >>>>> >> >> >>>> >> > the mapping process fails and an exception is thrown. >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > That on its own isn't a big deal; my system is designed >>>>> >> >> >>>> >> > to >>>>> >> >> >>>> >> > compensate >>>>> >> >> >>>> >> > for >>>>> >> >> >>>> >> > periodically failing jobs. >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > The problem is that that mapping failure seems to be >>>>> >> >> >>>> >> > recorded >>>>> >> >> >>>> >> > in >>>>> >> >> >>>> >> > a >>>>> >> >> >>>> >> > global >>>>> >> >> >>>> >> > space that then prevents all future mapping attempts to >>>>> >> >> >>>> >> > be >>>>> >> >> >>>> >> > aborted. >>>>> >> >> >>>> >> > Even >>>>> >> >> >>>> >> > attempts on completely different threads using >>>>> >> >> >>>> >> > completely >>>>> >> >> >>>> >> > different >>>>> >> >> >>>> >> > databases. This renders my entire application >>>>> >> >> >>>> >> > effectively >>>>> >> >> >>>> >> > broken >>>>> >> >> >>>> >> > from >>>>> >> >> >>>> >> > that >>>>> >> >> >>>> >> > point on. >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > After looking in SQLAlchemy's code, mappers are stored >>>>> >> >> >>>> >> > in a >>>>> >> >> >>>> >> > _mapper_registry >>>>> >> >> >>>> >> > global space variable and once any mapper in the >>>>> >> >> >>>> >> > registry >>>>> >> >> >>>> >> > errors >>>>> >> >> >>>> >> > out, >>>>> >> >> >>>> >> > any >>>>> >> >> >>>> >> > attempt to configure a new mapper will fail. >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > Mapping failures of this nature may be rare -- and >>>>> >> >> >>>> >> > indeed it >>>>> >> >> >>>> >> > only >>>>> >> >> >>>> >> > rarely >>>>> >> >> >>>> >> > happens on the connection I'm having a problem with -- >>>>> >> >> >>>> >> > but >>>>> >> >> >>>> >> > this >>>>> >> >> >>>> >> > complete >>>>> >> >> >>>> >> > locking behavior of all future mapping seems very odd to >>>>> >> >> >>>> >> > me. >>>>> >> >> >>>> >> > If >>>>> >> >> >>>> >> > there >>>>> >> >> >>>> >> > isn't >>>>> >> >> >>>> >> > a way around this I might have no choice but to have my >>>>> >> >> >>>> >> > process >>>>> >> >> >>>> >> > completely >>>>> >> >> >>>> >> > exit when the exception is encountered, even if that >>>>> >> >> >>>> >> > means >>>>> >> >> >>>> >> > killing >>>>> >> >> >>>> >> > other >>>>> >> >> >>>> >> > running threads. >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> are you creating mappers on the fly or on a per-request >>>>> >> >> >>>> >> basis? >>>>> >> >> >>>> >> You'd >>>>> >> >> >>>> >> want to ideally have mappings created just once at the >>>>> >> >> >>>> >> module >>>>> >> >> >>>> >> import >>>>> >> >> >>>> >> level. Then when your application is ready to start up, >>>>> >> >> >>>> >> call >>>>> >> >> >>>> >> configure_mappers() and everything will be set up. >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> if those are not patterns you're able to use, then please >>>>> >> >> >>>> >> provide >>>>> >> >> >>>> >> more >>>>> >> >> >>>> >> specifics. from your stack trace on SO, it seems like >>>>> >> >> >>>> >> you are >>>>> >> >> >>>> >> using >>>>> >> >> >>>> >> automap. When is that running? If per request, this >>>>> >> >> >>>> >> very >>>>> >> >> >>>> >> expensive >>>>> >> >> >>>> >> and will have problems. >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> The mapping process *is* guarded by a mutex so it is >>>>> >> >> >>>> >> difficult >>>>> >> >> >>>> >> to >>>>> >> >> >>>> >> produce an issue with mappings failing - the stack trace >>>>> >> >> >>>> >> you >>>>> >> >> >>>> >> post >>>>> >> >> >>>> >> almost appears like there is some kind of naming issue >>>>> >> >> >>>> >> happening >>>>> >> >> >>>> >> where >>>>> >> >> >>>> >> a particular mapper has been garbage collected or >>>>> >> >> >>>> >> something >>>>> >> >> >>>> >> like >>>>> >> >> >>>> >> that >>>>> >> >> >>>> >> yet still being referred towards by other mappers that are >>>>> >> >> >>>> >> being >>>>> >> >> >>>> >> configured. need to see details of how your code >>>>> >> >> >>>> >> works. >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > Any ideas? >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > -- >>>>> >> >> >>>> >> > SQLAlchemy - >>>>> >> >> >>>> >> > The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > http://www.sqlalchemy.org/ >>>>> >> >> >>>> >> > >>>>> >> >> >>>> >> > To post example code, please provide an MCVE: Minimal, >>>>> >> >> >>>> >> > Complete, >>>>> >> >> >>>> >> > and >>>>> >> >> >>>> >> > Verifiable Example. See >>>>> >> >> >>>> >> > http://stackoverflow.com/help/mcve >>>>> >> >> >>>> >> > for a >>>>> >> >> >>>> >> > full >>>>> >> >> >>>> >> > description. >>>>> >> >> >>>> >> > --- >>>>> >> >> >>>> >> > You received this message because you are subscribed to >>>>> >> >> >>>> >> > the >>>>> >> >> >>>> >> > Google >>>>> >> >> >>>> >> > Groups >>>>> >> >> >>>> >> > "sqlalchemy" group. >>>>> >> >> >>>> >> > To unsubscribe from this group and stop receiving emails >>>>> >> >> >>>> >> > from >>>>> >> >> >>>> >> > it, >>>>> >> >> >>>> >> > send >>>>> >> >> >>>> >> > an >>>>> >> >> >>>> >> > email to [email protected]. >>>>> >> >> >>>> >> > To post to this group, send email to >>>>> >> >> >>>> >> > [email protected]. >>>>> >> >> >>>> >> > Visit this group at >>>>> >> >> >>>> >> > https://groups.google.com/group/sqlalchemy. >>>>> >> >> >>>> >> > For more options, visit >>>>> >> >> >>>> >> > https://groups.google.com/d/optout. >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> -- >>>>> >> >> >>>> >> SQLAlchemy - >>>>> >> >> >>>> >> The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> http://www.sqlalchemy.org/ >>>>> >> >> >>>> >> >>>>> >> >> >>>> >> To post example code, please provide an MCVE: Minimal, >>>>> >> >> >>>> >> Complete, >>>>> >> >> >>>> >> and >>>>> >> >> >>>> >> Verifiable Example. See >>>>> >> >> >>>> >> http://stackoverflow.com/help/mcve >>>>> >> >> >>>> >> for a >>>>> >> >> >>>> >> full >>>>> >> >> >>>> >> description. >>>>> >> >> >>>> >> --- >>>>> >> >> >>>> >> You received this message because you are subscribed to >>>>> >> >> >>>> >> the >>>>> >> >> >>>> >> Google >>>>> >> >> >>>> >> Groups >>>>> >> >> >>>> >> "sqlalchemy" group. >>>>> >> >> >>>> >> To unsubscribe from this group and stop receiving emails >>>>> >> >> >>>> >> from >>>>> >> >> >>>> >> it, >>>>> >> >> >>>> >> send >>>>> >> >> >>>> >> an >>>>> >> >> >>>> >> email to [email protected]. >>>>> >> >> >>>> >> To post to this group, send email to >>>>> >> >> >>>> >> [email protected]. >>>>> >> >> >>>> >> Visit this group at >>>>> >> >> >>>> >> https://groups.google.com/group/sqlalchemy. >>>>> >> >> >>>> >> For more options, visit >>>>> >> >> >>>> >> https://groups.google.com/d/optout. >>>>> >> >> >>>> > >>>>> >> >> >>>> > >>>>> >> >> >>>> > -- >>>>> >> >> >>>> > SQLAlchemy - >>>>> >> >> >>>> > The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>>> > >>>>> >> >> >>>> > http://www.sqlalchemy.org/ >>>>> >> >> >>>> > >>>>> >> >> >>>> > To post example code, please provide an MCVE: Minimal, >>>>> >> >> >>>> > Complete, >>>>> >> >> >>>> > and >>>>> >> >> >>>> > Verifiable Example. See http://stackoverflow.com/help/mcve >>>>> >> >> >>>> > for a >>>>> >> >> >>>> > full >>>>> >> >> >>>> > description. >>>>> >> >> >>>> > --- >>>>> >> >> >>>> > You received this message because you are subscribed to the >>>>> >> >> >>>> > Google >>>>> >> >> >>>> > Groups >>>>> >> >> >>>> > "sqlalchemy" group. >>>>> >> >> >>>> > To unsubscribe from this group and stop receiving emails >>>>> >> >> >>>> > from >>>>> >> >> >>>> > it, >>>>> >> >> >>>> > send >>>>> >> >> >>>> > an >>>>> >> >> >>>> > email to [email protected]. >>>>> >> >> >>>> > To post to this group, send email to >>>>> >> >> >>>> > [email protected]. >>>>> >> >> >>>> > Visit this group at >>>>> >> >> >>>> > https://groups.google.com/group/sqlalchemy. >>>>> >> >> >>>> > For more options, visit https://groups.google.com/d/optout. >>>>> >> >> >>>> >>>>> >> >> >>>> -- >>>>> >> >> >>>> SQLAlchemy - >>>>> >> >> >>>> The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>>> >>>>> >> >> >>>> http://www.sqlalchemy.org/ >>>>> >> >> >>>> >>>>> >> >> >>>> To post example code, please provide an MCVE: Minimal, >>>>> >> >> >>>> Complete, >>>>> >> >> >>>> and >>>>> >> >> >>>> Verifiable Example. See http://stackoverflow.com/help/mcve >>>>> >> >> >>>> for a >>>>> >> >> >>>> full >>>>> >> >> >>>> description. >>>>> >> >> >>>> --- >>>>> >> >> >>>> You received this message because you are subscribed to the >>>>> >> >> >>>> Google >>>>> >> >> >>>> Groups >>>>> >> >> >>>> "sqlalchemy" group. >>>>> >> >> >>>> To unsubscribe from this group and stop receiving emails from >>>>> >> >> >>>> it, >>>>> >> >> >>>> send an >>>>> >> >> >>>> email to [email protected]. >>>>> >> >> >>>> To post to this group, send email to >>>>> >> >> >>>> [email protected]. >>>>> >> >> >>>> Visit this group at >>>>> >> >> >>>> https://groups.google.com/group/sqlalchemy. >>>>> >> >> >>>> For more options, visit https://groups.google.com/d/optout. >>>>> >> >> >>> >>>>> >> >> >>> >>>>> >> >> >>> -- >>>>> >> >> >>> SQLAlchemy - >>>>> >> >> >>> The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>> >>>>> >> >> >>> http://www.sqlalchemy.org/ >>>>> >> >> >>> >>>>> >> >> >>> To post example code, please provide an MCVE: Minimal, >>>>> >> >> >>> Complete, >>>>> >> >> >>> and >>>>> >> >> >>> Verifiable Example. See http://stackoverflow.com/help/mcve for >>>>> >> >> >>> a >>>>> >> >> >>> full >>>>> >> >> >>> description. >>>>> >> >> >>> --- >>>>> >> >> >>> You received this message because you are subscribed to the >>>>> >> >> >>> Google >>>>> >> >> >>> Groups >>>>> >> >> >>> "sqlalchemy" group. >>>>> >> >> >>> To unsubscribe from this group and stop receiving emails from >>>>> >> >> >>> it, >>>>> >> >> >>> send >>>>> >> >> >>> an >>>>> >> >> >>> email to [email protected]. >>>>> >> >> >>> To post to this group, send email to >>>>> >> >> >>> [email protected]. >>>>> >> >> >>> Visit this group at >>>>> >> >> >>> https://groups.google.com/group/sqlalchemy. >>>>> >> >> >>> For more options, visit https://groups.google.com/d/optout. >>>>> >> >> >>>>> >> >> -- >>>>> >> >> SQLAlchemy - >>>>> >> >> The Python SQL Toolkit and Object Relational Mapper >>>>> >> >> >>>>> >> >> http://www.sqlalchemy.org/ >>>>> >> >> >>>>> >> >> To post example code, please provide an MCVE: Minimal, Complete, >>>>> >> >> and >>>>> >> >> Verifiable Example. See http://stackoverflow.com/help/mcve for a >>>>> >> >> full >>>>> >> >> description. >>>>> >> >> --- >>>>> >> >> You received this message because you are subscribed to the Google >>>>> >> >> Groups >>>>> >> >> "sqlalchemy" group. >>>>> >> >> To unsubscribe from this group and stop receiving emails from it, >>>>> >> >> send >>>>> >> >> an >>>>> >> >> email to [email protected]. >>>>> >> >> To post to this group, send email to [email protected]. >>>>> >> >> Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> >> >> For more options, visit https://groups.google.com/d/optout. >>>>> >> > >>>>> >> > >>>>> >> > -- >>>>> >> > SQLAlchemy - >>>>> >> > The Python SQL Toolkit and Object Relational Mapper >>>>> >> > >>>>> >> > http://www.sqlalchemy.org/ >>>>> >> > >>>>> >> > To post example code, please provide an MCVE: Minimal, Complete, >>>>> >> > and >>>>> >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a >>>>> >> > full >>>>> >> > description. >>>>> >> > --- >>>>> >> > You received this message because you are subscribed to the Google >>>>> >> > Groups >>>>> >> > "sqlalchemy" group. >>>>> >> > To unsubscribe from this group and stop receiving emails from it, >>>>> >> > send >>>>> >> > an >>>>> >> > email to [email protected]. >>>>> >> > To post to this group, send email to [email protected]. >>>>> >> > Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> >> > For more options, visit https://groups.google.com/d/optout. >>>>> >> >>>>> >> -- >>>>> >> SQLAlchemy - >>>>> >> The Python SQL Toolkit and Object Relational Mapper >>>>> >> >>>>> >> http://www.sqlalchemy.org/ >>>>> >> >>>>> >> To post example code, please provide an MCVE: Minimal, Complete, and >>>>> >> Verifiable Example. See http://stackoverflow.com/help/mcve for a >>>>> >> full >>>>> >> description. >>>>> >> --- >>>>> >> You received this message because you are subscribed to the Google >>>>> >> Groups >>>>> >> "sqlalchemy" group. >>>>> >> To unsubscribe from this group and stop receiving emails from it, >>>>> >> send an >>>>> >> email to [email protected]. >>>>> >> To post to this group, send email to [email protected]. >>>>> >> Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> >> For more options, visit https://groups.google.com/d/optout. >>>>> > >>>>> > >>>>> > -- >>>>> > SQLAlchemy - >>>>> > The Python SQL Toolkit and Object Relational Mapper >>>>> > >>>>> > http://www.sqlalchemy.org/ >>>>> > >>>>> > To post example code, please provide an MCVE: Minimal, Complete, and >>>>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>>> > description. >>>>> > --- >>>>> > You received this message because you are subscribed to the Google >>>>> > Groups >>>>> > "sqlalchemy" group. >>>>> > To unsubscribe from this group and stop receiving emails from it, send >>>>> > an >>>>> > email to [email protected]. >>>>> > To post to this group, send email to [email protected]. >>>>> > Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> > For more options, visit https://groups.google.com/d/optout. >>>>> >>>>> -- >>>>> SQLAlchemy - >>>>> The Python SQL Toolkit and Object Relational Mapper >>>>> >>>>> http://www.sqlalchemy.org/ >>>>> >>>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>>> description. >>>>> --- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "sqlalchemy" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> To post to this group, send email to [email protected]. >>>>> Visit this group at https://groups.google.com/group/sqlalchemy. >>>>> For more options, visit https://groups.google.com/d/optout. >>>> >>>> >>>> -- >>>> SQLAlchemy - >>>> The Python SQL Toolkit and Object Relational Mapper >>>> >>>> http://www.sqlalchemy.org/ >>>> >>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> description. >>>> --- >>>> You received this message because you are subscribed to the Google Groups >>>> "sqlalchemy" group. >>>> To unsubscribe from this group and stop receiving emails from it, send an >>>> email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at https://groups.google.com/group/sqlalchemy. >>>> For more options, visit https://groups.google.com/d/optout. >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
