1.18.4: unique key blocks attachments across different object types

MS1Corp

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!

KevinTheJedi

MS1Corp

Idk what AI told you that but that is not the issue at all... you can attach the same file to any number of objects. If this was an issue we'd get millions of reports, trust me. Something must be happening in your specific situation/setup.

Cheers.

MS1Corp

Hi Kevin,
I found this (I'm a rusty Oracle DBA). I'm not disputing that attaching the same file to any number of objects normally works fine. That's true, and it's actually because of a second, correct unique key (file-type: object_id, file_id, type) that does exactly that job properly.

The edge case is narrower than "attach a file twice": it only triggers when two different object types (e.g. a Canned Response and an FAQ) happen to land on the same numeric row ID by coincidence — since canned_id and faq_id are independent AUTO_INCREMENT counters, that's rare, but not impossible on any install with enough of both. file_object's key (file_id, object_id) doesn't include type, so it can't tell those two rows apart and throws a false duplicate.

This isn't theoretical for us — we hit it for real during a from-scratch dev rebuild, where a Canned Response and an FAQ both happened to land on id 19: Duplicate entry '9-19' for key 'attachment.file_object'

Traced it through include/upgrader/streams/core/: file_object was added in the v1.11.0 patch as a plain, non-unique INDEX (performance only). No later patch ever converts it to UNIQUE — but the current shipped install-mysql.sql has it as UNIQUE KEY anyway. Looks like unintentional drift in the fresh-install schema snapshot, not a deliberate change.

We applied the fix (DROP KEY file_object, leaving file-type untouched) to both our dev and production databases and verified two things directly afterward: (1) the original collision now succeeds, (2) a genuine duplicate (same file+object+type) is still correctly rejected by file-type. No adverse effects since.

Happy to share the exact repro script if useful for a fix upstream.
Thanks!