database tables get out of sync

[Ben Allen]

I recently noticed that the ticket dependency information in my database is no longer consistent. This results in dependency graphs and queries that give incorrect results. I am running plugin version 3.0.5dev on Trac 1.0.1.

For example, I have a ticket #78. This ticket is blocked by #75 and #77, and it blocks ticket #79. When viewing the ticket, I see the "blocks" and "blocked by" fields are shown correctly. When viewing the dependency graph, all I get is 75 -> 78 -> 79. Ticket #77 is missing. Similarly, ticket #78 is missing from the dependency graph for #77.

Querying the raw database, I get the following:

mysql> select * from mastertickets where source=78 or dest=78;
+--------+------+
| source | dest |
+--------+------+
|     75 |   78 |
|     78 |   79 |
+--------+------+
2 rows in set (0.00 sec)

mysql> select * from mastertickets where source=77 or dest=77;
+--------+------+
| source | dest |
+--------+------+
|     77 |   74 |
+--------+------+
1 row in set (0.00 sec)

mysql> select * from ticket_custom where ticket=78;
+--------+----------------+------------+
| ticket | name           | value      |
+--------+----------------+------------+
|     78 | blockedby      | 75, 77     |
|     78 | blocking       | 79         |
+--------+----------------+------------+
4 rows in set (0.00 sec)

mysql> select * from ticket_custom where ticket=77;
+--------+----------------+-------+
| ticket | name           | value |
+--------+----------------+-------+
|     77 | blockedby      | 78    |
|     77 | blocking       | 74    |
+--------+----------------+-------+
4 rows in set (0.00 sec)

mysql> select * from ticket_custom where value like '%78%';
+--------+-----------+-------+
| ticket | name      | value |
+--------+-----------+-------+
|     75 | blockedby |   78  |
|     77 | blockedby |   78  |
|     79 | blockedby |   78  |
+--------+-----------+-------+
3 rows in set (0.01 sec)

The relationship between tickets #77 and #78 is recorded in the ticket_custom table, but it somehow disappeared from the mastertickets table. I'm assuming that the dependency graph uses the mastertickets table, because that would explain why the node for #77 is missing. This can lead to tickets missing from queries if the tables get out of sync in the other direction (the relationship is in the mastertickets table but not in the ticket_custom table).

Unfortunately, I'm not sure when or how I got into this state; I just happened to notice the inconsistency while working on something unrelated. Regardless of how the data got out of sync, the plugin needs to be proactive in ensuring that the blocker/blockee relationships in the database stay consistent. Whenever a ticket is modified, the plugin can scan the database and resolve any differences regarding data for that ticket between the two tables. I would assume that the data in ticket_custom would be considered the "official" version, because that is what users see and modify.

[Ryan J Ollos]

I think the issue should be fixed in release 4.0.0. You may need to manually fixup your database, however the integrity should be much better now that transaction context managers are used to ensure atomic transactions.