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 -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : yahoo-eng-team@lists.launchpad.net Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
