openstack team mailing list archive

[Jay Pipes <jaypipes@xxxxxxxxx>]

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