Open Stack

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 :)

> 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 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...

If you can help me figure out the above auth issue, I'd be more than
happy to help with this.

Best,
-jay