Modify

Opened 17 months ago

Last modified 12 months ago

#11235 assigned defect

database tables get out of sync

Reported by: AllenB Owned by: rjollos
Priority: normal Component: MasterTicketsPlugin
Severity: critical Keywords:
Cc: Trac Release: 1.0

Description

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.

Attachments (0)

Change History (2)

comment:1 Changed 17 months ago by AllenB

  • Trac Release set to 1.0

comment:2 Changed 12 months ago by rjollos

  • Status changed from new to assigned

Add Comment

Modify Ticket

Action
as assigned The owner will remain rjollos.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.