Michael, thanks for your reply, which states what I expected is going on behind the scenes. However, for the case I mentioned (the "env" field that the mapper could not find anymore) I can say for certain that this particular table was not changed at all by DDL. This really puzzled me, obviously. I actually have to admit that I have no proof that schema changes trip the application. It's just an observation I made every time this has happened. It may well be that there's something else happening every time the schema changes. Is there actually any way at all a mapper could "forget" about a column when there's no DDL change to the table?
I should really try and reproduce the problem first in a controlled environment to be sure I'm isolating the correct problem. Cheers, Sven On Oct 11, 6:12 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Oct 11, 2010, at 11:17 AM, Sven A. Schmidt wrote: > > > > > > > Hi, > > > I've got an issue which pops up from time to time with an SQLAlchemy > > based webservice application and it revolves around schema changes > > happening while an engine with autoload=True has been instantiated and > > is being used to start sessions. > > > What happens is that someone on the team will make a schema change > > (mostly backwards compatible, for example increasing the varchar size > > of a field) without asking for a restart of the webservice. Subsequent > > calls to the webservice will fail with quite obscure (i.e. seemingly > > unrelated) error messages. For example, I've seen errors like the > > following: > > > Error: type object 'AppEnvRpe2Table' has no attribute 'env' > > > (AppEnvRpe2Table is the object mapped to an sqlalchemy.Table.) First I > > thought someone had dropped the column (they'd actually do that...) > > but to my surprise the given table did have a column 'env' (and it did > > since the start). A restart of the service (i.e. reload of the schema) > > always fixed this kind of problem, which led me to believe it's > > related to the engine's internal representation of the schema somehow > > conflicting with updates in the database even when it's in places that > > aren't used in the particular query being executed. > > > Obviously, it's never a good idea to change the schema when a process > > is running, but in this case I have no control over the changes being > > made. I've warned about it but it happens anyway and then on next > > execution of the webservice a crash results. Sometimes quite a bit of > > time between the schema change and the calling of the webservice can > > occur so it's not always immediately clear that it was the schema > > change that caused the problem. (I'm actually thinking of keeping this > > issue around because I've won lots of beers when they complained about > > crashes and it turned out to be their fault to begin with but you can > > drink only so much... ;) > > > So that being the set-up, I'm wondering what I can do to remedy the > > situation. Things I considered: > > > - Re-create the engine on every request. Bad, because it increases run- > > time for every request and most of the time (i.e. when there was no > > schema change) it is unnecessary. There are quite a few tables in the > > schema and autoload time is not negligible. > > > - Re-create the engine after a schema change. The question is how do I > > programmatically notice a schema change? I can't rely on exceptions, > > because there's not a predictable error popping up when this occurs. > > > - Catch errors and retry with new engine instance. The problem is that > > I'd have to catch pretty much any exception (unpredictable errors, see > > above), and I'd possibly obscure other issues by doing so. > > > - Get an extra liver and just suffer the consequences... > > > Any ideas greatly appreciated! > > > PS: This is sqlalchemy 0.5.8 and I cannot easily update to 0.6 if that > > should be required. > > Increasing the size of a VARCHAR field in the DB will never have any effect > on the app as it runs. If the field has already been reflected, your > application will never see the new value. If the field has not yet been > reflected, your app, upon reflecting the table, will see the new size, stick > it in the type object, and never look at it again. So that's not the source > of the issue. > > The only thing that would cause any issues are renames of columns/tables, or > drops of columns/tables that are mapped. If that is the source, then the > problem has little to do with engines, and is more about mappers. If your > table has an "env" column, then the mapper will apply an "env" attribute to > the mapped object. The "no env attribute" error would therefore be a side > effect of some other schema/mapping mismatch. > > The simple fact is, if you map a class A to a table "a", a plain query(A) > will issue a SELECT for all columns explicitly: > > select x as a_x, y as a_y, z as a_z from a > > If a DBA logs in and drops column "Y" from the database, your app is now > broken, end of story. > > It's not reasonable to expect the application to gracefully handle the > removal of columns upon which the application has been constructed to rely > upon. If you are constructing mappings using autoload=True then mapping, > in effect the construction of your mappings is ad-hoc, and if your app is > constructed in such a way such that mappings are created sometime after the > app starts up, then the construction of the mappings is random. > > If you truly want to be unaffected by columns being dropped, you need to > ensure your application never has awareness of those columns. You do this > using either hardcoded Table metadata, no autoload, which don't include > columns that may be dropped, or you can apply "include_properties" / > "exclude_properties" to your mappers, again ensuring that no columns which > may be dropped are mapped in any way. > > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
