You can't specify target table 'fixed_ips' for update

[Dan Prince]

Using the latest nova Essex code with the FlatDHCPNetwork manager w/ MySQL. I'm seeing the following exceptions in nova-network.log after booting an instance:

2011-12-22 19:58:33,227 ERROR nova.manager [f8b2fd9e-ce89-430a-a311-70ed3737c534 None None] Error during FlatDHCPManager._disassociate_stale_fixed_ips: (OperationalError) (1093, "You can't specify target table 'fixed_ips' for update in FROM clause") 'UPDATE fixed_ips SET updated_at=%s, instance_id=%s, leased=%s WHERE fixed_ips.id IN (SELECT fixed_ips.id \nFROM fixed_ips INNER JOIN instances ON fixed_ips.instance_id = instances.id AND fixed_ips.deleted = %s INNER JOIN networks ON networks.id = fixed_ips.network_id \nWHERE fixed_ips.updated_at < %s AND fixed_ips.instance_id IS NOT NULL AND fixed_ips.allocated = %s AND (instances.host = %s AND networks.multi_host = %s OR networks.host = %s))' (datetime.datetime(2011, 12, 22, 19, 58, 33, 218988), None, 0, 0, datetime.datetime(2011, 12, 22, 19, 48, 33, 216268), 0, 'nova1', 1, 'nova1')
(nova.manager): TRACE: Traceback (most recent call last):
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/nova/manager.py", line 155, in periodic_tasks
(nova.manager): TRACE: task(self, context)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/nova/network/manager.py", line 519, in _disassociate_stale_fixed_ips
(nova.manager): TRACE: time)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/api.py", line 391, in fixed_ip_disassociate_all_by_timeout
(nova.manager): TRACE: return IMPL.fixed_ip_disassociate_all_by_timeout(context, host, time)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/api.py", line 96, in wrapper
(nova.manager): TRACE: return f(*args, **kwargs)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/api.py", line 800, in fixed_ip_disassociate_all_by_timeout
(nova.manager): TRACE: synchronize_session='fetch')
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2313, in update
(nova.manager): TRACE: result = session.execute(update_stmt, params=self._params)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 724, in execute
(nova.manager): TRACE: clause, params or {})
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1191, in execute
(nova.manager): TRACE: params)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1271, in _execute_clauseelement
(nova.manager): TRACE: return self.__execute_context(context)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1302, in __execute_context
(nova.manager): TRACE: context.parameters[0], context=context)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1401, in _cursor_execute
(nova.manager): TRACE: context)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1394, in _cursor_execute
(nova.manager): TRACE: context)
(nova.manager): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 299, in do_execute
(nova.manager): TRACE: cursor.execute(statement, parameters)
(nova.manager): TRACE: File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 174, in execute
(nova.manager): TRACE: self.errorhandler(self, exc, value)
(nova.manager): TRACE: File "/usr/lib/pymodules/python2.7/MySQLdb/connections.py", line 36, in defaulterrorhandler
(nova.manager): TRACE: raise errorclass, errorvalue
(nova.manager): TRACE: OperationalError: (OperationalError) (1093, "You can't specify target table 'fixed_ips' for update in FROM clause") 'UPDATE fixed_ips SET updated_at=%s, instance_id=%s, leased=%s WHERE fixed_ips.id IN (SELECT fixed_ips.id \nFROM fixed_ips INNER JOIN instances ON fixed_ips.instance_id = instances.id AND fixed_ips.deleted = %s INNER JOIN networks ON networks.id = fixed_ips.network_id \nWHERE fixed_ips.updated_at < %s AND fixed_ips.instance_id IS NOT NULL AND fixed_ips.allocated = %s AND (instances.host = %s AND networks.multi_host = %s OR networks.host = %s))' (datetime.datetime(2011, 12, 22, 19, 58, 33, 218988), None, 0, 0, datetime.datetime(2011, 12, 22, 19, 48, 33, 216268), 0, 'nova1', 1, 'nova1')

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/2576

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/2576
Committed: http://github.com/openstack/nova/commit/5cf93a39dc59f1040a5118bdc74a00cb3eb3163a
Submitter: Jenkins
Branch: master

commit 5cf93a39dc59f1040a5118bdc74a00cb3eb3163a
Author: Dan Prince <email address hidden>
Date: Thu Dec 22 19:54:03 2011 -0500

    Fixes LP bug #907898.

    Update DB api so that fixed_ip_disassociate_all_by_timeout uses two
    separate commands (a query then an update). Fixes LP bug #907898.

    Change-Id: I60d7e7ba6bb3d3542ccf5b595d0c0de944e499a3