subnet_id can not be added as primary key due to foreign key constraint

Bug #1791121 reported by Sam Betts on 2018-09-06
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
networking-cisco
High
Sam Betts

Bug Description

  File "/usr/lib/python2.7/site-packages/networking_cisco/db/migration/alembic_migrations/versions/mitaka/expand/73c84db9f299_update_ha_group_primary_key.py", line 69, in upgrade
    columns=['ha_port_id', 'subnet_id'])
  File "<string>", line 8, in create_primary_key
  File "<string>", line 3, in create_primary_key
  File "/usr/lib/python2.7/site-packages/alembic/operations/ops.py", line 269, in create_primary_key
    return operations.invoke(op)
  File "/usr/lib/python2.7/site-packages/alembic/operations/base.py", line 319, in invoke
    return fn(self, operation)
  File "/usr/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 135, in create_constraint
    operation.to_constraint(operations.migration_context)
  File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 180, in add_constraint
    self._exec(schema.AddConstraint(const))
  File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1409, in _handle_dbapi_exception
    util.raise_from_cause(newraise, exc_info)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
oslo_db.exception.DBError: (pymysql.err.InternalError) (1832, u"Cannot change column 'subnet_id': used in a foreign key constraint 'cisco_router_ha_groups_ibfk_3'") [SQL: u'ALTER TABLE cisco_router_ha_groups ADD CONSTRAINT pk_cisco_router_ha_groups PRIMARY KEY (ha_port_id, subnet_id)'] (Background on this error at: http://sqlalche.me/e/2j85)

This is seen on OSP13 (queens) which uses:

sudo docker exec bb5d5986702d mysql --version
mysql Ver 15.1 Distrib 10.1.20-MariaDB, for Linux (x86_64) using EditLine wrapper

instead of mysql.

Sam Betts (sambetts) on 2018-09-06
Changed in networking-cisco:
assignee: nobody → Sam Betts (sambetts)
importance: Undecided → High
status: New → Triaged
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to networking-cisco (release-6)

Fix proposed to branch: release-6
Review: https://review.openstack.org/601967

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to networking-cisco (master)

Reviewed: https://review.openstack.org/590795
Committed: https://git.openstack.org/cgit/openstack/networking-cisco/commit/?id=cbaf892d40c2fe1027a111e5e4d7be9d4de045b4
Submitter: Zuul
Branch: master

commit cbaf892d40c2fe1027a111e5e4d7be9d4de045b4
Author: Murali Annamneni <email address hidden>
Date: Fri Aug 10 16:13:01 2018 +0100

    Fix foreign key constraint violation while creating primary key with subnet_id

    'subnet_id' is part of FK 'cisco_router_ha_groups_ibfk_3', so including
    it in primary key violates FK constraint.

    Change-Id: Ie5fc6168b3ab3cbd09958778824ae37535a99be9
    Closes-Bug: #1791121

Changed in networking-cisco:
status: Triaged → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to networking-cisco (release-6)

Reviewed: https://review.openstack.org/601967
Committed: https://git.openstack.org/cgit/openstack/networking-cisco/commit/?id=68a5b4b5af4f08a4ffddce0cdaf42d538a3c738f
Submitter: Zuul
Branch: release-6

commit 68a5b4b5af4f08a4ffddce0cdaf42d538a3c738f
Author: Murali Annamneni <email address hidden>
Date: Fri Aug 10 16:13:01 2018 +0100

    Fix foreign key constraint violation while creating primary key with subnet_id

    'subnet_id' is part of FK 'cisco_router_ha_groups_ibfk_3', so including
    it in primary key violates FK constraint.

    Change-Id: Ie5fc6168b3ab3cbd09958778824ae37535a99be9
    Closes-Bug: #1791121

tags: added: in-release-6
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers