Hi Konstantin, > I'm trying to figure out how this can possibly happen: > >> SELECT id, submission_id FROM patchwork_comment WHERE > submission_id=9896319; > +----------+---------------+ > | id | submission_id | > +----------+---------------+ > | 20810589 | 9896319 | > +----------+---------------+ > 1 row in set (0.00 sec) > > MariaDB [KORG_PATCHWORK]> SELECT * FROM patchwork_submission WHERE > id=9896319; > Empty set (0.00 sec)
Right, well this is definitely the most terrifying start to any email I have received since starting as one of the patchwork maintainers! > There don't appear to be any foreign key constraints on the > patchwork_comment table -- would that be the side-effect of migration? I'm pretty sure that's right. I tested something similar to what you did on a local 2.1 setup: 1) find a comment mysql> select id, submission_id FROM patchwork_comment WHERE submission_id = 3; +----+---------------+ | id | submission_id | +----+---------------+ | 2 | 3 | +----+---------------+ 1 row in set (0.00 sec) 2) verify it has a patchwork_patch entry mysql> select submission_ptr_id, patch_project_id from patchwork_patch where submission_ptr_id = 3; +-------------------+------------------+ | submission_ptr_id | patch_project_id | +-------------------+------------------+ | 3 | 1 | +-------------------+------------------+ 1 row in set (0.00 sec) 3) delete the associate patches, clearing out foreign keys as required mysql> delete from patchwork_patch where patch_project_id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`patchwork`.`patchwork_event`, CONSTRAINT `patchwork_event_patch_id_15d4004e_fk_patchwork` FOREIGN KEY (`patch_id`) REFERENCES `patchwork_patch` (`submission_ptr_id`)) mysql> delete patchwork_event from patchwork_event inner join patchwork_patch on patchwork_event.patch_id = patchwork_patch.submission_ptr_id where patchwork_patch.patch_project_id = 1; Query OK, 64087 rows affected (14.09 sec) mysql> delete from patchwork_patch where patch_project_id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`patchwork`.`patchwork_seriespatch`, CONSTRAINT `patchwork_seriespatc_patch_id_33c4d56e_fk_patchwork` FOREIGN KEY (`patch_id`) REFERENCES `patchwork_patch` (`submission_ptr_id`)) mysql> delete patchwork_seriespatch from patchwork_seriespatch inner join patchwork_patch on patchwork_seriespatch.patch_id = patchwork_patch.submission_ptr_id where patchwork_patch.patch_project_id = 1; Query OK, 33392 rows affected (1.24 sec) mysql> delete from patchwork_patch where patch_project_id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`patchwork`.`patchwork_patchtag`, CONSTRAINT `patchwork_patchtag_patch_id_5fad6ac3_fk_patchwork` FOREIGN KEY (`patch_id`) REFERENCES `patchwork_patch` (`submission_ptr_id`)) mysql> delete patchwork_patchtag from patchwork_patchtag inner join patchwork_patch on patchwork_patchtag.patch_id = patchwork_patch.submission_ptr_id where patchwork_patch.patch_project_id = 1; Query OK, 11701 rows affected (0.23 sec) mysql> delete from patchwork_patch where patch_project_id = 1; Query OK, 33446 rows affected (2.98 sec) 4) Verify that the comment is still there ... mysql> select id, submission_id FROM patchwork_comment WHERE submission_id = 3; +----+---------------+ | id | submission_id | +----+---------------+ | 2 | 3 | +----+---------------+ 1 row in set (0.00 sec) 5) But the patch is not... mysql> select submission_ptr_id, patch_project_id from patchwork_patch where submission_ptr_id = 3; Empty set (0.00 sec) Even worse, we don't even clear up submissions in PW 2.1 when you delete the patches! mysql> select id, project_id from patchwork_submission where id =3 ; +----+------------+ | id | project_id | +----+------------+ | 3 | 1 | +----+------------+ 1 row in set (0.00 sec) (This wouldn't be the case with deletion in PW1 as there was no separate submission table.) So - unless I'm mistaken or something else is also awry - it doesn't look like you've experienced data loss, just excess data not cleaned up. I will prepare a patch that adds the foreign keys. Thanks for the report! Regards, Daniel _______________________________________________ Patchwork mailing list [email protected] https://lists.ozlabs.org/listinfo/patchwork
