Error
I'm trying to attach the same exact PDF file to two objects:
Object 1: FAQ (table faq), row ID = 19
Object 2: Canned Response (table canned_response), row ID = 19
Note: it's a coincidence that both rows have ID = 19. The objects were created naturally by normal incremental administration, not with a strategic row numbering approach.
When attaching the PDF to Object 2, I get a duplicate error:
Duplicate entry '9-19' for key 'attachment.file_object'
Steps to reproduce
Attach a file to a Canned Response with canned_id=19.
Attach the same file (identical content) to an FAQ entry with faq_id=19.
Step 2 fails with the error above.
File-level dedup (AttachmentFile::create() in include/class.file.php, matching by content signature+size) is working as intended — it correctly reuses the existing file row. The failure is on the attachment link row.
Root cause
attachment has two unique keys:
UNIQUE KEY file-type (object_id,file_id,type)
UNIQUE KEY file_object (file_id,object_id)
file_object omits type, so it can't distinguish a Canned Response and an FAQ that happen to share the same object_id. file-type already enforces the real constraint (no duplicate file on the same object+type); file_object incorrectly blocks the cross-type case.
Traced through include/upgrader/streams/core/:
file-type (UNIQUE) added in the v1.10.0 patch (36f6b328-5cd0a25a.patch.sql), when the generic attachment table was introduced.
file_object added in the v1.11.0 patch (70921d5c-26fd79dc.patch.sql) as a plain, non-unique INDEX (performance only):
ADD INDEX `file_object` (`file_id`,`object_id`);
No later patch converts it to UNIQUE. The current shipped install-mysql.sql has it as UNIQUE KEY anyway — looks like an unintentional drift in the fresh-install schema snapshot, not a deliberate change.
Suggested fix
Revert file_object to a plain INDEX, matching its original v1.11.0 definition. Leave file-type as is.
I'm trying to understand the impact of applying this fix to our production instance.
Thanks!
