<div dir="ltr"><br><div class="gmail_extra"><br clear="all"><div><div><br></div>-Dolph</div>
<br><br><div class="gmail_quote">On Mon, Jan 21, 2013 at 11:58 AM, Adam Young <span dir="ltr"><<a href="mailto:ayoung@redhat.com" target="_blank">ayoung@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">On 01/20/2013 03:43 PM, Jay Pipes wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Welcome to SQLAlchemy migrate hell. Been there, done that :) Comments<br>
inline...<br>
<br>
On 01/18/2013 06:12 PM, Adam Young wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
I often have to connect to the mysql Database using the command line and<br>
set the DB to a known state using:<br>
<br>
drop database keystone; create database keystone;<br>
</blockquote>
Yep, we do this in the Glance db migration tests in the test setup/teardown:<br>
<br>
<a href="https://github.com/openstack/glance/blob/master/glance/tests/unit/test_migrations.py#L93" target="_blank">https://github.com/openstack/<u></u>glance/blob/master/glance/<u></u>tests/unit/test_migrations.py#<u></u>L93</a><br>
<br>
Lots of fun :)<br>
</blockquote></div>
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:<br>
<br>
sudo dropdb keystone ; sudo puppet apply --verbose /etc/puppet/site.pp<br>
<br>
With puppet site.pp script as:<br>
<br>
class { 'postgresql::server': }<br>
<br>
postgresql::db{ 'keystone':<br>
user => 'keystone',<br>
password => 'keystone',<br>
grant => 'all',<br>
}<br>
<br>
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.<br>
<br>
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.<div>
<div class="h5"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Once this is done, I find that it hangs on the 3rd test:<br>
<br>
./run_tests.sh -N test_sql_upgrade:<u></u>SqlUpgradeTests.test_<u></u>downgrade_12_to_9<br>
<br>
I've been running this in the debugger. It is hanging on the downgrade<br>
stage at 001_add_initial_tables.py downgrade. The last line iterates<br>
through the tables and drops them. If hangs dropping the 'endpoint' table.<br>
<br>
If I look at the database while the script is hung with<br>
mysql>use keystone<br>
...<br>
mysql> show tables;<br>
+--------------------+<br>
| Tables_in_keystone |<br>
+--------------------+<br>
| endpoint |<br>
| migrate_version |<br>
| service |<br>
+--------------------+<br>
<br>
But then once I kill the script:<br>
mysql> show tables;<br>
+--------------------+<br>
| Tables_in_keystone |<br>
+--------------------+<br>
| migrate_version |<br>
| service |<br>
+--------------------+<br>
2 rows in set (0.00 sec)<br>
<br>
Something in the script holding a reference to the endpoint table.<br>
<br>
Note that to get MySQL to run, you need to roll back the "rename" patch<br>
that we recently did. I have attached a patch that does just that, as<br>
well as another I was running with the ensures the session is closed in<br>
all unit tests.<br>
<br>
I don't think this is a problem with the migration, but rather with the<br>
unit tests, but I think we should treat it as if we have a unit test<br>
failure until this is fixed.<br>
</blockquote>
I'm trying to reproduce the above, but I can't seem to even get the<br>
basics to work... it keeps erroring out trying to get access with<br>
credentials that clearly are correct:<br>
<br>
<a href="http://paste.openstack.org/show/29665/" target="_blank">http://paste.openstack.org/<u></u>show/29665/</a><br>
<br>
Note that my testing connection string is:<br>
<br>
connection = mysql://root@localhost/<u></u>keystone?charset=utf8<br>
<br>
Which as shown in the paste above does indeed work when connecting using<br>
the mysql command line tool, but an authentication error is still thrown<br>
for some reason :(<br>
</blockquote>
<br>
<br></div></div>
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.<div class="im"><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
I think that Postgres is going to require an additional change. "user"<br>
is a keyword in Postgres, but it is the name of one of out tables. TO<br>
escape it in Postgres, we need to modify the scripts so the table name<br>
is in double quotes. That syntax does not work with MySQL. So the<br>
scripts are going to be a little uglier moving forward.<br>
</blockquote>
Which scripts exactly? SQLAlchemy-migrate Python calls for table<br>
renaming should properly place double-quotes (for PostgreSQL) and<br>
backticks (for MySQL) around table names...<br>
</blockquote></div>
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.<br>
<br>
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.<div class="HOEnZb">
<div class="h5"><br></div></div></blockquote><div><br></div><div style>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?</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
If you can help me figure out the above auth issue, I'd be more than<br>
happy to help with this.<br>
<br>
Best,<br>
-jay<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</blockquote>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</div></div></blockquote></div><br></div></div>