Assume that 'image' table has 1 record and 120 image tags are added to a image. When you purge the db passing default parameters, it will give ‘IntegrityError’ error.
Steps to reproduce:
1. glance-manage db purge --age_in days 30 --max_rows 100
2. As per above command, max 100 records get purge from image_tags table
3. It fails with the below error when it tries to purge record from image table having reference in image_tags table because 20 records of image_tags still exists.
DBError detected when purging from images: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_tags`, CONSTRAINT `image_tags_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 ORDER BY images.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2017, 6, 4, 7, 25, 50, 16864), u'param_1': 100}]
Hi all,
Instead of applying limit of max_rows to each and every table separately, we can apply on all the tables (similar to nova archive deleted rows logic).
For example, Images table has 2 records and image_tags table has 15 records, then if we pass max_rows as 10 then it will purge 10 records from image_tags table on first attempt of purge command and then on second attempt it will purge the 5 records from image_tags and 2 records from images table. Also records will be purged from child tables first before purging the records from parent table which will help to avoid ItegrityFailure issue.