← Back to team overview

openstack team mailing list archive

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


I've done a few migration scripts in recent weeks for our nova/hpc-trunk.  It was a painful learning curve mostly because I come from the Perl generation, but I got through it.  

Is the design pattern for migrations written down somewhere?  If not, we should add some instructions to the migrate_repo/README file.  

The transition from austin/bexar.py files where all transforms are in a lump to individual files.  This does have grief This is a good change, but the template example I used didn't have a downgrade function.

bschott@island100:~/source/nova/nova-trunk/nova/db/sqlalchemy/migrate_repo/versions$ grep downgrade *.py
001_austin.py:def downgrade(migrate_engine):
008_add_instance_types.py:def downgrade(migrate_engine):
010_add_os_type_to_instances.py:def downgrade(migrate_engine):

My only suggestion to Jay is that it is perfectly acceptable to do a migration in multiple stages.  Less efficient, but it might traverse less untested code in SA-migrate.

Brian Schott

On Mar 21, 2011, at 2:34 PM, Jay Pipes wrote:

> On Mon, Mar 21, 2011 at 1:08 PM, Justin Santa Barbara
> <justin@xxxxxxxxxxxx> wrote:
>>> By "seemed powerful", did you actually try it on anything more than
>>> simple add a table/drop a table scripts? Did you try it on more than a
>>> single database? Did you use the SQL scripting as well as the Python
>>> change scripts?
>> I've written 3 or 4 - most did the simple stuff, but one also added a column
>> with a non-null default value.  I stayed away from SQL scripting because my
>> understanding is that the whole idea of SA is that we're not using raw SQL.
>> My migrations should have been easy, but SA-migrate did made them harder.
>>  However, it was the documentation that really made it much harder; for
> The docs only cover the most basic things, yeah. Unfortunately, the
> test cases in sa-migrate *also* only cover the most basic of
> operations, and many migrate patterns that are common, but more
> complex than "add a table or a column", are entirely untested code
> paths. And those code paths have bugs, as I mentioned in the original
> post. :(
>> example, it seems you should use reflection on a table you're modifying, but
>> that was mostly absent from the docs.
> Sure, you can use reflection, but actually, table reflection is what
> sa-migrate is doing "under the covers" when it processes a Python
> change script. Unfortunately, it's that reflection that produces the
> bug #1. It's a long explanation, but you can look in the code to
> verify that statement....
>> I found some posts where the mailing
>> list guys stated what I had to do for my 'non-null default' (create a
>> NULLable column, set the default value, alter the column it is non-NULL),
>> but there wasn't an actual code example, which would have made it trivial.
> Agreed, as mentioned, the examples are all (too) simplistic.
>>  However, now that it's done, it would be easy to do again.  My experience
>> suggests that this is a brick-wall learning curve that we have to get over.
> It's not a learning curve. It's bugs in sa-migrate that are the main
> issue for Glance. As mentioned, bug #1's solution is to use SQL
> scripts, but multi-statemetn SQL scripts don't work properly for
> MySQL. So you have an untenable solution.
>> Jay and I talked on IRC, and he's doing something much more complicated than
>> my "supposed to be simple" migration (he's moving a column between tables,
>> and testing with downgrades also), and his experience is that the brick wall
>> is un-scalable (and that the grass on the other side isn't even that green.)
>>  So Jay: if you want to use SQL scripts to unblock glance, then go for it.
>>  It puts the SA-migrate code in an odd limbo, which is why I'd prefer that
>> we were able to have SA-migrate work, but if the developer "on the ground"
>> says it doesn't work, then we should go by that.
>> I guess we should probably discuss the broader implications at the Design
>> Summit?
> Not sure. I posted this to get the discussion going and was hoping
> someone might have suggestions showing some alternative solution that
> I hadn't thought of.
> Cheers!
> 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