Open Stack

-Dolph


On Mon, Jan 21, 2013 at 11:58 AM, Adam Young <ayoung at redhat.com> wrote:

> 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<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/<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.
>
>
We're specifically using backticks for table & column names in several
migrations where we use manual sql. I didn't realize that was a difference
between mysql & postgres?


>
>
>> 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 <OpenStack-dev at lists.openstack.org>
>> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>>
>
>
> ______________________________**_________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.**org <OpenStack-dev at lists.openstack.org>
> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130121/24c37c44/attachment.html>