filerevision; add primary keys)
Open, MediumPublic
Actions

Description

The most recent version of each image is stored in the image table, with historic versions stored in the oldimage table. This structure resembles cur and old, with all the nastiness that comes with it. In MW 1.5 we ditched cur/old in favor of page/revision, but we never did a similar thing for files.

We talked about this a bit at the 2011 Amsterdam Hackathon and decided that, while it's complex and hairy, we want to do it at some point in the future.

RFC

Task: T589: RFC: image and oldimage tables
Status: Approved
https://www.mediawiki.org/wiki/Requests_for_comment/image_and_oldimage_tables

In a nut shell

Currently, the oldimage table contains information about non-current file revisions, and the image table contains information about the current file revisions as well as the unversioned information about a file in general. This means that to query all revisions, MediaWiki has to query two tables. It also means that whenever a file gains a new revision, the "current" data has to be moved across from one table to another.

The approved changes will rename oldimage to filerevision and add the missing rows for the current file revisions. Then, the image (now: file) table can be reduced to just containing the generic file information. This matches the way the page and revision tables have been organised in the database since MediaWiki 1.5 (before the page/revision tables existed, MW used curpage/oldpage tables which is similar to these old image tables, and is why today we still have a notion of curid=pageid and oldid=revid).

Approved changes

Add fields:
- img_id: New primary key for image.
- img_latest: Pointer to oldimage.oi_id for the current revision of a file (similar to how page.page_latest points to revision.rev_id)
- oi_id: New primary key for oldimage.
- oi_img: Pointer to image.img_id for the file this revision corresponds to (similar to how revision.rev_page points to page.page_id)
Rename tables:
- Rename image to file.
- Rename oldimage to filerevision.
Add missing rows in filerevision for current revisions, based on rows from image.
Reduce fields in file to only be the minimum amount of derived data we need indexed for the "current" revision. Currently indexed: img_timestamp, img_user_text, img_sha1, img_media_type, img_major_mime, img_minor_mime, img_size.
- img_timestamp: Remove. Only used for file history, redundant now with the missing rows now present in filerevision.
- img_user_text: Remove. Only used for file history (ApiQueryAllImages, SpecialMIMEsearch), these queries can instead query or join filerevision directly.
- img_sha1: Keep. Needed for reverse look-up in duplication detection on current file revisions.
- img_media_type img_major_mime, img_minor_mime: Keep. Needed for SpecialMIMESearch. Note that (while out of scope of this task), there has been talk about creating a separate RFC for removing these fields in favour of a SearchEngine-based approach.
- img_size: Remove. Not needed as indexed field. Only used by checkImages.php and Special:MediaStatistics (SUM query), these can directly query/join against filerevision.
- img_width, img_height: Remove. Not needed as indexed field. Only existed because oldimage didn't contain current revisions, these fields are redundant in filerevision.
- img_bits: Remove. Not needed. Redundant now with filerevision.
- img_description: Remove. Not needed. Redundant now with filerevision.
- img_user: Remove. Not needed. Redundant now with filerevision. Used by ApiQueryAllImages and NewFilesPager (SpecialNewimages) which could query/join filerevision instead. Though, should probably use recentchanges.rc_bot instead. (unrelated)

Proposed migration strategy

Exact schema migration script to be written as part of the implementation and fine-tuned as needed during code review. We may need two separate strategies due to the size of the migration (one for the default db updater, and an opt-in maintenance script for large farms such as Wikimedia Foundation). A few ideas so far:

Tim Starling, T589#2747454:
- Rename image to filerevision. Create a view called image.
- Add new fields to filerevision.
- Create file table.
- (Disable uploads.)
- Populate file from filerevision.
- Also move oldimage rows into filerevision. (These will be made invisible from the image view.)
- (Deploy new MediaWiki version that uses file/filerevision.)
- (Re-enable uploads)
- Drop image and oldimage.
Jaime Crespo mentioned the idea of potentially doing the migration offline while serving traffic from codfw instead of eqiad.

Announcements

Details

Reference: bz26741

Related Changes in Gerrit:

	Subject	Author	Repo	Branch	Lines +/-
	file: Move filerevision insert within endAtomic() block	Krinkle	mediawiki/core	master	+4 -1
	Introduce img_id column	Ladsgroup	mediawiki/core	master	+439 -34

Customize query in gerrit

Related Objects
Search...

View Standalone Graph

This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Status	Subtype	Assigned	Task
			· · ·
