Bug 787036

The code appears to be fine. It is loading from the envers database and pulling the data for revisions accurately. However there appears to be a problem with the TopicToTag_AUD and Tag/Tag_AUD table entries. As such when envers tries to pull the data from these auditing tables it sees that the tag has been deleted in the Tag_AUD table but it still exists in certain TopicToTag_AUD tuples. This causes an exception to be thrown stating that it is unable to find the tag.

There are 8 Tags that no longer exist in the Tag Table but weren't deleted based on the Auditing table (See the ID's below). These however won't cause any issue as from an Envers point of view the tags still exist when pulling revisions. 

43
44
79
80
86
92
100
137

The problem however lies with these tags:

57
96
177
179
186
189
190
191
193
194
195
196
200
201
202
203
204
207
208
209
210
211
212
258

They have been deleted at various points however references still exist in the TopicToTag_AUD table, there are ~ 500 TopicToTags. I'm not entirely sure yet how this would of happened as the delete button on the GUI uses seams remove function from EntityHome to remove the Tag entity. This will need further investigating to find why in certain cases it doesn't remove all references (it most likely lies at the hibernate level). Below is a query that will display the corrupt TopicToTag values:

# Get all TopicToTags that haven't been deleted
SELECT * FROM `TopicToTag_AUD` 
WHERE REVTYPE = 0 
AND TopicToTagID NOT IN (
        SELECT TopicToTagID 
        FROM TopicToTag_AUD 
        WHERE REVTYPE=2) 
# Filter the TopicToTags down to tags that have 
# been removed based on Envers
AND TagID IN (
        SELECT TagID 
        FROM `Tag_AUD` 
        WHERE REVTYPE = 2)

There were three options that I came up with. The first was to insert the TopicToTag tuples to show that it was deleted at the same time as the tag. The second option is to remove the Envers deleted tuple from Tag_AUD so that they are in the same state as the first 8 tags I listed. The last option which I didn't really like, is to delete those TopicToTag tuples. I was just wondering which one you preferred (or if you had other suggestions). The one to make the database as accurate as possible is probably the first option, while the second would be the easiest.

I've sent an email to Matt Casperson to see which option or if any are a viable solution.

To fix this set the tags that no longer exist to deleted and set the TopicToTags to be deleted as well. Some tuples exist where the same Tag ID exists multiple times for the same Topic ID. In this case tuples will be inserted into the Auditing tables so that the first tuple is deleted when the next tuple was created and so on so that only one instance of the Tag exists per Topic at one point in the revision history. Once the fix is applied i'll change this to ON_QA.

Found out that there is also an issue with certain TopicTotopic references as well.

Fixed all of the items that I could find with the database. The was also an issue with:

BlobConstant_AUD
StringConstant_AUD
IntegerConstant_AUD
Role_AUD
TagExclusion_AUD
TagToTagRelationshipType
TagToTag_AUD
TopicSecondOrderData_AUD (index is on the wrong field)
UserRole_AUD

See the Skynet Deployment Details Docspace page for the attached queries and instructions.

*** Bug 821579 has been marked as a duplicate of this bug. ***