db migration failing when upgrading glance - trying to create existing tables

[Yong Zhao]

error happened when upgrading Ubuntu to Natty

ProblemType: Package
DistroRelease: Ubuntu 11.10
Package: glance 2011.3-0ubuntu4
ProcVersionSignature: Ubuntu 2.6.38-11.48-generic 2.6.38.8
Uname: Linux 2.6.38-11-generic i686
ApportVersion: 1.23-0ubuntu3
Architecture: i386
Date: Sun Oct 30 16:47:41 2011
ErrorMessage: ErrorMessage: subprocess installed post-installation script returned error exit status 1
InstallationMedia: Ubuntu 11.04 "Natty Narwhal" - Release i386 (20110427.1)
PackageArchitecture: all
SourcePackage: glance
Title: package glance 2011.3-0ubuntu4 failed to install/upgrade: ErrorMessage: subprocess installed post-installation script returned error exit status 1
UpgradeStatus: Upgraded to oneiric on 2011-10-30 (0 days ago)

[Dave Walker (davewalker)]

Installing new version of config file /etc/init/glance-registry.conf ...
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1244, in _execute_ddl
    return self.__execute_context(context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1302, in __execute_context
    context.parameters[0], context=context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1401, in _cursor_execute
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1394, in _cursor_execute
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 299, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) table images already exists u'\nCREATE TABLE images (\n\tid INTEGER NOT NULL, \n\tname VARCHAR(255), \n\ttype VARCHAR(30), \n\tsize INTEGER, \n\tstatus VARCHAR(30) NOT NULL, \n\tis_public BOOLEAN NOT NULL, \n\tlocation TEXT, \n\tcreated_at DATETIME NOT NULL, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOLEAN NOT NULL, \n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tCHECK (is_public IN (0, 1))\n)\n\n' ()
dpkg: error processing glance (--configure):
 subprocess installed post-installation script returned error exit status 1

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Ursula Junque (ursinha)]

Steps to reproduce:
1) Install natty;
2) apt-get update/upgrade
3) apt-get install glance
4) dist-upgrade to oneiric

This is happening because glance db migration scripts are trying to create tables that already exist. Postinst script fails on line 22:

$ su -c 'glance-manage sync_db' glance

According to zul this should be fixed upstream.

[Adam Gandelman (gandelman-a)]

I believe this issue is being discussed upstream @ https://bugs.launchpad.net/glance/+bug/824794

As a side note and possible work around: On startup, it seems glance-registry automatically migrates the database. Instead of running 'glance-manage db_sync' against a natty-generated database (which always fails), simply restarting glance-registry appears to bring the db schema up to date. This doesn't solve the issue, but relying on service restart instead of glance-manage may solve the upgrade problem in the meantime.

[Adam Gandelman (gandelman-a)]

Taking a closer look at the upstream issue as well as the history of the Ubuntu package, some notes:

1. The glance.postinst on natty only attempts to run a manual database migration if the sql_connection is configured for something other than a sqlite connection, which is the default.***
2. On default installs, the tables are automatically created by sqlalchemy when glance-registry is first run. As mentioned in the upstream bug these tables are created outside of version control without the metadata needed to perform future migrations.
3. Any subsequent attempt to run manual migrations will result in migrating from version 0, thus failing because the migration attempts to create tables that already exist.

This is not only an upgrade bug, but a natty bug as well.

To reproduce on natty without upgrading:

apt-get -y install glance
glance-manage db_sync

* In current natty packaging, the postinst is broken in the case of a sql_connection to something other than sqllite, as it tries to run 'glance-manage db sync' instead of 'db_sync'. So, if I've got a config file in place to point glance to a mysql server, postinst will always fail.

[Adam Gandelman (gandelman-a)]

Please see Bug #779311 as it seems this was reported once, confirmed and discussed upstream and mistakenly closed in Ubuntu.

[Adam Gandelman (gandelman-a)]

Seems this has been deferred upstream to their next cycle, with hopefully some discussion at Openstack Developers Summit regarding standardizing database management across projects.

For Ubuntu, it might make sense to carry a patch that disables automatic table generation, and depend on the same logic we employ in Nova's packaging to migrate databases only if they are local sqlite dbs, and depend on manually migrations on upgrade/install for shared, remote databases.

[Adam Gandelman (gandelman-a)]

I've put together a small patch that disables the auto creation of database schema and instead performs a simple query at service startup via the sqlalchemy models defined in glance.db.models. These queries will fail if the tables and their columns do not match the models, meaning the database is not up to date and migration needs to be run. Database connection/permission issues are also caught here, previously they were caught during auto creation.

Should note that packaging will run the manual migration (glance-manage db_sync) if glance-registry.conf points to a local database. Otherwise, it will just restart the service. In the case of a shared database that requires a migration run, the service will fail to start completely, which IMHO is better than a database that ends up in an inconsistent state

[Adam Gandelman (gandelman-a)]

The following upstream commits address this issue:

https://review.openstack.org/#change,5863
https://review.openstack.org/#change,5862

They've been committed to milestone-proposed and should be released in Essex final.

A fix has also been merged into diablo-stable that would address the issue for Oneiric: https://review.openstack.org/#change,6077

[Adam Gandelman (gandelman-a)]

Fix released in glance 2012.1~rc3-0ubuntu1 (precise)

[dino99 (9d9)]

https://wiki.ubuntu.com/Releases