Modify

Opened 4 years ago

Closed 7 months ago

#11235 closed defect (fixed)

database tables get out of sync

Reported by: Ben Allen Owned by: Ryan J Ollos
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 (3)

comment:1 Changed 4 years ago by Ben Allen

Trac Release: 1.0

comment:2 Changed 4 years ago by Ryan J Ollos

Status: newassigned

comment:3 Changed 7 months ago by Ryan J Ollos

Resolution: fixed
Status: assignedclosed

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.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Ryan J Ollos.
The resolution will be deleted.

Add Comment


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

 
Note: See TracTickets for help on using tickets.