yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1554412] [NEW] purge command fails due to foreign key constraint

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Abhishek Kekane <abhishek.kekane@xxxxxxxxxxx>
Date: Tue, 08 Mar 2016 08:34:14 -0000
Reply-to: Bug 1554412 <1554412@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

In our production environment some how we have encountered a issue in
purge command. One of the example is, purge command fails if image is
deleted but members entry for that particular image is not deleted.

Steps to reproduce:

1. Apply patch https://review.openstack.org/#/c/278870/7 (otherwise you will get subquery related error)
2. Run purge command "glance-manage db purge 1 1"

Purge command trace:

2016-03-08 08:25:16.127 CRITICAL glance [req-7ce644e9-3c7d-
4cd4-8377-77f7e1a689a5 None None] DBReferenceError:
(pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent
row: a foreign key constraint fails (`glance`.`image_locations`,
CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES
`images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT
T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at <
%(deleted_at_1)s \n LIMIT %(param_1)s) as T1)'] [parameters:
{u'deleted_at_1': datetime.datetime(2016, 3, 7, 8, 25, 16, 92480),
u'param_1': 1}]

2016-03-08 08:25:16.127 TRACE glance Traceback (most recent call last):
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/bin/glance-manage", line 10, in <module>
2016-03-08 08:25:16.127 TRACE glance     sys.exit(main())
2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/cmd/manage.py", line 344, in main
2016-03-08 08:25:16.127 TRACE glance     return CONF.command.action_fn(*func_args, **func_kwargs)
2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/cmd/manage.py", line 165, in purge
2016-03-08 08:25:16.127 TRACE glance     db_api.purge_deleted_rows(ctx, age_in_days, max_rows)
2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/db/sqlalchemy/api.py", line 1327, in purge_deleted_rows
2016-03-08 08:25:16.127 TRACE glance     result = session.execute(delete_statement)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1034, in execute
2016-03-08 08:25:16.127 TRACE glance     bind, close_with_result=True).execute(clause, params or {})
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
2016-03-08 08:25:16.127 TRACE glance     return meth(self, multiparams, params)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
2016-03-08 08:25:16.127 TRACE glance     return connection._execute_clauseelement(self, multiparams, params)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
2016-03-08 08:25:16.127 TRACE glance     compiled_sql, distilled_params
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2016-03-08 08:25:16.127 TRACE glance     context)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
2016-03-08 08:25:16.127 TRACE glance     util.raise_from_cause(newraise, exc_info)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
2016-03-08 08:25:16.127 TRACE glance     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-03-08 08:25:16.127 TRACE glance     context)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-03-08 08:25:16.127 TRACE glance     cursor.execute(statement, parameters)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
2016-03-08 08:25:16.127 TRACE glance     result = self._query(query)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
2016-03-08 08:25:16.127 TRACE glance     conn.query(q)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 819, in query
2016-03-08 08:25:16.127 TRACE glance     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1001, in _read_query_result
2016-03-08 08:25:16.127 TRACE glance     result.read()
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1285, in read
2016-03-08 08:25:16.127 TRACE glance     first_packet = self.connection._read_packet()
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 965, in _read_packet
2016-03-08 08:25:16.127 TRACE glance     packet.check_error()
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 394, in check_error
2016-03-08 08:25:16.127 TRACE glance     err.raise_mysql_exception(self._data)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
2016-03-08 08:25:16.127 TRACE glance     _check_mysql_exception(errinfo)
2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
2016-03-08 08:25:16.127 TRACE glance     raise errorclass(errno, errorvalue)
2016-03-08 08:25:16.127 TRACE glance DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_locations`, CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 3, 7, 8, 25, 16, 92480), u'param_1': 1}]
2016-03-08 08:25:16.127 TRACE glance

** Affects: glance
     Importance: Undecided
     Assignee: Abhishek Kekane (abhishek-kekane)
         Status: New

** Changed in: glance
     Assignee: (unassigned) => Abhishek Kekane (abhishek-kekane)

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Glance.
https://bugs.launchpad.net/bugs/1554412

