Two different files whose names differ only in accents or case break category imports (unicode_ci unique index on entries.name) #645

A category import fails with a 500 when the category holds two different files whose names the database considers equal, for example names that differ only in е/ё, in an accent, or in case. This happens where entries.name has a unique index with the collation utf8mb4_unicode_ci, which ignores case and accents: montage-beta, and fresh installs built from the model. Production has no index on entries.name (see History), so there the pair does not fail; both files are stored.

Reproduce (montage-beta, 2026-10-03, again 2026-10-05)

A category import into a round (photos uploaded in September 2026) failed with:

IntegrityError (1062, "Duplicate entry 'Церковь_Богоявления,_Вышний_Воло...' for key 'ix_entries_name'")

The category holds both of these files, which are two different photos on Commons:

utf8mb4_unicode_ci treats е and ё as the same letter, so the second INSERT hits the unique index. The same happens for other pairs this collation treats as equal: case after the first letter (Photo.jpg / Photo.JPG), accents (é/e), ß/ss, æ/ae, full-width characters, and (to be checked) characters outside the Basic Multilingual Plane such as emoji, which the UCA 4.0.0 collations may all weigh the same.

Cause

• add_entries() removes duplicates only case-insensitively (.lower()), then inserts every name the database does not already have by exact match. Two names that the collation treats as equal are both inserted; the second fails where the index is unique.
• An existing entry with one spelling and a new file with the other fails the same way: the lookup finds the existing row, but under the other spelling, so the code inserts the new name anyway.
• Since Faster import lookups: lookup by file_id in batches of 199 (#513) #654, imports into campaigns opened from 2026-06-01 look existing entries up by file_id first; the second photo then has its own file_id and is inserted as a new row, which is correct, but still fails on a unique name index.

History

• 2016: the model declares entries.name unique (8b1be99). The original schema was utf8mb3_bin with a unique ix_entries_name (montage-dev's old database still has it); a utf8mb3 key on VARCHAR(255) is 765 bytes.
• 2024-04-02 (6ec677d): the app sets SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' on each connection. That sets the connection's collation, not the tables'.
• 2025-07-01: production's tables were all rebuilt as utf8mb4_unicode_ci with row format Compact. A utf8mb4 key on VARCHAR(255) is 1,020 bytes and does not fit Compact's 767-byte index limit, so entries came out without the name index (Category imports time out on production: every file-name lookup reads the whole entries table #650).
• 2025-10: without a unique index, concurrent imports stored 43,965 names twice (exact duplicates; before July 2025 there were 26 in eight years). A unique index can no longer be created on production without merging them.
• 2026-04-19: montage-beta's database was created following deployment.md (85d0be4), with utf8mb4_unicode_ci and the unique index from the model.
• 2026-05-31: production's entries was rebuilt (file_id migration) as Dynamic; the index would fit again, but was not re-added.

Question for whoever set this up: was unicode_ci chosen for a reason?

Options

• A. Match the database in the code: remove duplicates with the database's equality (case- and accent-insensitive), reuse the existing entry, and warn for every file left out. No schema change, but one of two different photos is then silently left out of the contest.
• B. Make entries.name binary (utf8mb4_bin): Commons file names are case- and accent-sensitive. Keeps a unique index; Montage only looks up file names by exact match. Needs a migration (ALTER TABLE entries MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin) and removing the .lower() de-duplication in add_entries(). On production a unique index additionally needs the 43,965 duplicates merged first.
• C. Stop requiring unique names: a file's identity is its file_id. Order: (1) backfill file_id (Backfill file_id for entries imported before #505 migration #513); (2) merge the duplicates and enforce identity by file_id (the import already looks up by file_id since Faster import lookups: lookup by file_id in batches of 199 (#513) #654; rows without a file_id, e.g. files deleted on Commons, still to be decided); (3) make entries.name non-unique, with a non-unique index for fast lookups (Category imports time out on production: every file-name lookup reads the whole entries table #650). Also needs the .lower() de-duplication removed.

users.username has the same pattern (Wikimedia usernames are case-sensitive after the first letter), much less likely to matter in practice.

Related: #146 (exact duplicate names in a file list, the same error class), #641, #650, #654, #513.

Written with Claude (Claude Code) on behalf of @lgelauff. Updated 2026-10-05.