yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1543937] [NEW] 'nova-manage db archive_deleted_rows' fails for very large number

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Ravi Shekhar Jethani <ravishekar.jethani@xxxxxxxxxxx>
Date: Wed, 10 Feb 2016 07:03:52 -0000
Reply-to: Bug 1543937 <1543937@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

The command:
$ nova-manage db archive_deleted_rows <NUMBER> --verbose
fails for very large NUMBER value on nova master

Nova version:
openstack@openstack-136:/opt/stack/nova$ git log -1
commit 29641bd9778b51ac5794dfed9d4b881c5d47dc50
Merge: 21e79d5 9fbe683
Author: Jenkins <jenkins@xxxxxxxxxxxxxxxxxxxx>
Date:   Wed Feb 10 06:03:00 2016 +0000

    Merge "Top 100 slow tests: api.openstack.compute.test_api"

Example:

openstack@openstack-136:~$ nova-manage db archive_deleted_rows 2147483547647747477747474775366545456499999 --verbose
2016-02-09 22:17:10.713 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     context)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     cursor.execute(statement, parameters)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result = self._query(query)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     conn.query(q)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result.read()
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     first_packet = self.connection._read_packet()
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     packet.check_error()
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     err.raise_mysql_exception(self._data)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     _check_mysql_exception(errinfo)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     raise errorclass(errno, errorvalue)
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4")
2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters
Command failed, please check log for more info
2016-02-09 22:17:10.716 CRITICAL nova [-] DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]

2016-02-09 22:17:10.716 TRACE nova Traceback (most recent call last):
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/bin/nova-manage", line 10, in <module>
2016-02-09 22:17:10.716 TRACE nova     sys.exit(main())
2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 1448, in main
2016-02-09 22:17:10.716 TRACE nova     ret = fn(*fn_args, **fn_kwargs)
2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 951, in archive_deleted_rows
2016-02-09 22:17:10.716 TRACE nova     table_to_rows_archived = db.archive_deleted_rows(max_rows)
2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/api.py", line 1945, in archive_deleted_rows
2016-02-09 22:17:10.716 TRACE nova     return IMPL.archive_deleted_rows(max_rows=max_rows)
2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6129, in archive_deleted_rows
2016-02-09 22:17:10.716 TRACE nova     tablename, max_rows=max_rows - total_rows_archived)
2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6084, in _archive_deleted_rows_for_table
2016-02-09 22:17:10.716 TRACE nova     conn.execute(insert)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
2016-02-09 22:17:10.716 TRACE nova     return meth(self, multiparams, params)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
2016-02-09 22:17:10.716 TRACE nova     return connection._execute_clauseelement(self, multiparams, params)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
2016-02-09 22:17:10.716 TRACE nova     compiled_sql, distilled_params
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2016-02-09 22:17:10.716 TRACE nova     context)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
2016-02-09 22:17:10.716 TRACE nova     util.raise_from_cause(newraise, exc_info)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
2016-02-09 22:17:10.716 TRACE nova     reraise(type(exception), exception, tb=exc_tb)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-02-09 22:17:10.716 TRACE nova     context)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-02-09 22:17:10.716 TRACE nova     cursor.execute(statement, parameters)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
2016-02-09 22:17:10.716 TRACE nova     result = self._query(query)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
2016-02-09 22:17:10.716 TRACE nova     conn.query(q)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
2016-02-09 22:17:10.716 TRACE nova     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
2016-02-09 22:17:10.716 TRACE nova     result.read()
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
2016-02-09 22:17:10.716 TRACE nova     first_packet = self.connection._read_packet()
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
2016-02-09 22:17:10.716 TRACE nova     packet.check_error()
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
2016-02-09 22:17:10.716 TRACE nova     err.raise_mysql_exception(self._data)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
2016-02-09 22:17:10.716 TRACE nova     _check_mysql_exception(errinfo)
2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
2016-02-09 22:17:10.716 TRACE nova     raise errorclass(errno, errorvalue)
2016-02-09 22:17:10.716 TRACE nova DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
2016-02-09 22:17:10.716 TRACE nova

Instead of a stacktrace user should get a user friendly message like:
"max rows must be <= 2147483647"

** Affects: nova
     Importance: Undecided
     Assignee: Ravi Shekhar Jethani (ravishekar-jethani)
         Status: New

** Changed in: nova
     Assignee: (unassigned) => Ravi Shekhar Jethani (ravishekar-jethani)

** Description changed:

- 
  The command:
- $ nova-manage db archive_deleted_rows <NUMBER> --verbose 
+ $ nova-manage db archive_deleted_rows <NUMBER> --verbose
  fails for very large NUMBER value on nova master
  
  Nova version:
  openstack@openstack-136:/opt/stack/nova$ git log -1
