sql: migration fails with embrane_lbaas_driver

[Emilien Macchi]

Migration Havana to Icehouse using HAproxy driver for LBaaS service:

root@os-ci-test10:~# neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugin.ini upgrade head
No handlers could be found for logger "neutron.common.legacy"
INFO [alembic.migration] Context impl MySQLImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade 19180cf98af6 -> 33dd0a9fa487, embrane_lbaas_driver
Traceback (most recent call last):
  File "/usr/bin/neutron-db-manage", line 10, in <module>
    sys.exit(main())
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 169, in main
    CONF.command.func(config, CONF.command.name)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 82, in do_upgrade_downgrade
    do_alembic_command(config, cmd, revision, sql=CONF.command.sql)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 60, in do_alembic_command
    getattr(alembic_command, cmd)(config, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/alembic/command.py", line 124, in upgrade
    script.run_env()
  File "/usr/lib/python2.7/dist-packages/alembic/script.py", line 199, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/lib/python2.7/dist-packages/alembic/util.py", line 199, in load_python_file
    module = load_module(module_id, path)
  File "/usr/lib/python2.7/dist-packages/alembic/compat.py", line 55, in load_module
    mod = imp.load_source(module_id, path, fp)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 103, in <module>
    run_migrations_online()
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 87, in run_migrations_online
    options=build_options())
  File "<string>", line 7, in run_migrations
  File "/usr/lib/python2.7/dist-packages/alembic/environment.py", line 652, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.7/dist-packages/alembic/migration.py", line 225, in run_migrations
    change(**kw)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/versions/33dd0a9fa487_embrane_lbaas_driver.py", line 54, in upgrade
    sa.PrimaryKeyConstraint(u'pool_id'))
  File "<string>", line 7, in create_table
  File "/usr/lib/python2.7/dist-packages/alembic/operations.py", line 647, in create_table
    self._table(name, *columns, **kw)
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 149, in create_table
    self._exec(schema.CreateTable(table))
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create table 'neutron.embrane_pool_port' (errno: 150)") '\nCREATE TABLE embrane_pool_port (\n\tpool_id VARCHAR(36) NOT NULL, \n\tport_id VARCHAR(36) NOT NULL, \n\tPRIMARY KEY (pool_id), \n\tCONSTRAINT embrane_pool_port_ibfk_1 FOREIGN KEY(pool_id) REFERENCES pools (id), \n\tCONSTRAINT embrane_pool_port_ibfk_2 FOREIGN KEY(port_id) REFERENCES ports (id)\n)\n\n' ()

[Mark McClain (markmcclain)]

What plugins (core and service) do you have enabled?

[Emilien Macchi (emilienm)]

plugin: ml2
plugins: lbaas, metering and l3_routing.

[Jakub Libosvar (libosvar)]

Can you paste output of
mysql -e "select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE from information_schema.columns where table_schema = 'neutron_ml2'"

[Emilien Macchi (emilienm)]

@Jakub: http://paste.debian.net/plain/100829

[Ann Taraday (akamyshnikova)]

Checked on devstack with configuration core plugin - Ml2, service plugins - lbaas, metering, l3. No such error was found.

[George Shuklin (george-shuklin)]

I've hit that issue too.

sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create table 'neutron.embrane_pool_port' (errno: 150)") '\nCREATE TABLE embrane_pool_port (\n\tpool_id VARCHAR(36) NOT NULL, \n\tport_id VARCHAR(36) NOT NULL, \n\tPRIMARY KEY (pool_id), \n\tCONSTRAINT embrane_pool_port_ibfk_1 FOREIGN KEY(pool_id) REFERENCES pools (id), \n\tCONSTRAINT embrane_pool_port_ibfk_2 FOREIGN KEY(port_id) REFERENCES ports (id)\n)\n\n' ()

Disabling loadbalancing out of service_plugins helps.

[Oscar Martin (oscar-martin)]

Hello, I've same problem with neutron migration, Any idea here?

neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade icehouse

..........................
..........................
INFO [alembic.migration] Context impl MySQLImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade 19180cf98af6 -> 33dd0a9fa487, embrane_lbaas_driver
Traceback (most recent call last):
  File "/usr/bin/neutron-db-manage", line 10, in <module>
    sys.exit(main())
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 169, in main
    CONF.command.func(config, CONF.command.name)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 82, in do_upgrade_downgrade
    do_alembic_command(config, cmd, revision, sql=CONF.command.sql)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 60, in do_alembic_command
    getattr(alembic_command, cmd)(config, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/alembic/command.py", line 124, in upgrade
    script.run_env()
  File "/usr/lib/python2.7/dist-packages/alembic/script.py", line 199, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/lib/python2.7/dist-packages/alembic/util.py", line 199, in load_python_file
    module = load_module(module_id, path)
  File "/usr/lib/python2.7/dist-packages/alembic/compat.py", line 55, in load_module
    mod = imp.load_source(module_id, path, fp)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 103, in <module>
    run_migrations_online()
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 87, in run_migrations_online
    options=build_options())
  File "<string>", line 7, in run_migrations
  File "/usr/lib/python2.7/dist-packages/alembic/environment.py", line 652, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.7/dist-packages/alembic/migration.py", line 225, in run_migrations
    change(**kw)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/versions/33dd0a9fa487_embrane_lbaas_driver.py", line 54, in upgrade
    sa.PrimaryKeyConstraint(u'pool_id'))
  File "<string>", line 7, in create_table
  File "/usr/lib/python2.7/dist-packages/alembic/operations.py", line 647, in create_table
    self._table(name, *columns, **kw)
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 149, in create_table
    self._exec(schema.CreateTable(table))
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr...

[Ann Taraday (akamyshnikova)]

Oscar Martin, George Shuklin

Do you still have this error? If it is so, it is really strange as now it is checked if table 'pools' exists before creating table ''embrane_pool_port'' https://github.com/openstack/neutron/blob/master/neutron/db/migration/alembic_migrations/versions/33dd0a9fa487_embrane_lbaas_driver.py#L35

[Armando Migliaccio (armando-migliaccio)]

This bug is > 172 days without activity. We are unsetting assignee and milestone and setting status to Incomplete in order to allow its expiry in 60 days.

If the bug is still valid, then update the bug status.

[Launchpad Janitor (janitor)]

[Expired for neutron because there has been no activity for 60 days.]