Open		None	T33257 File properties storage (tracking)
Resolved		LSobanski	T17441 Some tables lack unique or primary keys, may allow confusing duplicate data
Open		None	T67264 File storage uses (title,timestamp) as a unique key, but this is not unique
Open		None	T87268 Copyright license and attribution issues (tracking)
Open		None	T27854 Implement oEmbed API (+ discovery ) for File pages (image thumbs and video player)
Open	Feature	None	T33338 Export file thumbnail, video preview via Open Graph meta elements as Facebook posting helper
Open		None	T414419 Transcode table should refer to file id
Open		None	T425897 Future of filearchive table
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		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		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
Resolved		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
Resolved	PRODUCTION ERROR	Zabe	T422946 Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}
Resolved	Security	Zabe	T423654 Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}
Resolved	BUG REPORT	Ladsgroup	T423978 Special:Mediastatistics is being weird and not grouping similar file types together
Open		Zabe	T425191 Drop old image tables from wikireplicas
Stalled		Zabe	T428970 Stop writing to the old file db schema
Resolved	BUG REPORT	Manvikesarwani09	T429720 Files on Betacommons can be moved once, then never again
Open	BUG REPORT	None	T439616 Phantom revisions in filerevision table due to incorrect rollbacks of queries during early WRITE_NEW phase
			· · ·

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Yes!

Ladsgroup mentioned this in T386643: Move large file metadata to external store.Feb 17 2025, 3:18 PM

Bugreporter mentioned this in T381629: Remove rows about file metadata from text table.Feb 19 2025, 3:09 AM

In T28741#10541452, @Ladsgroup wrote:

In T28741#10541414, @VirginiaPoundstone wrote:

@Ladsgroup do you have a timeline for this migration?

It depends on how fast the migration script will go. Since we are waiting for text table migration to finish before starting image table migration on commons. I think the data will be migrated in two months.

I suggest writing the code behind a feature flag and ready to flip the switch once the migration of data is done since we can't wait long with both data schema in place.

For most other wikis, the data migration is done already.

See T183490: MCR schema migration stage 4: Migrate External Store URLs (wmf production) and T382778: Optimize text table.

I am trying to fix my tools to use the new tables, once available. I am looking at the DB replicas, which have them enables but not complete. I have some questions which I did not find answered above:

How do I get the img_user_text via the filerevison table? Which field is the ID to that, and which table would it reference?
The fr_timestamp field appears to be an integer. How do I convert that to the previous text format, preferably within the query itself?
What table does the fr_description_id reference?

Thanks!

In T28741#10637163, @Magnus wrote:

I am trying to fix my tools to use the new tables, once available. I am looking at the DB replicas, which have them enables but not complete. I have some questions which I did not find answered above:

How do I get the img_user_text via the filerevison table? Which field is the ID to that, and which table would it reference?

This is part of the decade-long move of these names into the actor concept (img_actor in image). You should be able to get the text via JOIN actor ON fr_actor = actor_id.

The fr_timestamp field appears to be an integer. How do I convert that to the previous text format, preferably within the query itself?

The fr_timestamp field is an mwtimestamp, same as used pretty much everywhere, including img_timestamp. Were you using a view that converted that to a different format?

What table does the fr_description_id reference?

It's the replacement for img_description_id, and refers to the same table, comment.

For more details, you can see the just-created pages https://www.mediawiki.org/wiki/Manual:File_table and https://www.mediawiki.org/wiki/Manual:Filerevision_table (and https://www.mediawiki.org/wiki/Manual:Filetypes_table) – sorry about the lack of documentation yet.

@Jdforrester-WMF Thanks!

It appears fr_actor is not exposed on the replica; I would have made the connection otherwise. As it is, I can not test any related new code because of this.

In T28741#10639754, @Magnus wrote:

It appears fr_actor is not exposed on the replica; I would have made the connection otherwise. As it is, I can not test any related new code because of this.

Will be fixed with https://gerrit.wikimedia.org/r/c/operations/puppet/+/1128041.

Magnus closed this task as Resolved.Mar 16 2025, 5:18 PM

Please wait until all subtasks are closed before closing this task.

Ottomata moved this task from Needs Clarification to Tag with Radar on the Data-Engineering board.Apr 1 2025, 5:35 PM

Ottomata added a project: Data-Engineering-Radar.

@Ladsgroup: Reported at T389586: Wikimedia\Rdbms\DBQueryError: Error 1062: Duplicate entry moving a file; Function: LocalFileMoveBatch::doDBUpdates

Currently moving a file to a name that is used by a deleted file is broken, since it will change file_name to a name that already exists, violating unique key constraint. Until we also move deleted file revisions to file/filerevision table (i.e. kill filearchive table, which is not part of current work), we can just drop the old file row related to the target name. (But once filearchive is killed, we also need to migrate fr_file to unify two records, plus we need to handle history spliting, etc.)

Tgr mentioned this in T90300: Suppressed username shown on File pages.Apr 24 2025, 4:48 PM

