Page MenuHomePhabricator
Create Task
Maniphest T67264

File storage uses (title,timestamp) as a unique key, but this is not unique
Open, HighPublic
Actions

Description

FileRepo::findFile appears to use the (title,timestamp) pair as a unique key to identify a particular version of a file. However, this is not necessarily unique. See for example https://commons.wikimedia.org/wiki/File:Treppe_22_22_test_upload.jpg where two versions were both uploaded at 2014-05-05T13:00:59Z.

For proper operation, the API requires some ordered unique identifier for file revisions. I imagine there are other places in the code that also require unique identifiers.

This appears to depend on bug 15441 (for the oldimage table), although I'm not familiar enough with the FileBackend code to know for sure whether that's generally the case or if it only applies to certain backends.

Version: 1.23.0
Severity: normal

Details

Reference
bz65264
Related Changes in Gerrit:
SubjectAuthorRepoBranchLines +/-
migrateFileTables: Skip duplicate oldimage rows within a single runZabemediawiki/coremaster+19 -35
Add script to drop duplicate oldimage/filerevision rowsZabemediawiki/extensions/WikimediaMaintenancewmf/1.47.0-wmf.22+259 -0
Add script to drop duplicate oldimage/filerevision rowsZabemediawiki/extensions/WikimediaMaintenancewmf/1.47.0-wmf.21+259 -0
Add script to drop duplicate oldimage/filerevision rowsZabemediawiki/extensions/WikimediaMaintenancemaster+259 -0
Customize query in gerrit

Related Objects
Search...

StatusSubtypeAssignedTask
 OpenNoneT69323 MultimediaViewer "HTML embed" shouldn't default to original (unsuitably large)
 ResolvedJdlrobsonT103362 Try to get rid of mobileview call for regular pages
 DeclinedFeatureNoneT56035 API imageinfo should allow fetching multiple thumbnail sizes
 OpenFeatureNoneT40891 MediaWiki API 2.0 (tracking)
 OpenNoneT35226 prop=imageinfo: give empty tag for files without metadata
 ResolvedcscottT56844 Image / media handling (tracking)
 DuplicatecscottT51896 In Parsoid and MW API, implement handling for file types that do not have thumbnails, such as audio ogg files
 DeclinedFeatureNoneT66491 Media Viewer does not contain link to local file description page
 OpenFeatureNoneT118653 Return local description page in imageinfo API
 OpenNoneT86611 API does not fail gracefully when data is too large
 OpenNoneT36951 Output filekey for prop=stashimageinfo
 OpenNoneT30559 limit/start/end param of prop=imageinfo are for a list of titles
 ResolvedUmherirrenderT91461 Inconsistent value for "suppressed" field in API
 DuplicateNoneT101400 This result was truncated because it would otherwise be larger than the limit of 12,582,912 bytes
