Reviewed: https://review.openstack.org/617889 Committed: https://git.openstack.org/cgit/openstack/glance/commit/?id=72159a4a7b267bfe376e84ea754a42b372206325 Submitter: Zuul Branch: master
commit 72159a4a7b267bfe376e84ea754a42b372206325 Author: Liang Fang <[email protected]> Date: Wed Nov 14 14:18:54 2018 +0800 Fix for FK constraint violation First force purging of records that are not soft deleted but are referencing soft deleted tasks/images records (e.g. task_info records). Then purge all soft deleted records in glance tables in the right order to avoid FK constraint violation. Closes-Bug: #1803643 Change-Id: I1c471adce002545f8965a57ef78a57e1e3031ef0 Co-authored-by: Tee Ngo <[email protected]> Signed-off-by: Liang Fang <[email protected]> ** Changed in: glance Status: In Progress => Fix Released -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to Glance. https://bugs.launchpad.net/bugs/1803643 Title: task_info FK error when running "glance-manage db purge" Status in Glance: Fix Released Bug description: "glance-manage db purge" failed when there're tasks in db, with state "deleted=1" and "deleted_at" one month ago. Error logs --------------------------------------------------------------- DBError detected when purging from tasks: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`task_info`, CONSTRAINT `task_info_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`))') [SQL: u'DELETE FROM tasks WHERE tasks.id in (SELECT T1.id FROM (SELECT tasks.id \nFROM tasks \nWHERE tasks.deleted_at < %(deleted_at_1)s ORDER BY tasks.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2018, 11, 14, 2, 28, 7, 645622), u'param_1': 100}] (Background on this error at: http://sqlalche.me/e/gkpj): DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`task_info`, CONSTRAINT `task_info_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`))') [SQL: u'DELETE FROM tasks WHERE tasks.id in (SELECT T1.id FROM (SELECT tasks.id \nFROM tasks \nWHERE tasks.deleted_at < %(deleted_at_1)s ORDER BY tasks.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2018, 11, 14, 2, 28, 7, 645622), u'param_1': 100}] (Background on this error at: http://sqlalche.me/e/gkpj) Purge command failed, check glance-manage logs for more details. Steps to reproduce --------------------------------------------------------------- 1. create a task glance task-create --type "import" --input '{"import_from": "/opt/stack/111.img"}' glance task-create --type "import" --input '{"import_from": "/opt/stack/222.img"}' 2. update the db table "tasks", set deleted=1 and deleted_at a day one month ago e.g. update tasks set deleted=1, deleted_at='2018-10-10 03:18:50' where id='dc76da48-cace-47d4-bcfd-0b62254e52ed'; 3. run "glance-manage db purge --age_in_days 2" Database like: mysql> select * from tasks; +--------------------------------------+--------+---------+----------------------------------+------------+---------------------+---------------------+---------------------+---------+ | id | type | status | owner | expires_at | created_at | updated_at | deleted_at | deleted | +--------------------------------------+--------+---------+----------------------------------+------------+---------------------+---------------------+---------------------+---------+ | dc76da48-cace-47d4-bcfd-0b62254e52ed | import | pending | 60a12b1788ad44468afd983f89a5f8dc | NULL | 2018-11-15 03:18:33 | 2018-11-15 03:18:33 | 2018-10-10 03:18:50 | 1 | | fbd7e46a-0f33-4c98-be87-0ff7112561e1 | import | pending | 60a12b1788ad44468afd983f89a5f8dc | NULL | 2018-11-16 02:18:12 | 2018-11-16 02:18:12 | NULL | 0 | +--------------------------------------+--------+---------+----------------------------------+------------+---------------------+---------------------+---------------------+---------+ 2 rows in set (0.00 sec) mysql> select * from task_info; +--------------------------------------+---------------------------------------+--------+---------+ | task_id | input | result | message | +--------------------------------------+---------------------------------------+--------+---------+ | dc76da48-cace-47d4-bcfd-0b62254e52ed | {"import_from": "/opt/stack/111.img"} | NULL | | | fbd7e46a-0f33-4c98-be87-0ff7112561e1 | {"import_from": "/opt/stack/222.img"} | NULL | | +--------------------------------------+---------------------------------------+--------+---------+ To manage notifications about this bug go to: https://bugs.launchpad.net/glance/+bug/1803643/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : [email protected] Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
