nova-manage db archive_deleted_rows fails if max_rows is a large number

[ChangBo Guo(gcb)]

The nova-manage db archive_deleted_rows fails if max_rows is a large number (I tried 1 million but a smaller value may also cause issues) because it receives an exception from the sqlalchemy and db layer regarding the number of parameters on the sql statement.
Database has a limite of maximum total length of host and indicator variables in SQL statement. When I ran the archive, the table had 165822 rows in it and 18489 of those were not deleted. Therefore, 147333 rows had deleted=1. So get error from Database.

[ChangBo Guo(gcb) (glongwave)]

I got the log :
2013-08-07 09:40:27.691 58469 INFO sqlalchemy.engine.base.Engine [-] ROLLBACK
2013-08-07 09:40:27.767 CRITICAL nova [req-5214dafa-f8ae-442c-8579-50501b7d4828 None None] (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001 SQLCODE=-99999 'DELETE FROM instance_system_metadata WHERE instance_system_metadata.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?...

[OpenStack Infra (hudson-openstack)]

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

[ChangBo Guo(gcb) (glongwave)]

When I try to update 8000 items in my patch's unit test , got the error . So we really need divide the operations.
2013-08-21 08:10:32.673 | Traceback (most recent call last):
2013-08-21 08:10:32.674 | File "/home/jenkins/workspace/gate-nova-python26/nova/tests/db/test_db_api.py", line 5942, in test_archive_deleted_rows_large_rows
2013-08-21 08:10:32.674 | self.conn.execute(update_statement)
2013-08-21 08:10:32.674 | File "/home/jenkins/workspace/gate-nova-python26/.tox/py26/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
2013-08-21 08:10:32.675 | params)
2013-08-21 08:10:32.675 | File "/home/jenkins/workspace/gate-nova-python26/.tox/py26/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
2013-08-21 08:10:32.675 | compiled_sql, distilled_params
2013-08-21 08:10:32.676 | File "/home/jenkins/workspace/gate-nova-python26/.tox/py26/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
2013-08-21 08:10:32.676 | context)
2013-08-21 08:10:32.676 | File "/home/jenkins/workspace/gate-nova-python26/.tox/py26/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
2013-08-21 08:10:32.676 | context)
2013-08-21 08:10:32.678 | File "/home/jenkins/workspace/gate-nova-python26/.tox/py26/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 331, in do_execute
2013-08-21 08:10:32.678 | cursor.execute(statement, parameters)
2013-08-21 08:17:01.641 | OperationalError: (OperationalError) too many SQL variables u'UPDATE instance_id_mappings SET deleted=? WHERE instance_id_mappings.uuid IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?

[ChangBo Guo(gcb) (glongwave)]

My patch is blocked by a tempest bug https://bugs.launchpad.net/tempest/+bug/1217265

I test the patch in my env and passed unit test , I also extract code from tempest and run it successfully.

[ChangBo Guo(gcb) (glongwave)]

 I work around the mysql limit with nesting select SQL statement. so It can passed the tempest now

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/43056
Committed: http://github.com/openstack/nova/commit/b36826ef3ddeafcf5f16034bccd971de800f677a
Submitter: Jenkins
Branch: master

commit b36826ef3ddeafcf5f16034bccd971de800f677a
Author: Chang Bo Guo <email address hidden>
Date: Sat Aug 24 09:02:47 2013 -0700

    Add DeleteFromSelect to avoid database's limit

    nova-manage db archive_deleted_rows fails if max_rows is a large
    number. Database has a limit of maximum sql variables in one SQL
    statement. It is more efficient to insert(select) directly and then
    delete(same select) without ever returning the selected rows back to
    Python. This also can avoid database's limit.

    Closes-Bug: #1214720

    Change-Id: I29e3a5ce14c59dd2979e45e8d31fc3df04c70266