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.
