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 at
> http://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.