Title:
  purge command fails due to foreign key constraint

Status in Glance:
  New

Bug description:
  In our production environment some how we have encountered a issue in
  purge command. One of the example is, purge command fails if image is
  deleted but members entry for that particular image is not deleted.

  Steps to reproduce:

  1. Apply patch https://review.openstack.org/#/c/278870/7 (otherwise you will get subquery related error)
  2. Run purge command "glance-manage db purge 1 1"

  Purge command trace:

  2016-03-08 08:25:16.127 CRITICAL glance [req-7ce644e9-3c7d-
  4cd4-8377-77f7e1a689a5 None None] DBReferenceError:
  (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent
  row: a foreign key constraint fails (`glance`.`image_locations`,
  CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`)
  REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE
  images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images
  \nWHERE images.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s) as
  T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 3, 7, 8,
  25, 16, 92480), u'param_1': 1}]

  2016-03-08 08:25:16.127 TRACE glance Traceback (most recent call last):
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/bin/glance-manage", line 10, in <module>
  2016-03-08 08:25:16.127 TRACE glance     sys.exit(main())
  2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/cmd/manage.py", line 344, in main
  2016-03-08 08:25:16.127 TRACE glance     return CONF.command.action_fn(*func_args, **func_kwargs)
  2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/cmd/manage.py", line 165, in purge
  2016-03-08 08:25:16.127 TRACE glance     db_api.purge_deleted_rows(ctx, age_in_days, max_rows)
  2016-03-08 08:25:16.127 TRACE glance   File "/opt/stack/glance/glance/db/sqlalchemy/api.py", line 1327, in purge_deleted_rows
  2016-03-08 08:25:16.127 TRACE glance     result = session.execute(delete_statement)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1034, in execute
  2016-03-08 08:25:16.127 TRACE glance     bind, close_with_result=True).execute(clause, params or {})
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
  2016-03-08 08:25:16.127 TRACE glance     return meth(self, multiparams, params)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  2016-03-08 08:25:16.127 TRACE glance     return connection._execute_clauseelement(self, multiparams, params)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  2016-03-08 08:25:16.127 TRACE glance     compiled_sql, distilled_params
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2016-03-08 08:25:16.127 TRACE glance     context)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2016-03-08 08:25:16.127 TRACE glance     util.raise_from_cause(newraise, exc_info)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  2016-03-08 08:25:16.127 TRACE glance     reraise(type(exception), exception, tb=exc_tb, cause=cause)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2016-03-08 08:25:16.127 TRACE glance     context)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2016-03-08 08:25:16.127 TRACE glance     cursor.execute(statement, parameters)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 146, in execute
  2016-03-08 08:25:16.127 TRACE glance     result = self._query(query)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 296, in _query
  2016-03-08 08:25:16.127 TRACE glance     conn.query(q)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 819, in query
  2016-03-08 08:25:16.127 TRACE glance     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1001, in _read_query_result
  2016-03-08 08:25:16.127 TRACE glance     result.read()
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1285, in read
  2016-03-08 08:25:16.127 TRACE glance     first_packet = self.connection._read_packet()
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 965, in _read_packet
  2016-03-08 08:25:16.127 TRACE glance     packet.check_error()
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 394, in check_error
  2016-03-08 08:25:16.127 TRACE glance     err.raise_mysql_exception(self._data)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
  2016-03-08 08:25:16.127 TRACE glance     _check_mysql_exception(errinfo)
  2016-03-08 08:25:16.127 TRACE glance   File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 112, in _check_mysql_exception
  2016-03-08 08:25:16.127 TRACE glance     raise errorclass(errno, errorvalue)
  2016-03-08 08:25:16.127 TRACE glance DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_locations`, CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2016, 3, 7, 8, 25, 16, 92480), u'param_1': 1}]
  2016-03-08 08:25:16.127 TRACE glance

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

[Bug 1554412] Re: purge command fails due to foreign key constraint
From: OpenStack Infra, 2017-04-05