- commit 34cf19c8a4dababd1e723c0364a80823c0ac2637
- Merge: 6248dd0 83cd67c
+ commit 29641bd9778b51ac5794dfed9d4b881c5d47dc50
+ Merge: 21e79d5 9fbe683
  Author: Jenkins <jenkins@xxxxxxxxxxxxxxxxxxxx>
- Date:   Wed Jan 13 02:57:09 2016 +0000
+ Date:   Wed Feb 10 06:03:00 2016 +0000
  
-     Merge "Add 'hw:cpu_thread_policy=require' scheduling"
- 
+     Merge "Top 100 slow tests: api.openstack.compute.test_api"
  
  Example:
  
  openstack@openstack-136:~$ nova-manage db archive_deleted_rows 2147483547647747477747474775366545456499999 --verbose
  2016-02-09 22:17:10.713 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     context)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     cursor.execute(statement, parameters)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result = self._query(query)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     conn.query(q)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result.read()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     first_packet = self.connection._read_packet()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     packet.check_error()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     err.raise_mysql_exception(self._data)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     _check_mysql_exception(errinfo)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     raise errorclass(errno, errorvalue)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4")
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters
  Command failed, please check log for more info
  2016-02-09 22:17:10.716 CRITICAL nova [-] DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
  
  2016-02-09 22:17:10.716 TRACE nova Traceback (most recent call last):
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/bin/nova-manage", line 10, in <module>
  2016-02-09 22:17:10.716 TRACE nova     sys.exit(main())
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 1448, in main
  2016-02-09 22:17:10.716 TRACE nova     ret = fn(*fn_args, **fn_kwargs)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 951, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     table_to_rows_archived = db.archive_deleted_rows(max_rows)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/api.py", line 1945, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     return IMPL.archive_deleted_rows(max_rows=max_rows)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6129, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     tablename, max_rows=max_rows - total_rows_archived)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6084, in _archive_deleted_rows_for_table
  2016-02-09 22:17:10.716 TRACE nova     conn.execute(insert)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
  2016-02-09 22:17:10.716 TRACE nova     return meth(self, multiparams, params)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  2016-02-09 22:17:10.716 TRACE nova     return connection._execute_clauseelement(self, multiparams, params)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  2016-02-09 22:17:10.716 TRACE nova     compiled_sql, distilled_params
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2016-02-09 22:17:10.716 TRACE nova     context)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2016-02-09 22:17:10.716 TRACE nova     util.raise_from_cause(newraise, exc_info)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  2016-02-09 22:17:10.716 TRACE nova     reraise(type(exception), exception, tb=exc_tb)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-02-09 22:17:10.716 TRACE nova     context)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-02-09 22:17:10.716 TRACE nova     cursor.execute(statement, parameters)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
  2016-02-09 22:17:10.716 TRACE nova     result = self._query(query)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
  2016-02-09 22:17:10.716 TRACE nova     conn.query(q)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
  2016-02-09 22:17:10.716 TRACE nova     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
  2016-02-09 22:17:10.716 TRACE nova     result.read()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
  2016-02-09 22:17:10.716 TRACE nova     first_packet = self.connection._read_packet()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
  2016-02-09 22:17:10.716 TRACE nova     packet.check_error()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
  2016-02-09 22:17:10.716 TRACE nova     err.raise_mysql_exception(self._data)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-02-09 22:17:10.716 TRACE nova     _check_mysql_exception(errinfo)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-02-09 22:17:10.716 TRACE nova     raise errorclass(errno, errorvalue)
  2016-02-09 22:17:10.716 TRACE nova DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
  2016-02-09 22:17:10.716 TRACE nova
  
- 
- Instead of a stacktrace user should get a user friendly message like: "max rows must be <= 2147483647"
+ Instead of a stacktrace user should get a user friendly message like:
+ "max rows must be <= 2147483647"

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1543937

Title:
  'nova-manage db archive_deleted_rows' fails for very large number

Status in OpenStack Compute (nova):
  New

