HOW-TO: Fix “Multiple AttachmentData objects were returned” errors in Atlassian Confluence

So, if you found this blog post, I assume you’ve run into this particular problem in your installation of Confluence. And you’ve probably also found the Atlassian KB article, RuntimeException Multiple AttachmentData objects were returned when only one was expected, which is only marginally helpful at best. The “resolution” suggested by Atlassian — “Rename the attachment and upload again” — is not a resolution at all. It’s a workaround. It doesn’t even acknowledge the root cause of the problem: that Confluence and its underlying database disagree on the definition of integral attachment data, and the database somehow ends up storing data that Confluence doesn’t like.

Of course, when a problem is worked around rather than resolved, it’s bound to come up again. In this particular case, as long as the wonky data remains in the database, the attachment migration task will fail if you attempt to move your attachments from the database to the filesystem:

ricky@dvwbwiki41:~$ less /var/lib/tomcat6/logs/catalina.out
[...]
Exception in thread "Attachment data migration task" org.springframework.dao.IncorrectResultSizeDataAccessException: Multiple AttachmentData objects were returned when only one was expected for attachment: Attachment: eob-sample10.gif v.2 (155845103) confluenceadmin
[...]

The specifics

As noted in the error above, there are multiple records in the ATTACHMENTDATA table having the same ATTVERSION and ATTACHMENTID values. But that’s just half of the problem. Let’s start with the ATTACHMENTS table:

mysql> select ATTACHMENTID, TITLE, PAGEID, ATTVERSION, PREVVER
     > from ATTACHMENTS where TITLE like '%eob-sample%';
+--------------+------------------+-----------+------------+-----------+
| ATTACHMENTID | TITLE            | PAGEID    | ATTVERSION | PREVVER   |
+--------------+------------------+-----------+------------+-----------+
|    155845103 | eob-sample10.gif | 155718494 |          2 |      NULL |
|    155845106 | eob-sample10.gif | 155718494 |          1 | 155845103 |
|    155845107 | eob-sample10.gif | 155718494 |          1 | 155845103 |
+--------------+------------------+-----------+------------+-----------+
3 rows in set (0.02 sec)

Well, there’s an obvious problem: two ATTACHMENTID with the same ATTVERSION belonging to the same PAGEID. This is why looking at the “Attachments view” on PAGEID 155718494 would show two “Version 1″ listings for this attachment.

Now, let’s take a look at the ATTACHMENTDATA table:

mysql> select ATTACHMENTDATAID, ATTACHMENTID, ATTVERSION
     > from ATTACHMENTDATA where ATTACHMENTID = 155845103;
+------------------+--------------+------------+
| ATTACHMENTDATAID | ATTACHMENTID | ATTVERSION |
+------------------+--------------+------------+
|        155877870 |    155845103 |          2 |
|        155877871 |    155845103 |          2 |
+------------------+--------------+------------+
2 rows in set (0.00 sec)

ZOINKS. There’s the other obvious problem: two different ATTACHMENTDATAID having identical ATTACHMENTID and ATTVERSION values.

The resolution

First, let’s just get something out of the way: I haven’t looked at all into how the data got like this in the first place. And now let me put some words into your mouth: “At this point, you do not care — you just need it fixed so you can go back to your real job and stop troubleshooting this … infernal … thing.”

This is probably not the only, and likely not the best, way to fix this, but one resolution is to delete the unnecessary records from each table:

mysql> delete from ATTACHMENTS where ATTACHMENTID = 155845106;
mysql> delete from ATTACHMENTDATA where ATTACHMENTDATAID = 155877870;

And Bob’s your uncle.

Other thoughts

Taking a look at the actual Jira ticket, CONF-7882, is interesting. One of my favorite snippets from the ticket:

That snippet has been there for over three and a half years and the comment still remains in the code.

But the granddaddy fave from this ticket is what Don Willis says on the ides of April back in 2008:

Don Willis [Atlassian] added a comment – 15/Apr/08 3:18 AM
I suggest we add a unique constraint to the attachmentdata.attachmentid column. That would stop this situation happening in any new instances. It might create another bug, since there’s presumably an application logic flaw that attempts to insert two data rows for the one attachment, but this bug should be easier to find once the unique constraint causes an exception to be thrown immediately by the second insertion.

BINGO! We have a winner! I hope Don is still there, and I hope he someday has a chance to implement this unique constraint (and maybe a composite unique constraint like it on the ATTACHMENTS.TITLE and ATTACHMENTS.ATTVERSION columns?), thereby aligning the application’s idea of data integrity with the database schema. Then, perhaps this deep, dark conspiracy which forces development teams to find defects, log them, identify the root causes, suggest viable solutions, and then … completely forget about them — perhaps then this conspiracy will be exposed once and for all.

And then I hope Don Willis gets a promotion.

Other other thoughts

If Confluence were a truly communal open-source project, I (or some other annoyed user) would likely have checked in a fix for this years ago, someone at Atlassian would have reviewed it (Don?), and maybe it would have been promoted to the testing branch and then release branch. And, dare I say it, the product could be far more stable than it is today, with the defects that annoy users the most being addressed through an open-source community process, while Atlassian develops the lion’s share of the new functionality. Of course, that model probably wouldn’t be simpatico with the current licensing terms for this “open source” product.

Cheers.