schema version check does not differentiate between NoSuchTableError and OperationalError

[Guang Yee]

We are experiencing periodic gate failures internally at 'keystone db_sync'. The error was

"DBMigrationError: The database is not under version control, but has tables. Please stamp the current version of the schema manually."

After tracing through the code, it appears sqlalchemy-migrate does not handle temporary network failures admirably.

1. During 'keystone-manage db_sync', first thing it does is to check for the current migration version. If the migration version for the keystone migration repository does not exist in the keystone database, it will attempt to create one.

https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L73
https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L125
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/api.py#L148
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/schema.py#L33
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/schema.py#L51

2. Now the problem is that sqlalchemy-migrate is doing a catch-all and raise a generic DatabaseNotControlledError when engine.execute() failed. See

https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/schema.py#L51

engine.execute() can fail in many different ways. i.e. connection is not ready, connection is broken, connection timed out, etc. What's more, the exact error is not being logged either so correlating the exact failure is a wild geese chase odyssey.

3. Meanwhile, upper layer code is interpreting DatabaseNotControlledError as migration for the keystone repository does not exist and attempting to create one.

https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L126
https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L132
https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L153
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/api.py#L250
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/schema.py#L141
https://github.com/openstack/sqlalchemy-migrate/blob/master/migrate/versioning/schema.py#L189

And guest what, this time it found out that the migration version for the given repository has already been created, hence DatabaseAlreadyControlledError.

[Guang Yee (guang-yee)]

This error can be reproducible by simulating a temporary network failure.

1. install devstack

2. inject this code to line #43 and #52 of migrate/versioning/schema.py, prior to entering the try block and inside the except block.

raw_input('Press Enter to continue ...')

3. run 'keystone-manage db_sync', DO NOT press Enter when prompt yet.

4. on a separate window, stop mysql (sudo service mysql stop)

5. now press the Enter key to continue, this time should yield a DB connection error. At this point, you should stop at the second prompt inside the except block. DO NOT press Enter yet.

6. on a separate window, start mysql again (sudo service mysql start)

7. now press Enter again and you'll get "DBMigrationError: The database is not under version control, but has tables. Please stamp the current version of the schema manually."

[OpenStack Infra (hudson-openstack)]

Change abandoned by guang-yee (<email address hidden>) on branch: master
Review: https://review.openstack.org/358863
Reason: Nobody cares. So use local fork instead.