DB Upgrade: expand command fails while upgrading from ocata to pike

[Abhishek Kekane]

If you try to run 'glance-manage db expand' command while upgrading from ocata to pike then it fails with below error:

InternalError: (pymysql.err.InternalError) (1060, u"Duplicate column name 'visibility'") [SQL: u"ALTER TABLE images ADD COLUMN visibility ENUM('private','public','shared','community')"]

NOTE: Same issue is reproducible on current master as well. (On master code if you run 'glance-manage db expand' command, then you will get below error.)

Steps to reproduce:
1. Setup stable/ocata
2. Pulled stable/pike branch using 'git checkout stable/pike'
3. Ran 'glance-manage db expand' command

Error:
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade mitaka02 -> ocata_expand01, add visibility to images
CRITI [glance] InternalError: (pymysql.err.InternalError) (1060, u"Duplicate column name 'visibility'") [SQL: u"ALTER TABLE images ADD COLUMN visibility ENUM('private','public','shared','community')"]
Traceback (most recent call last):
  File "./glance-manage", line 10, in <module>
    sys.exit(main())
  File "/opt/stack/glance/glance/cmd/manage.py", line 510, in main
    return CONF.command.action_fn(*func_args, **func_kwargs)
  File "/opt/stack/glance/glance/cmd/manage.py", line 183, in expand
    self.sync(version=expand_head)
  File "/opt/stack/glance/glance/cmd/manage.py", line 159, in sync
    alembic_command.upgrade(a_config, version)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 174, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script/base.py", line 416, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "/opt/stack/glance/glance/db/sqlalchemy/alembic_migrations/env.py", line 89, in <module>
    run_migrations_online()
  File "/opt/stack/glance/glance/db/sqlalchemy/alembic_migrations/env.py", line 84, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/environment.py", line 807, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/migration.py", line 321, in run_migrations
    step.migration_fn(**kw)
  File "/opt/stack/glance/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py", line 149, in upgrade
    _add_visibility_column(meta)
  File "/opt/stack/glance/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py", line 126, in _add_visibility_column
    op.add_column('images', v_col)
  File "<string>", line 8, in add_column
  File "<string>", line 3, in add_column
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/ops.py", line 1541, in add_column
    return operations.invoke(op)
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/toimpl.py", line 123, in add_column
    schema=schema
  File "/usr/local/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 172, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/usr/local/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 835, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1019, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1302, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 981, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
InternalError: (pymysql.err.InternalError) (1060, u"Duplicate column name 'visibility'") [SQL: u"ALTER TABLE images ADD COLUMN visibility ENUM('private','public','shared','community')"]

[Brian Rosmaita (brian-rosmaita)]

Need to look at https://review.openstack.org/#/c/433934 , may need to fix and merge as part of the fix for this bug.

[Michael J Burling (mburling)]

I believe whatever is causing this bug is also preventing me from installing a a fresh pike/stable implementation:

First run of `su -s /bin/sh -c "glance-manage db_sync" glance` yields:

[root@glance03 glance]# su -s /bin/sh -c "glance-manage db_sync" glance
/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/enginefacade.py:1328: OsloDBDeprecationWarning: EngineFacade is deprecated; please use oslo_db.sqlalchemy.enginefacade
  expire_on_commit=expire_on_commit, _conf=conf)
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> liberty, liberty initial
INFO [alembic.runtime.migration] Running upgrade liberty -> mitaka01, add index on created_at and updated_at columns of 'images' table
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`is_public` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_3` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
INFO [alembic.runtime.migration] Running upgrade mitaka01 -> mitaka02, update metadef os_nova_server
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
INFO [alembic.runtime.migration] Running upgrade mitaka02 -> ocata01, add visibility to and remove is_public from images
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`is_public` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_3` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`can_share` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/reflection.py:56: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
CRITI [glance] Unhandled error
Traceback (most recent call last):
  File "/usr/bin/glance-manage", line 10, in <module>
    sys.exit(main())
  File ...

[Brian Rosmaita (brian-rosmaita)]

Note: The issue mburling is seeing may actually be https://bugs.launchpad.net/glance/+bug/1697835

[Henry Spanka (henryspanka)]

Hello,
I do have the same issue, however I run a clean Pike installation and just tried to update to a patch release. After I run the migrations the error is: InternalError: (pymysql.err.InternalError) (1060, u"Duplicate column name 'visibility'") [SQL: u"ALTER TABLE images ADD COLUMN visibility ENUM('private','public','shared','community')"]

Any solution yet?

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/433934
Committed: https://git.openstack.org/cgit/openstack/glance/commit/?id=f268df1cbc3c356c472ace04bd4f2d4b3da6c026
Submitter: Zuul
Branch: master

commit f268df1cbc3c356c472ace04bd4f2d4b3da6c026
Author: shilpa.devharakar <email address hidden>
Date: Mon Jan 15 20:30:17 2018 +0530

    Modify glance manage db sync to use EMC

    Modified 'glance-manage db_sync' operation to use
    expand, migrate and contract operations.
    Added test queens scripts for testing purpose only.

    This patch removes use of "monolithic" upgrade scripts
    and resolve the issue while upgrading from ocata to pike.

    Co-Authored-By: Shilpa Devharakar <email address hidden>
    Closes-Bug: #1723001

    Change-Id: I2653560d637a6696f936b49e87f16326fd601dfe

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/glance 16.0.0.0rc1 release candidate.

[Luca Cervigni (cervigni)]

I am a bit confused, what are the steps to fix this issue if the patch has been released only for Queens? I still have to upgrade from ocata to pike.

I am running:
root@testing-glance-03:~# dpkg -l | grep glance
ii glance-api 2:15.0.0-0ubuntu1~cloud0 all OpenStack Image Registry and Delivery Service - API
ii glance-common 2:15.0.0-0ubuntu1~cloud0 all OpenStack Image Registry and Delivery Service - Common
ii glance-registry 2:15.0.0-0ubuntu1~cloud0 all OpenStack Image Registry and Delivery Service - Registry
ii glance-store-common 0.22.0-0ubuntu1~cloud0 all OpenStack Image Service store library - common files
ii python-glance 2:15.0.0-0ubuntu1~cloud0 all OpenStack Image Registry and Delivery Service - Python library
ii python-glance-store 0.22.0-0ubuntu1~cloud0 all OpenStack Image Service store library - Python 2.7
ii python-glanceclient 1:2.8.0-0ubuntu1~cloud0 all Client library for Openstack glance server - Python 2.x

Could you please help?

Thanks