OpenNoneT101457 imageinfo should return info for other files when one file gets a iiurlparamnormal error.
 DeclinedNoneT112045 Parsoid lacks support for mustRender in the non-extension DataAccesses
 DuplicateBUG REPORTNoneT122716 Using imageinfo and videoinfo together in one API request throws error
 OpenNoneT132946 Deprecate videoinfo
 OpenFeatureNoneT176673 Add iidir option for prop=imageinfo
 OpenFeatureNoneT38220 The stashimageinfo module shouldn't be a prop querymodule (it doesn't accept titles and doesn't work with generators)
 OpenNoneT89971 ApiQueryImageInfo is crufty, needs rewrite
 ResolvedNoneT26782 API uses non-unique value for paging for some modules
 OpenNoneT67251 Query-continue for imageinfo returns timestamp which is ambiguous if multiple files were uploaded within the same second under the same file name
 OpenNoneT67264 File storage uses (title,timestamp) as a unique key, but this is not unique
 OpenLadsgroupT28741 Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys)
 ResolvedKrinkleT589 RFC: image and oldimage tables
 ResolvedLadsgroupT368113 Design and merge the new tables of file tables
 ResolvedmfornsT389800 Update Commons Impact Metrics to account for new File table
 ResolvedLadsgroupT383093 Set beta cluster to write both in file schema and thoroughly test it
 ResolvedLadsgroupT383491 Deploy new file tables in production
 ResolvedLadsgroupT383492 Write the migration script
 ResolvedZabeT383496 Add support for reading new file schema into MediaWiki
 ResolvedDreamy_JazzT383555 Add support for reading new file schema in MediaModeration
 ResolvedLadsgroupT384481 Set new file tables to write both in production
 ResolvedLadsgroupT385167 Run data migration script for file migration
 DuplicateNoneT386133 Update Data Pipelines with new image table schema
 ResolvedZabeT412164 Set beta cluster to read new in file schema and thoroughly test it
 ResolvedLadsgroupT415330 Add support for stop writing to the old file db schema
 OpenZabeT416548 Start reading from file table on wmf production
 OpenPRODUCTION ERRORNoneT418007 Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry 'Savannah_River_(26769689014).jpg' for key 'file_name'Function: MediaWiki\FileRepo\File\LocalFile::acquireFileIdFromNameQuery: INSERT INTO `file` (file_name,file_lates
 ResolvedPRODUCTION ERRORZabeT418327 PHP Deprecated: Caller from MediaWiki\Exception\MWExceptionHandler::rollbackPrimaryChanges ignored an error originally raised from MediaWiki\Pager\IndexPager::buildQueryInfo (MediaWiki\Specials\Pager\ImageListPager): [1054] Unk
 ResolvedPRODUCTION ERRORZabeT419062 Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'img_timestamp' in 'WHERE'Function: MediaWiki\Pager\IndexPager::buildQueryInfo (MediaWiki\Specials\Pager\NewFilesPager)Query: SELECT /*! STRAIGHT_JOIN */ file_name AS
 ResolvedPRODUCTION ERRORZabeT422946 Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}
 ResolvedSecurityZabeT423654 Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}
 ResolvedBUG REPORTLadsgroupT423978 Special:Mediastatistics is being weird and not grouping similar file types together
 OpenZabeT425191 Drop old image tables from wikireplicas
 ResolvedZabeT426804 filerevision view should not filter out deleted file revisions
 StalledZabeT428970 Stop writing to the old file db schema
 ResolvedBUG REPORTManvikesarwani09T429720 Files on Betacommons can be moved once, then never again
 OpenBUG REPORTNoneT439616 Phantom revisions in filerevision table due to incorrect rollbacks of queries during early WRITE_NEW phase

Event Timeline

• bzimport raised the priority of this task from to High.Nov 22 2014, 3:24 AM
• bzimport added a project: MediaWiki-File-management.
• bzimport set Reference to bz65264.
• bzimport added a subscriber: Unknown Object (MLST).
Anomie created this task.May 13 2014, 4:33 PM
gerritbot added a comment.May 13 2014, 10:04 PM

Change 133178 had a related patch set uploaded by Aaron Schulz:
Made LocalFile avoid duplicate (name,timestamp) pairs

https://gerrit.wikimedia.org/r/133178

gerritbot added a comment.May 13 2014, 10:21 PM

Change 133178 merged by jenkins-bot:
Made LocalFile avoid duplicate (name,timestamp) pairs

https://gerrit.wikimedia.org/r/133178

Platonides added a comment.May 14 2014, 12:01 AM

This is probably a duplicate of some older bug.

Anomie added a comment.May 14 2014, 2:49 PM

So change 133178 fixed it going forward, which is good. Although I left a comment there.

We'll also need to do database cleanup for situations where this already exists, such as https://commons.wikimedia.org/wiki/File:Treppe_22_22_test_upload.jpg, to completely fix the bug. I also wonder if it's worth making the oi_name_timestamp index unique, although that would only detect the problem after the fact since the row for the current version isn't added to oldimage until a new version becomes current.

• Gilles added a project: Multimedia.Nov 24 2014, 3:33 PM
Anomie mentioned this in T67251: Query-continue for imageinfo returns timestamp which is ambiguous if multiple files were uploaded within the same second under the same file name.Feb 13 2015, 7:07 PM
Jdforrester-WMF moved this task from Untriaged to Backlog on the Multimedia board.Sep 4 2015, 6:46 PM
Restricted Application added subscribers: Steinsplitter, Matanya, Aklapper. · View Herald TranscriptSep 4 2015, 6:46 PM
MarkTraceur unsubscribed.Jan 27 2017, 10:36 PM
Restricted Application added a project: Commons. · View Herald TranscriptJan 27 2017, 10:36 PM
Krinkle edited subtasks, added: T28741: Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys); removed: T17441: Some tables lack unique or primary keys, may allow confusing duplicate data.Apr 12 2017, 8:55 PM
Liuxinyu970226 subscribed.Nov 26 2017, 11:20 PM
Anomie mentioned this in T192866: Some DjVu files have too much metadata to fit in their database column.Apr 25 2018, 4:06 PM
Aklapper removed subscribers: Anomie, • Fabrice_Florin, • wikibugs-l-list.Oct 16 2020, 5:01 PM
Krinkle mentioned this in T299764: Document media recovery use case proposals and decide their priority.Jun 3 2022, 6:53 PM
Ladsgroup subscribed.Jan 10 2025, 2:06 AM