Bug description:
  The command:
  $ nova-manage db archive_deleted_rows <NUMBER> --verbose
  fails for very large NUMBER value on nova master

  Nova version:
  openstack@openstack-136:/opt/stack/nova$ git log -1
  commit 29641bd9778b51ac5794dfed9d4b881c5d47dc50
  Merge: 21e79d5 9fbe683
  Author: Jenkins <jenkins@xxxxxxxxxxxxxxxxxxxx>
  Date:   Wed Feb 10 06:03:00 2016 +0000

      Merge "Top 100 slow tests: api.openstack.compute.test_api"

  Example:

  openstack@openstack-136:~$ nova-manage db archive_deleted_rows 2147483547647747477747474775366545456499999 --verbose
  2016-02-09 22:17:10.713 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     context)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     cursor.execute(statement, parameters)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result = self._query(query)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     conn.query(q)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     result.read()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     first_packet = self.connection._read_packet()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     packet.check_error()
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     err.raise_mysql_exception(self._data)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     _check_mysql_exception(errinfo)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters     raise errorclass(errno, errorvalue)
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4")
  2016-02-09 22:17:10.713 TRACE oslo_db.sqlalchemy.exc_filters
  Command failed, please check log for more info
  2016-02-09 22:17:10.716 CRITICAL nova [-] DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]

  2016-02-09 22:17:10.716 TRACE nova Traceback (most recent call last):
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/bin/nova-manage", line 10, in <module>
  2016-02-09 22:17:10.716 TRACE nova     sys.exit(main())
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 1448, in main
  2016-02-09 22:17:10.716 TRACE nova     ret = fn(*fn_args, **fn_kwargs)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/cmd/manage.py", line 951, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     table_to_rows_archived = db.archive_deleted_rows(max_rows)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/api.py", line 1945, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     return IMPL.archive_deleted_rows(max_rows=max_rows)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6129, in archive_deleted_rows
  2016-02-09 22:17:10.716 TRACE nova     tablename, max_rows=max_rows - total_rows_archived)
  2016-02-09 22:17:10.716 TRACE nova   File "/opt/stack/nova/nova/db/sqlalchemy/api.py", line 6084, in _archive_deleted_rows_for_table
  2016-02-09 22:17:10.716 TRACE nova     conn.execute(insert)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
  2016-02-09 22:17:10.716 TRACE nova     return meth(self, multiparams, params)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  2016-02-09 22:17:10.716 TRACE nova     return connection._execute_clauseelement(self, multiparams, params)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  2016-02-09 22:17:10.716 TRACE nova     compiled_sql, distilled_params
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2016-02-09 22:17:10.716 TRACE nova     context)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2016-02-09 22:17:10.716 TRACE nova     util.raise_from_cause(newraise, exc_info)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  2016-02-09 22:17:10.716 TRACE nova     reraise(type(exception), exception, tb=exc_tb)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-02-09 22:17:10.716 TRACE nova     context)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-02-09 22:17:10.716 TRACE nova     cursor.execute(statement, parameters)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
  2016-02-09 22:17:10.716 TRACE nova     result = self._query(query)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
  2016-02-09 22:17:10.716 TRACE nova     conn.query(q)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 781, in query
  2016-02-09 22:17:10.716 TRACE nova     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 942, in _read_query_result
  2016-02-09 22:17:10.716 TRACE nova     result.read()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1138, in read
  2016-02-09 22:17:10.716 TRACE nova     first_packet = self.connection._read_packet()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 906, in _read_packet
  2016-02-09 22:17:10.716 TRACE nova     packet.check_error()
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 367, in check_error
  2016-02-09 22:17:10.716 TRACE nova     err.raise_mysql_exception(self._data)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-02-09 22:17:10.716 TRACE nova     _check_mysql_exception(errinfo)
  2016-02-09 22:17:10.716 TRACE nova   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-02-09 22:17:10.716 TRACE nova     raise errorclass(errno, errorvalue)
  2016-02-09 22:17:10.716 TRACE nova DBError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483547647747477747474775366545456499999' at line 4") [SQL: u'INSERT INTO shadow_instance_actions_events (created_at, updated_at, deleted_at, deleted, id, event, action_id, start_time, finish_time, result, traceback, host, details) SELECT instance_actions_events.created_at, instance_actions_events.updated_at, instance_actions_events.deleted_at, instance_actions_events.deleted, instance_actions_events.id, instance_actions_events.event, instance_actions_events.action_id, instance_actions_events.start_time, instance_actions_events.finish_time, instance_actions_events.result, instance_actions_events.traceback, instance_actions_events.host, instance_actions_events.details \nFROM instance_actions_events \nWHERE instance_actions_events.deleted != %s ORDER BY instance_actions_events.id \n LIMIT %s'] [parameters: (0, 2147483547647747477747474775366545456499999L)]
  2016-02-09 22:17:10.716 TRACE nova

  Instead of a stacktrace user should get a user friendly message like:
  "max rows must be <= 2147483647"

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1543937/+subscriptions
Follow ups

[Bug 1543937] Re: db purge records fails for very large number
From: OpenStack Infra, 2016-07-30
[Bug 1543937] Re: db purge records fails for very large number
From: OpenStack Infra, 2016-07-26
[Bug 1543937] Re: db purge records fails for very large number
From: Bhagyashri Shewale, 2016-05-17
[Bug 1543937] Re: db purge records fails for very large number
From: OpenStack Infra, 2016-02-27
[Bug 1543937] Re: 'nova-manage db archive_deleted_rows' fails for very large number
From: Abhishek Kekane, 2016-02-11