From efeb5243ffd5e588748d8786ac82a04e302f0612 Mon Sep 17 00:00:00 2001 From: Vishvananda Ishaya Date: Sun, 12 Sep 2010 17:24:06 -0700 Subject: workaround for mysql select in update --- nova/db/sqlalchemy/api.py | 19 +++++++++++++------ 1 file changed, 13 insertions(+), 6 deletions(-) diff --git a/nova/db/sqlalchemy/api.py b/nova/db/sqlalchemy/api.py index fa2981899..aa8670253 100644 --- a/nova/db/sqlalchemy/api.py +++ b/nova/db/sqlalchemy/api.py @@ -246,12 +246,19 @@ def fixed_ip_disassociate(_context, address): def fixed_ip_disassociate_all_by_timeout(_context, host, time): session = get_session() - result = session.execute('update fixed_ips set instance_id = NULL ' - 'WHERE id IN (SELECT fixed_ips.id FROM fixed_ips ' - 'INNER JOIN networks ' - 'ON fixed_ips.network_id = ' - 'networks.id ' - 'WHERE host = :host) ' + # NOTE(vish): The annoying nested select here is because SQLite doesn't + # support JOINs in UPDATEs and Mysql doesn't support SELECT + # from the same table you are updating without using a temp + # table. It would be great if we can coax sqlalchemy into + # generating this update for us without having to update + # each fixed_ip individually. + result = session.execute('UPDATE fixed_ips SET instance_id = NULL ' + 'WHERE id IN (SELECT x.id FROM ' + '(SELECT fixed_ips.id FROM fixed_ips ' + 'INNER JOIN networks ' + 'ON fixed_ips.network_id = ' + 'networks.id ' + 'WHERE host = :host) as x) ' 'AND updated_at < :time ' 'AND instance_id IS NOT NULL', {'host': host, -- cgit