Database consistency issues

[Daniel Axtens]

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