From 2749fd41f066dd51116c2a18198f79aa3c640156 Mon Sep 17 00:00:00 2001 From: Maru Newby Date: Fri, 10 Apr 2015 16:01:52 +0000 Subject: [PATCH] Fix routerid constraint migration The migration to add a fk constraint to the routerl3agentbindings table could fail if orphaned records existed. This change ensures that binding records are properly sanitized before constraint addition is attempted. Change-Id: Iace190916c9c0b9be75ddd43c4ca86480f8e017f Closes-Bug: #1442683 --- .../31d7f831a591_add_constraint_for_routerid.py | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) diff --git a/neutron/db/migration/alembic_migrations/versions/31d7f831a591_add_constraint_for_routerid.py b/neutron/db/migration/alembic_migrations/versions/31d7f831a591_add_constraint_for_routerid.py index c09a7aaf8..c9a233b66 100644 --- a/neutron/db/migration/alembic_migrations/versions/31d7f831a591_add_constraint_for_routerid.py +++ b/neutron/db/migration/alembic_migrations/versions/31d7f831a591_add_constraint_for_routerid.py @@ -30,6 +30,7 @@ from alembic import op from neutron.db import migration TABLE_NAME = 'routerl3agentbindings' +AGENTS_TABLE_NAME = 'agents' PK_NAME = 'pk_routerl3agentbindings' @@ -39,19 +40,24 @@ def upgrade(): # and all the duplicate records which violate the PK # constraint need to be removed. context = op.get_context() + query_args = {'table': TABLE_NAME, 'agents_table': AGENTS_TABLE_NAME} if context.bind.dialect.name in ('postgresql', 'ibm_db_sa'): op.execute('DELETE FROM %(table)s WHERE id in (' 'SELECT %(table)s.id FROM %(table)s LEFT OUTER JOIN ' '(SELECT MIN(id) as id, router_id, l3_agent_id ' ' FROM %(table)s GROUP BY router_id, l3_agent_id) AS temp ' 'ON %(table)s.id = temp.id WHERE temp.id is NULL);' - % {'table': TABLE_NAME}) + % query_args) else: op.execute('DELETE %(table)s FROM %(table)s LEFT OUTER JOIN ' '(SELECT MIN(id) as id, router_id, l3_agent_id ' ' FROM %(table)s GROUP BY router_id, l3_agent_id) AS temp ' 'ON %(table)s.id = temp.id WHERE temp.id is NULL;' - % {'table': TABLE_NAME}) + % query_args) + # Remove orphaned records - bindings that reference non-existent agents + op.execute('DELETE FROM %(table)s ' + 'WHERE l3_agent_id NOT IN (select id from %(agents_table)s);' + % query_args) op.drop_column(TABLE_NAME, 'id') -- 2.45.2