Zabe mentioned this in T299953: Normalize imagelinks table.Jul 1 2025, 2:46 PM

Wellverywell subscribed.Jul 3 2025, 3:54 AM

• PEPE1234.13 closed subtask T386133: Update Data Pipelines with new image table schema as Invalid.Sep 6 2025, 1:25 PM

• PEPE1234.13 renamed this task from Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys) to Migrate fmage; file/filerevision; ad.Sep 6 2025, 1:28 PM

• PEPE1234.13 closed this task as Invalid.

• PEPE1234.13 removed Ladsgroup as the assignee of this task.

• PEPE1234.13 raised the priority of this task from Medium to Unbreak Now!.

• PEPE1234.13 removed subscribers: Wellverywell, Magnus, zdev and 51 others.

Aklapper reopened subtask T386133: Update Data Pipelines with new image table schema as Open.Sep 6 2025, 2:02 PM

Aklapper reopened this task as Open.Sep 6 2025, 2:18 PM

Aklapper assigned this task to Ladsgroup.

Aklapper lowered the priority of this task from Unbreak Now! to Medium.

Aklapper edited subscribers, added: Wellverywell, Magnus, zdev and 46 others; removed: • VirginiaPoundstone.

TheDJ renamed this task from Migrate fmage; file/filerevision; ad to Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys).Sep 6 2025, 2:36 PM

Zabe created subtask T412164: Set beta cluster to read new in file schema and thoroughly test it.Dec 9 2025, 10:55 PM

TheDJ added a parent task: T414419: Transcode table should refer to file id.Jan 13 2026, 10:38 AM

Ladsgroup closed subtask T385167: Run data migration script for file migration as Resolved.Jan 17 2026, 2:09 AM

Zabe closed subtask T383496: Add support for reading new file schema into MediaWiki as Resolved.Jan 21 2026, 3:08 PM

Ladsgroup created subtask T415330: Add support for stop writing to the old file db schema.Jan 23 2026, 1:13 AM

Zabe created subtask T416548: Start reading from file table on wmf production.Feb 5 2026, 3:58 AM

Ladsgroup mentioned this in T417301: Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'img_name' in 'WHERE' Function: MediaWiki\FileRepo\File\LocalFile::loadExtraFromDB Query: SELECT * FROM (SELECT fr_metadata AS `img_metadata` FROM `file` JOIN `filere.Feb 12 2026, 7:14 PM

Zabe closed subtask T412164: Set beta cluster to read new in file schema and thoroughly test it as Resolved.Feb 14 2026, 8:20 PM

• Nurzebetty3 added mocks: M338: REST API with /page/random, M336: Ideas for scaling Custom translation suggestions, M334: Developer Satisfaction Survey/2023/Demographics, M332: MediaWiki Skin Variables and Codex Tokens Architecture, Restricted Mockup.Feb 17 2026, 4:27 AM

valerio.bozzolan removed a mock: Restricted Mockup.Feb 17 2026, 6:52 AM

Ciencia_Al_Poder removed a mock: M338: REST API with /page/random.Feb 17 2026, 9:25 AM

Ciencia_Al_Poder removed a mock: M336: Ideas for scaling Custom translation suggestions.

Ciencia_Al_Poder removed a mock: M334: Developer Satisfaction Survey/2023/Demographics.

Ciencia_Al_Poder removed a mock: M332: MediaWiki Skin Variables and Codex Tokens Architecture.

Zabe added a subtask: 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.Feb 20 2026, 4:26 PM

Zabe added a subtask: 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.Feb 25 2026, 1:46 AM

Zabe added a subtask: 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.Mar 4 2026, 8:27 PM

Mr._Starfleet_Command subscribed.Mar 4 2026, 10:04 PM

Zabe closed subtask 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 as Resolved.Mar 5 2026, 12:25 AM

Mr._Starfleet_Command unsubscribed.Mar 5 2026, 12:40 AM

Zabe closed subtask 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 as Resolved.Mar 5 2026, 1:30 AM

From the task description I see "Create a view called image" but I do not see "Create a view called oldimage". Is it desired to create oldimage view as well?

(I would say yes, according to https://phabricator.wikimedia.org/T28741#6750642)

Very likely we will just announce the deprecation and won't create a b/c view[1]. Give it a month or two and then drop the old tables.

[1] The main point of views is to hide private information, when people use them for b/c, it adds a lot of complexity and has many times led to private data getting leaked through these views. I think we should avoid them as much as possible.

TheDJ mentioned this in T422847: Error: Cannot unset string offsets (in includes/Media/GIFHandler.php).Apr 10 2026, 12:22 PM

NotKlea subscribed.Apr 10 2026, 1:59 PM

Zabe added a subtask: T422946: Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}.Apr 10 2026, 2:30 PM

We will start removing the old tables from wikireplicas on 28 May.

I announced this on cloud-announce@lists.wikimedia.org and it will also be part of the next Tech News release.

Thank you <3

Zabe closed subtask T422946: Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query} as Resolved.Apr 16 2026, 7:47 PM

Zabe added a subtask: T423654: Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query}.Apr 16 2026, 8:35 PM

Nemoralis added a project: User-notice.Apr 17 2026, 7:12 AM

Nemoralis moved this task from To Triage to Announce in next Tech/News on the User-notice board.Apr 17 2026, 7:15 AM

STei-WMF moved this task from Announce in next Tech/News to In current Tech/News draft on the User-notice board.Apr 17 2026, 11:44 AM

Quiddity updated the task description. (Show Details)Apr 17 2026, 7:54 PM

TechnoSquirrel69 mentioned this in T423755: Deleted file revisions displaying improperly.Apr 18 2026, 12:10 AM

Lokal_Profil mentioned this in T423764: Update queries using image or oldimage tables.Apr 18 2026, 8:18 AM

don-vip opened https://gitlab.wikimedia.org/toolforge-repos/spacemedia/-/merge_requests/32

T28741 - Migrate from image/oldimage to file/filerevision

don-vip merged https://gitlab.wikimedia.org/toolforge-repos/spacemedia/-/merge_requests/32

T28741 - Migrate from image/oldimage to file/filerevision

Please see T423998: The imagelinks replica in hewiki is not available.

In T28741#11843014, @IKhitron wrote:

Please see T423998: The imagelinks replica in hewiki is not available.

Caused by T419635: Drop il_to column from imagelinks table in wmf production, unrelated to this task.

• matmarex added a subtask: T423978: Special:Mediastatistics is being weird and not grouping similar file types together.Apr 21 2026, 2:43 PM

Zabe closed subtask T423654: Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:{query} as Resolved.Apr 21 2026, 4:27 PM

STei-WMF moved this task from In current Tech/News draft to Already announced/Archive on the User-notice board.Apr 23 2026, 7:53 PM

Maintenance_bot removed a project: Patch-For-Review.Apr 23 2026, 8:31 PM

Ladsgroup closed subtask T423978: Special:Mediastatistics is being weird and not grouping similar file types together as Resolved.Apr 24 2026, 12:39 PM

Zabe created subtask T425191: Drop old image tables from wikireplicas.May 2 2026, 11:14 AM

TheDJ mentioned this in T425216: Varnish serving outdated version at original/non-thumb URL of overwritten file upload.May 2 2026, 10:42 PM

Bugreporter mentioned this in T425897: Future of filearchive table.May 11 2026, 1:59 AM

Bugreporter added a parent task: T425897: Future of filearchive table.

JJMC89 mentioned this in T426802: Incomplete imageinfo API response when file is missing.May 20 2026, 2:27 AM

Krinkle mentioned this in T428296: Change "shorturl" in FileRepo to something searchengine-friendly.Jun 5 2026, 6:32 PM

Zabe created subtask T428970: Stop writing to the old file db schema.Jun 11 2026, 9:56 PM

Tgr mentioned this in T429376: Files exist but cannot be embedded with Parsoid.Jun 17 2026, 8:02 AM

TheDJ mentioned this in T429616: PHP Warning: Undefined array key "frameCount".Jun 19 2026, 8:43 AM

Zabe changed the status of subtask T428970: Stop writing to the old file db schema from Open to Stalled.Jun 20 2026, 10:06 AM

AlexisJazz added a subtask: T429720: Files on Betacommons can be moved once, then never again.Jun 24 2026, 2:20 AM

Zabe closed subtask T429720: Files on Betacommons can be moved once, then never again as Resolved.Jun 25 2026, 10:57 PM

TheDJ mentioned this in T430442: Mixed filetypes in filehistories should be restored with the correct filetype.Jun 28 2026, 8:56 AM

Aklapper closed subtask T415330: Add support for stop writing to the old file db schema as Resolved.Jul 23 2026, 5:01 PM

Just mentioning here for visibility - There was a bug in file metadata serialization (T420341) which caused metadata serialization to be incorrect if the serialization method was called twice in the same request. As a result there may be some PDF and DjVu files which have incorrect fr_metadata but correct img_metadata.

Maxxxum69 subscribed.Tue, Sep 15, 2:53 AM

TheDJ added a subtask: T439616: Phantom revisions in filerevision table due to incorrect rollbacks of queries during early WRITE_NEW phase.Tue, Sep 29, 11:40 PM

	Catrope
	Jan 15 2011, 10:41 AM

Migrate file tables to a modern layout (image/oldimage; file/filerevision; add primary keys)Open, MediumPublicActions