Open Stack

On 01/20/2013 03:43 PM, Jay Pipes wrote:
> Welcome to SQLAlchemy migrate hell. Been there, done that :) Comments
> inline...
>
> On 01/18/2013 06:12 PM, Adam Young wrote:
>> I often have to connect to the mysql Database using the command line and
>> set the DB to a known state using:
>>
>> drop database keystone; create database keystone;
> Yep, we do this in the Glance db migration tests in the test setup/teardown:
>
> https://github.com/openstack/glance/blob/master/glance/tests/unit/test_migrations.py#L93
>
> Lots of fun :)
So, while we were doing this in Keystone, it doesn't seem to be Postgres 
friendly:  You can't drop a Database from insde a Session. I was doing 
it by hand in a MySQL shell.  For Postgresl, I've been doing something 
similar from the command line:

  sudo dropdb keystone   ;   sudo puppet apply --verbose /etc/puppet/site.pp

With puppet site.pp script as:

class { 'postgresql::server': }

postgresql::db{ 'keystone':
   user          => 'keystone',
   password      => 'keystone',
   grant         => 'all',
}

I like the idea of reproducible configuration, and this is also an 
excuse for me to learn Puppet.  Obviously, this won't work for Devstack.

The goal instead has been to use the migration scripts to run cleanly, 
and use those to build up and tear down the database tables.  This has 
the risk that if we don't clean up well, we wil be poisoning later unit 
tests, but I think the advantage of using the same scripts for defining 
the database as we run against is huge, and it means out downgrade 
scripts get exercised fairly regularly, something that wasn't happening 
in the past.

>
>> Once this is done, I find that it hangs on the 3rd test:
>>
>> ./run_tests.sh -N test_sql_upgrade:SqlUpgradeTests.test_downgrade_12_to_9
>>
>> I've been running this in the debugger.  It is hanging on the downgrade
>> stage at 001_add_initial_tables.py  downgrade.  The last line iterates
>> through the tables and drops them. If hangs dropping the 'endpoint' table.
>>
>> If I look at the database while the script is hung with
>> mysql>use keystone
>> ...
>> mysql> show tables;
>> +--------------------+
>> | Tables_in_keystone |
>> +--------------------+
>> | endpoint           |
>> | migrate_version    |
>> | service            |
>> +--------------------+
>>
>> But then once I kill the script:
>> mysql> show tables;
>> +--------------------+
>> | Tables_in_keystone |
>> +--------------------+
>> | migrate_version    |
>> | service            |
>> +--------------------+
>> 2 rows in set (0.00 sec)
>>
>> Something in the script holding a reference to the endpoint table.
>>
>> Note that to get MySQL to run,  you need to roll back the "rename" patch
>> that we recently did.  I have attached a patch that does just that, as
>> well as another I was running with the ensures the session is closed in
>> all unit tests.
>>
>> I don't think this is a problem with the migration, but rather with the
>> unit tests, but I think we should treat it as if we have a unit test
>> failure until this is fixed.
> I'm trying to reproduce the above, but I can't seem to even get the
> basics to work... it keeps erroring out trying to get access with
> credentials that clearly are correct:
>
> http://paste.openstack.org/show/29665/
>
> Note that my testing connection string is:
>
> connection = mysql://root@localhost/keystone?charset=utf8
>
> Which as shown in the paste above does indeed work when connecting using
> the mysql command line tool, but an authentication error is still thrown
> for some reason :(


I used Devstack to set up MySQL for me as a starting point.  I'm not 
sure how that differs from what you are doing.
>
>> I think that Postgres is going to require an additional change. "user"
>> is a keyword in Postgres, but it is the name of one of out tables.  TO
>> escape it in Postgres, we need to modify the scripts so the table name
>> is in double quotes.  That syntax does not work with MySQL.  So the
>> scripts are going to be a little uglier moving forward.
> Which scripts exactly? SQLAlchemy-migrate Python calls for table
> renaming should properly place double-quotes (for PostgreSQL) and
> backticks (for MySQL) around table names...
So, I suspect the rename commands are what is causing the hang, as 
everyt time I trace a hang down, it is executing a rename.

The scripts that are having postgres issues are things like loading up 
the database tables with sample data and so forth.  It would seem to me 
that the postgres driver should work cleanly with it, but it doesn't in 
practice.  Lets treat this as a lower priority issue for now, and spend 
cycles on it later.

>
> If you can help me figure out the above auth issue, I'd be more than
> happy to help with this.
>
> Best,
> -jay
>
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev