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

Assigned To

None

Authored By

	Anomie
	May 13 2014, 4:33 PM

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 Objects
Search...

Status	Subtype	Assigned	Task
Open		None	T69323 MultimediaViewer "HTML embed" shouldn't default to original (unsuitably large)
Open		None	T56037 ForeignAPIRepo / InstantCommons should fetch multiple-density thumbnails in one request
Resolved		Jdlrobson	T103362 Try to get rid of mobileview call for regular pages
Open	Feature	None	T56035 API imageinfo should allow fetching multiple thumbnail sizes
Open	Feature	None	T40891 MediaWiki API 2.0 (tracking)
Open		None	T35226 prop=imageinfo: give empty tag for files without metadata
Resolved		cscott	T56844 Image / media handling (tracking)
Duplicate		cscott	T51896 In Parsoid and MW API, implement handling for file types that do not have thumbnails, such as audio ogg files
Declined	Feature	None	T66491 Media Viewer does not contain link to local file description page
Open	Feature	None	T118653 Return local description page in imageinfo API
Open		None	T86611 API does not fail gracefully when data is too large
Open		None	T36951 Output filekey for prop=stashimageinfo
Open		None	T30559 limit/start/end param of prop=imageinfo are for a list of titles
Resolved		Umherirrender	T91461 Inconsistent value for "suppressed" field in API
Duplicate		None	T101400 This result was truncated because it would otherwise be larger than the limit of 12,582,912 bytes
Open		None	T101457 imageinfo should return info for other files when one file gets a iiurlparamnormal error.
Declined		None	T112045 Parsoid lacks support for mustRender in the non-extension DataAccesses
Duplicate	BUG REPORT	None	T122716 Using imageinfo and videoinfo together in one API request throws error
Open		None	T132946 Deprecate videoinfo
Open	Feature	None	T176673 Add iidir option for prop=imageinfo
Open	Feature	None	T38220 The stashimageinfo module shouldn't be a prop querymodule (it doesn't accept titles and doesn't work with generators)
Open		None	T89971 ApiQueryImageInfo is crufty, needs rewrite
Resolved		None	T26782 API uses non-unique value for paging for some modules
Open		None	T67251 Query-continue for imageinfo returns timestamp which is ambiguous if multiple files were uploaded within the same second under the same file name
Open		None	T67264 File storage uses (title,timestamp) as a unique key, but this is not unique
Open		Ladsgroup	T28741 Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys)
Resolved		Krinkle	T589 RFC: image and oldimage tables
Resolved		Ladsgroup	T368113 Design and merge the new tables of file tables
Resolved		mforns	T389800 Update Commons Impact Metrics to account for new File table
Resolved		Ladsgroup	T383093 Set beta cluster to write both in file schema and thoroughly test it
Resolved		Ladsgroup	T383491 Deploy new file tables in production
Resolved		Ladsgroup	T383492 Write the migration script
Resolved		Zabe	T383496 Add support for reading new file schema into MediaWiki
Resolved		Dreamy_Jazz	T383555 Add support for reading new file schema in MediaModeration
Resolved		Ladsgroup	T384481 Set new file tables to write both in production
Resolved		Ladsgroup	T385167 Run data migration script for file migration
Duplicate		None	T386133 Update Data Pipelines with new image table schema
Resolved		Zabe	T412164 Set beta cluster to read new in file schema and thoroughly test it
Open		Ladsgroup	T415330 Add support for stop writing to the old file db schema
Open		Zabe	T416548 Start reading from file table on wmf production
Open	PRODUCTION ERROR	None	T418007 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
Resolved	PRODUCTION ERROR	Zabe	T418327 PHP Deprecated: Caller from MediaWiki\Exception\MWExceptionHandler::rollbackPrimaryChanges ignored an error originally raised from MediaWiki\Pager\IndexPager::buildQueryInfo (MediaWiki\Specials\Pager\ImageListPager): [1054] Unk
Resolved	PRODUCTION ERROR	Zabe	T419062 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
Open	PRODUCTION ERROR	Zabe	T422946 Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}

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

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

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

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

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

This is probably a duplicate of some older bug.

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

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.

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

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