The existing duplicate data might make the migration to the new structure a bit complicated:

mysql:research@s1-analytics-replica.eqiad.wmnet [enwiki]> select oi_name, oi_timestamp, oi_archive_name  from oldimage where oi_archive_name in (select oi_archive_name from oldimage group by oi_archive_name havi
ng count(*) > 1) limit 50;
+----------------------------------------------------------------+----------------+------------------------------------------+
| oi_name                                                        | oi_timestamp   | oi_archive_name                          |
+----------------------------------------------------------------+----------------+------------------------------------------+
| Elas.gif                                                       | 20030120080513 | 20030122141949!Elas.gif                  |
| Elas.gif                                                       | 20030122142001 | 20030122141949!Elas.gif                  |
| Elas.gif                                                       | 20030122142227 | 20030122141949!Elas.gif                  |
| Squirell.500mm.5yards.jpg                                      | 20040821201010 | 20040821201046!Squirell.500mm.5yards.jpg |
| Squirell.500mm.5yards.jpg                                      | 20040821201217 | 20040821201046!Squirell.500mm.5yards.jpg |
| LPLW.JPG                                                       | 20070502003039 | 20070502003043!LPLW.JPG                  |
| LPLW.JPG                                                       | 20070502003043 | 20070502003043!LPLW.JPG                  |
| ConfederateLincoln.png                                         | 20061103224104 |                                          |
| Nearing-scott-1918.jpg                                         | 20090115091745 |                                          |
| Joe_Brooks_wiki.jpg                                            | 20090309235754 |                                          |
| RobertHIGoddard-1909.jpg                                       | 20090716151612 |                                          |
| SiobhanOwen_Singer_Harpist_2012.jpg                            | 20120907143842 |                                          |
| Arab_League_Languages.PNG                                      | 20121023151455 |                                          |
| Hines_Booth.jpg                                                | 20080827154940 | 20080827155054!Hines_Booth.jpg           |
| Hines_Booth.jpg                                                | 20080808042730 | 20080827154940!Hines_Booth.jpg           |
| Hines_Booth.jpg                                                | 20080803223030 | 20080808042730!Hines_Booth.jpg           |
| Hines_Booth.jpg                                                | 20080827154940 | 20080827155054!Hines_Booth.jpg           |
| Hines_Booth.jpg                                                | 20080808042730 | 20080827154940!Hines_Booth.jpg           |
| Hines_Booth.jpg                                                | 20080803223030 | 20080808042730!Hines_Booth.jpg           |
| Malcolm_Glazer.jpg                                             | 20160801213343 | 20180103064117!Malcolm_Glazer.jpg        |
| Malcolm_Glazer.jpg                                             | 20160801213343 | 20180103064117!Malcolm_Glazer.jpg        |
| KKNO_logo.jpg                                                  | 20080223183936 |                                          |
| Logo_of_the_International_Practical_Shooting_Confederation.png | 20210823171756 |                                          |
| Maquinista,_diptico_2008.jpg                                   | 20220513034020 |                                          |
| Charles_Xavier_(Patrick_Stewart).jpg                           | 20220824010434 |                                          |
| BBCAN11_titlecard.jpg                                          | 20230308232159 |                                          |
| Msk_novohrad_lucenec_logo.png                                  | 20230309085314 |                                          |
| Orlen_Lietuva_logo.jpg                                         | 20230919140811 |                                          |
| Seattle_Fire_Department_Official_Logo.png                      | 20240818205749 |                                          |
+----------------------------------------------------------------+----------------+------------------------------------------+
29 rows in set (3.767 sec)

We get empty ones too and they are not fixed.

Ladsgroup added a comment.Jan 10 2025, 3:49 PM

combination of oi_name and oi_timestamp is much cleaner though:

mysql:research@s1-analytics-replica.eqiad.wmnet [enwiki]> select oi_name, oi_timestamp, count(*) from oldimage group by oi_name, oi_timestamp having count(*) > 1;
+--------------------------------+----------------+----------+
| oi_name                        | oi_timestamp   | count(*) |
+--------------------------------+----------------+----------+
| Hines_Booth.jpg                | 20080803223030 |        2 |
| Hines_Booth.jpg                | 20080808042730 |        2 |
| Hines_Booth.jpg                | 20080827154940 |        2 |
| Joe_Brooks_wiki.jpg            | 20090309235754 |        2 |
| Malcolm_Glazer.jpg             | 20160801213343 |        2 |
| Me_at_Pizza_Hut.jpg            | 20070305143001 |        2 |
| New_English_Bible_cover.jpg    | 20060506015320 |        2 |
| State_police_Crawford_Erie.jpg | 20090215041007 |        2 |
+--------------------------------+----------------+----------+
8 rows in set (0.771 sec)

All cases are identical files being uploaded:

mysql:research@s1-analytics-replica.eqiad.wmnet [enwiki]> select oi_name, oi_timestamp, oi_archive_name, oi_sha1  from oldimage where concat(oi_name, '!', oi_timestamp) in (select concat(oi_name, '!', oi_timestamp) from oldimage group by oi_name, oi_timestamp having count(*) > 1) order by oi_name,oi_timestamp limit 50;
+--------------------------------+----------------+-----------------------------------------------+---------------------------------+
| oi_name                        | oi_timestamp   | oi_archive_name                               | oi_sha1                         |
+--------------------------------+----------------+-----------------------------------------------+---------------------------------+
| Hines_Booth.jpg                | 20080803223030 | 20080808042730!Hines_Booth.jpg                | dx3q3qn05m8vdjh534zoxg2t7j426bs |
| Hines_Booth.jpg                | 20080803223030 | 20080808042730!Hines_Booth.jpg                | dx3q3qn05m8vdjh534zoxg2t7j426bs |
| Hines_Booth.jpg                | 20080808042730 | 20080827154940!Hines_Booth.jpg                | 8t9bk58rrfo1trqs510n3u237tozv7d |
| Hines_Booth.jpg                | 20080808042730 | 20080827154940!Hines_Booth.jpg                | 8t9bk58rrfo1trqs510n3u237tozv7d |
| Hines_Booth.jpg                | 20080827154940 | 20080827155054!Hines_Booth.jpg                | k3axmxtrj8zvopksvf5fi8kbl5atn5y |
| Hines_Booth.jpg                | 20080827154940 | 20080827155054!Hines_Booth.jpg                | k3axmxtrj8zvopksvf5fi8kbl5atn5y |
| Joe_Brooks_wiki.jpg            | 20090309235754 |                                               | qu8ndqxgaue9u135l9sydn9s9vv8hv7 |
| Joe_Brooks_wiki.jpg            | 20090309235754 | 20090310002032!Joe_Brooks_wiki.jpg            | qu8ndqxgaue9u135l9sydn9s9vv8hv7 |
| Malcolm_Glazer.jpg             | 20160801213343 | 20180103064117!Malcolm_Glazer.jpg             | d13rs6nopm9k6q8o21rfid4zc3533l9 |
| Malcolm_Glazer.jpg             | 20160801213343 | 20180103064117!Malcolm_Glazer.jpg             | d13rs6nopm9k6q8o21rfid4zc3533l9 |
| Me_at_Pizza_Hut.jpg            | 20070305143001 | 20070305143001!Me_at_Pizza_Hut.jpg            | b79qyiw1es9cts54gqgmn493flphkxr |
| Me_at_Pizza_Hut.jpg            | 20070305143001 | 20070305143046!Me_at_Pizza_Hut.jpg            | b79qyiw1es9cts54gqgmn493flphkxr |
| New_English_Bible_cover.jpg    | 20060506015320 | 20110925002150!New_English_Bible_cover.jpg    | q8086om09p23sfk7yauyptfbvvsr890 |
| New_English_Bible_cover.jpg    | 20060506015320 | 20190628054743!New_English_Bible_cover.jpg    | q8086om09p23sfk7yauyptfbvvsr890 |
| State_police_Crawford_Erie.jpg | 20090215041007 | 20090215041007!State_police_Crawford_Erie.jpg | fap8pvagamdyfv51ozkmnptj9tf344i |
| State_police_Crawford_Erie.jpg | 20090215041007 | 20150514060412!State_police_Crawford_Erie.jpg | fap8pvagamdyfv51ozkmnptj9tf344i |
+--------------------------------+----------------+-----------------------------------------------+---------------------------------+
16 rows in set (2.687 sec)

