← Back to team overview

openstack team mailing list archive

Re: GLANCE: How to deal with SQLAlchemy-migrate problems


Do we have patches for approach #3?  I'm thinking that if we have a few
specific issues, that maybe we could maintain our own branch and/or
monkey-patch SA-migrate, and get a better handle on the problem, and the
upstream's project receptiveness to patches (rather than bug reports).

It's a bit more work now, but avoids a major change at this stage of the
release.  One upside is that we'll hopefully have a better understanding
when it comes time to write a replacement if we decide to go down that road.

My experience of SA-migrate was that it seemed powerful, but the
documentation wasn't worth the paper it was written on :-)  It may be that
we can fix a few bugs, figure out the "SA" way to do things, and end up


On Mon, Mar 21, 2011 at 8:39 AM, Jay Pipes <jaypipes@xxxxxxxxx> wrote:

> Hey all,
> For the better part of the last week, I've been struggling to use
> SQLAlchemy-Migrate to produce database migration scripts for Glance.
> In Glance, unlike Nova, we have a unit test that tests that the
> database for Glance's registry can be downgraded and upgraded
> smoothly. In Nova, none of the migrate scripts even contain a
> downgrade method, so this isn't even testable.
> We've found a number of problems in using SA-Migrate:
> 1. When using the SQLite engine, if a table has a column with
> index=True on it and you drop any column on that table, SA-Migrate
> will die if you use Python migrate scripts
> (http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=117)
> 2. When using the MySQL engine, you cannot use SQL scripts with
> multiple statements in them as upgrade/downgrade scripts
> (http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=99)
> because SA-Migrate pipes all the statements into the
> Connection.execute() method as a single statement, causing the
> underlying MySQLdb driver to vomit an error 2014 "Commands out of
> sync"
> 3. In order to solve #1 above, you need to do your own SQL scripting
> to bypass bugs in SA-Migrate. Writing the SQL script means you run
> into #2 above. This means you can't test both SQLite and MySQL
> migrations using the same scripts. This sucks. A lot.
> 4. It seems the contributors to SA-Migrate are not responsive to bug
> reports. This makes it less than ideal as a dependency for Glance,
> especially considering the critical nature that database migrations
> play in installation and upgrading of our software.
> So, what to do? I see these options, none of which I particularly like:
> 1) Don't test migration scripts - the Nova approach. This would mean
> we write all our migrate scripts as Python scripts, which work better
> with MySQL but break completely for SQLite. No idea about PostgreSQL.
> 2) Don't use SA-Migrate and write a replacement using python-sqlparse
> and straight SQL scripts. While I can't stand SA-Migrate at this
> point, it remains really the only thing that remotely works.
> 3) Submit patches to SA-Migrate and keep patched versions of
> SA-Migrate in the Glance PPA.
> Thoughts? I'm leaning towards #2, then #1, then #3.
> -jay
> _______________________________________________
> Mailing list: https://launchpad.net/~openstack
> Post to     : openstack@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~openstack
> More help   : https://help.launchpad.net/ListHelp

Follow ups