yahoo-eng-team team mailing list archive

[Liang Fang <1803643@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

"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   |         |
+--------------------------------------+---------------------------------------+--------+---------+

** Affects: glance
     Importance: Undecided
         Status: New

-- 
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:
  New

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