I suggest simply dropping those rows

Ladsgroup added a comment.EditedJan 16 2025, 12:48 AM

I've decided in the migration script just ignore inserting any row that has the same timestamp and sha1. If the files are the same and the upload time is the same, it was a duplicate entry and one of them should politely bite the dust.

Magnus closed subtask T28741: Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys) as Resolved.Mar 16 2025, 5:18 PM
Bugreporter reopened subtask T28741: Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys) as Open.Mar 16 2025, 5:38 PM
CXuesong mentioned this in T392771: MediaWiki API imageinfo continuation leads to a dead loop.Apr 27 2025, 12:47 PM
• PEPE1234.13 closed subtask T28741: Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys) as Invalid.Sep 6 2025, 1:28 PM
Aklapper reopened subtask T28741: Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys) as Open.Sep 6 2025, 2:18 PM
Bugreporter2 updated the task description. (Show Details)Aug 29 2026, 3:59 AM
gerritbot added a comment.Mon, Sep 28, 9:32 PM

Change #1345656 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/WikimediaMaintenance@master] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1345656

gerritbot added a project: Patch-For-Review.Mon, Sep 28, 9:32 PM
gerritbot added a comment.Wed, Sep 30, 3:33 AM

Change #1345656 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMaintenance@master] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1345656

Maintenance_bot removed a project: Patch-For-Review.Wed, Sep 30, 4:31 AM
gerritbot added a comment.Wed, Sep 30, 11:15 AM

Change #1346537 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/WikimediaMaintenance@wmf/1.47.0-wmf.22] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1346537

gerritbot added a project: Patch-For-Review.Wed, Sep 30, 11:15 AM

Change #1346538 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/WikimediaMaintenance@wmf/1.47.0-wmf.21] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1346538

TheDJ mentioned this in T439616: Phantom revisions in filerevision table due to incorrect rollbacks of queries during early WRITE_NEW phase.Wed, Sep 30, 1:10 PM
Zabe removed a project: Multimedia.Wed, Sep 30, 2:16 PM
gerritbot added a comment.Wed, Sep 30, 2:43 PM

Change #1346538 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMaintenance@wmf/1.47.0-wmf.21] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1346538

gerritbot added a comment.Wed, Sep 30, 2:44 PM

Change #1346537 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMaintenance@wmf/1.47.0-wmf.22] Add script to drop duplicate oldimage/filerevision rows

https://gerrit.wikimedia.org/r/1346537

Stashbot mentioned this in T428406: old file revisions missing of File:A_Warm_Shade_of_Ivory_-_Henry_Mancini_album_cover.jpg.Wed, Sep 30, 2:44 PM

Mentioned in SAL (#wikimedia-operations) [2026-09-30T14:44:36Z] <zabe@deploy1003> Started scap sync-world: Backport for [[gerrit:1346537|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]], [[gerrit:1346538|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]]

Stashbot added a comment.Wed, Sep 30, 2:48 PM

Mentioned in SAL (#wikimedia-operations) [2026-09-30T14:48:41Z] <zabe@deploy1003> zabe: Backport for [[gerrit:1346537|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]], [[gerrit:1346538|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Zabe subscribed.Wed, Sep 30, 2:53 PM
Stashbot added a comment.Wed, Sep 30, 2:54 PM

Mentioned in SAL (#wikimedia-operations) [2026-09-30T14:54:29Z] <zabe@deploy1003> Finished scap sync-world: Backport for [[gerrit:1346537|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]], [[gerrit:1346538|Add script to drop duplicate oldimage/filerevision rows (T67264 T428406)]] (duration: 09m 54s)

Maintenance_bot removed a project: Patch-For-Review.Wed, Sep 30, 3:31 PM
gerritbot added a comment.Sat, Oct 3, 10:55 PM

Change #1350402 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/core@master] migrateFileTables: Skip duplicate oldimage rows within a single run

https://gerrit.wikimedia.org/r/1350402

gerritbot added a project: Patch-For-Review.Sat, Oct 3, 10:55 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits