This RFC is tentatively scheduled for our ArchCom IRC meeting next week: E228: ArchCom RFC Meeting W28: image and oldimage tables (2016-07-13, #wikimedia-office)

These are 2 ancient rules regarding databases (that I have just made up) regarding database design:

• Do no delete rows
• Do not move rows between tables

Speciall, INSERT... SELECT is bad (either requires a lot of locking or it is insecure, we are on the second case for performance reasons), and MySQL is already giving a warning every time revisions are archived and unarchived.

Given that the parent task says: "This structure resembles cur and old, with all the nastiness that comes with it." I probably do not need to convince anybody of how badly that is. But if I had to, just have a look at bugs such as: T139346 and T135851#2312924

My two cents on integrating file history with page history (T96384), using Multi-Content-Revisions (T107595):

1. when a new version of a file is uploaded, a new revision of the file description page is created, in which the "file" slot is updated (optionally, the "main" slot with the wikitext would also be updated).
2. The "file" slot has a content model called "uploaded-media" (or just "file"), which consists of structured meta-data (json?) about the uploaded file.
3. The most important information is the internal identifier of the file, which can be used to find this version of the file both on disk and via HTTP. Additional information about the file should probably be included, like size, mime type, hash (enabled migration to content addressable storage!), and maybe even extracted meta-data (exif).
4. this requires internal and external identifiers that remain stable. 'Vulpes_vulpes_standing_in_snow.jpg' is not sufficient, it would have to be something like '20040912170154!Vulpes_vulpes_standing_in_snow.jpg' from the start.
5. We could achieve this by allowing such "archive IDs" to work also on the current version. So '20040912170158!Vulpes_vulpes_standing_in_snow.jpg' should currently be an alias for the plain 'Vulpes_vulpes_standing_in_snow.jpg' (or the other way around).
6. For display, ImagePage will have to know about the "file" slot. Some API modules will probably also have to be aware of this.

I think all the information that is currently in the image table can be managed as "slot content", but we should keep the image table as a secondary table for quick queries by size or mime type. The image table would become a "derived" table like the link tables, that gets updated when content (of the file slot) gets saved.

The oldimage table however can be dropped, after creating "fake" revisions for each upload, with the necessary information in the file slot. The same is possible for the filearchive table, with the difference that the "fake" revisions would be created in the archive table.

The result would be:

• oldimage and filearchive table can be removed
• full integration of file uploads with the page history, including patrolling, reverts, undeletion, etc
• image table is still available for queries
• file metadata would become available in XML dumps.

In T589#2453326, @daniel wrote:

My two cents on integrating file history with page history (T96384), using Multi-Content-Revisions (T107595):

1. when a new version of a file is uploaded, a new revision of the file description page is created, in which the "file" slot is updated (optionally, the "main" slot with the wikitext would also be updated).
2. The "file" slot has a content model called "uploaded-media" (or just "file"), which consists of structured meta-data (json?) about the uploaded file.

+1

3. The most important information is the internal identifier of the file, which can be used to find this version of the file both on disk and via HTTP. Additional information about the file should probably be included, like size, mime type, hash (enabled migration to content addressable storage!), and maybe even extracted meta-data (exif).

Potentially some overlap with derived data (derived from the file), but consistent storage is of course nice.

4. this requires internal and external identifiers that remain stable. 'Vulpes_vulpes_standing_in_snow.jpg' is not sufficient, it would have to be something like '20040912170154!Vulpes_vulpes_standing_in_snow.jpg' from the start.
5. We could achieve this by allowing such "archive IDs" to work also on the current version. So '20040912170158!Vulpes_vulpes_standing_in_snow.jpg' should currently be an alias for the plain 'Vulpes_vulpes_standing_in_snow.jpg' (or the other way around).

Ah, this comes back to needing permanent storage ids; I'm not sure I like the aliasing idea though it may work more simply than forcing an immediate migration...

6. For display, ImagePage will have to know about the "file" slot. Some API modules will probably also have to be aware of this.

I think all the information that is currently in the image table can be managed as "slot content", but we should keep the image table as a secondary table for quick queries by size or mime type. The image table would become a "derived" table like the link tables, that gets updated when content (of the file slot) gets saved.

Sounds sensible... image table is mostly used for metadata lookups, but does get used for listings. I think mime type searches could be done more effectively by tagging the search index on the pages than anything we do with the image table (unless you're just pulling a list of all file of a certain type, which is probably rare).

The oldimage table however can be dropped, after creating "fake" revisions for each upload, with the necessary information in the file slot. The same is possible for the filearchive table, with the difference that the "fake" revisions would be created in the archive table.

These'll need to either stay for old items, or stay long enough for a migration.

The result would be:

• oldimage and filearchive table can be removed
• full integration of file uploads with the page history, including patrolling, reverts, undeletion, etc
• image table is still available for queries
• file metadata would become available in XML dumps.

Whee! :)

The RFC could use a more detailed problem statement, which would help to establish a scope. For example, to me it is not entirely clear whether the issues and solutions described in T66214: Define an official thumb API are in scope or not, or how those two proposals would interact.

Was handling of image revisions via multi-content revisions discussed during yesterday's the IRC session?

In T589#2459544, @GWicke wrote:

The RFC could use a more detailed problem statement, which would help to establish a scope. For example, to me it is not entirely clear whether the issues and solutions described in T66214: Define an official thumb API are in scope or not.

The design of the query API for imageinfo and thumbnails, and the url pattern for thumbnails itself is orthogonal to this RFC. It shouldn't matter to this RFC whether those interfaces use file names (that resolve to the current version of a mutable/versioned file), or refer to a specific version directly (using the filename/timestamp or content hash). Either public interface can be made on top of the current or here-proposed schema. In fact, even the implementation of T66214 shouldn't be affected by the schema changes. The implementation details that change in this RFC are mostly hidden behind abstract methods.

The only difference is that this RFC would introduce a new "file revision ID" concept that we may or may not want to support as parameter in the imageinfo query API.

File hashes are already stored and queryable. T66214 may need to add an index or link table for the content hashes, but that's an unrelated schema change.

I also assume that T66214 has no intention of changing the fact that we use canonical references inside wikitext/html (e.g, not embedding an older file directly). The expansion to refer to content hashes and/or versioned thumbnail urls would apply to the parser (and would need to purge pages when files change). If we want to support embedding old versions directly in wikitext, that would be a separate change also.

Summary of yesterday's IRC discussion of this RFC follows. (Full chat log at P3425.)

• "Problems" section added to https://www.mediawiki.org/wiki/Requests_for_comment/image_and_oldimage_tables.
  1. File revisions should have better unique identifiers than "current file title + timestamp".
  2. Uploading file revisions must not involve rows moving the across database tables.

• This RFC would provide stable file IDs. While they are not intended to become public identifiers, T66214 might want to use them. It would allow thumbnails (addressed by content hash) to be associated with a file ID rather than a file name. This would allow a thumbnail storage to have more stable and hierarchical structure. Making purges easier. Especially when users rename a file.

• Having a primary key will make future schema changes easier.

• Merely adding a primary key to the current schema would not solve Problem 2. As such, Proposal 1 was rejected.

• Several different schema migration strategies were discussed afterward in #wikimedia-tech. Ideas from @tstarling and @jcrespo are now summarised at https://www.mediawiki.org/wiki/Requests_for_comment/image_and_oldimage_tables#Migration_strategy.

Update and call for discussion on wikitech-l:

https://lists.wikimedia.org/pipermail/wikitech-l/2016-August/086283.html
https://www.mail-archive.com/wikitech-l@lists.wikimedia.org/msg85607.html

Open questions

1. Which fields do we keep in the 'image' table (img_id, img_name, img_latest, anything else?).

All fields currently being queried from both tables, will probably only
stay in the image revision table. But there are a few fields that we
intentionally only want to query about current versions. For example
'img_sha1'. For duplicate-detection, we need to only consider the latest
revisions. Doing this by keeping img_sha1 means uploading a new revision
will involve updating two fields instead of one (img_latest and img_sha1).
This isn't unprecedented as we do this for page as well
(WikiPage::updateRevisionOn; page_latest, page_touched, page_is_redirect,
page_len).

Are there other fields we need to keep besides img_sha1? Or should we can
solve the img_sha1 use case in a different manner?

2. img_metadata

This field is a blob of serialised PHP (typically representing the Exif
data of an image).

Tim (correct me if I got it wrong) mentioned we could potentially make
migration easier by changing img_metadata to be stored in a separate table
and change the img_metadata field (in the image revision table) to instead
be a pointer to a primary key.

This could potentially be done separately later, but if it helps migration,
we should consider doing it now.

How will this interact with file deletion? Will it be difficult to garbage
collect this? Do we need to? (We don't seem to do it for the 'text' table /
external store; is it worth moving this an external store?)

3. Migration

If we rename both tables (image/oldimage -> file/filerevision), we'd have
the ability to run migration in the background without interfering with the
live site, and without requiring a long read-only period and/or duplicate
and additional code complexity to be developed.

Is there a way we can do the migration without creating two new tables?
Using the oldimage table as import destination for current rows isn't
straight forward as existing scan queries would need to skip the current
rows somehow while in the midst of this migration. Seems possible, but is
it worth the complexity? (We'd need extra code that knows about that
migration field, and how long do we keep that code? Also complicates
migration for third-parties using update.php).

Is creating the new tables separately viable for the scale of Wikimedia
Commons? (and dropping the old ones once finished). Is this a concern from
a DBA perspective with regards to storage space? (We'd temporarily need
about twice the space for these tables). So far I understood that it
wouldn't be a problem per se, but that there are also other options we can
explore for Wikimedia. For example we could use a separate set of slaves
and alter those while depooled (essentially using entirely separate set of
db slaves instead of a separate table within each slave).

Do we create the new table(s) separately and switch over once it's caught
up? This would require doing multiple passes as we depool slaves one by one
(we've done that before at Wikimedia). Switch-over could be done by
migrating before the software upgrade, with a very short read-only period
after the last pass is finished. It wouldn't require maintaining multiple
code paths, which is attractive.

Other ideas?

TechCom suggests this RFC for discussion at E266: ArchCom RFC Meeting W35: image and oldimage tables (2016-08-31, #wikimedia-office). Hopefully, we can discuss most of the thorny issues in the comments here, and then clarify at the IRC meeting. In particular, @jcrespo and @Krinkle, assuming timezones work for you, please have informal IRC conversations about this. It'll be especially appreciated if you drop the logs of those conversations in this task (or even just a pointer to the logs of the discussion venue you used).

Since @jcrespo commented on wikitech-l, I'm going to quote his August 11 email:

Mediawiki migration could be done in any form or way; the easiest path
for a live migration is to keep the image table; add the logic to ignore
older revisions on the same table; add the oldimage entries, which are
very few in comparison; then create the non-revision version; migrate
the bulk of the code functionality; then drop unnecesary columns. That
was discussed at the time, although I am not sure it was all recorded.

Duplicating commons infrastructure is not viable with the hardware we
have now.

Modifying the image table is not "free", specially for Commons, but having
the image,oldimage pattern is already causing issues to our editors.

Summary of second IRC meeting about image/oldimage migration (E266).

(For the summary of the first RFC meeting, see T589#2463446.)

This second meeting deals with the remaining three open questions at T589#2541630.

• Which solution to pursue?
  • Agreement to shelve the idea to manage upload history using MCR (Multi-content revisions). Only discussing the two-table file / file_revision option for now.
• Question 1: What fields to keep unversioned in the image table?
  • Minimal set: img_id, img_name, img_latest.
  • Currently indexed: img_timestamp, img_user_text, img_sha1, img_media_type, img_major_mime, img_minor_mime.
  • img_user_text: Remove. For upload contributions, replaced by a filerevision index.
  • img_sha1: Keep. For duplicate detection, applies to latest only.
  • img_timestamp: Remove. Mostly used on oldimage, not image. image usage for pagination replaced by ids.
  • img_media_type/img_major_mime/img_minor_mime: TBD. Preference is to remove, but currently used by <Special:MIMESearch>. Could be implemented on top of SearchEngine instead. Depending on short-term feasibility, we may want to keep for now and remove later.
• Question 2: What about img_metadata?
  • Orthogonal. This RFC will move it from image to filerevision. For later: Consider replacing img_metadata blobs with references to external storage.
  • See also T32906: Store DjVu, PDF extracted text in a structured table instead of img_metadata, T99263: Store Pdf extracted text in a structured table instead of img_metadata.
• Question 3: Schema migration
  • @jcrespo and @tstarling support using a custom table view.
  • "rename image to filerevision, re-create image as a view that filters filerevision"
  • This leaves a few details to be discussed, but we can do that in code-review closer to implementation.
  • See also: https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

@jcrespo and @tstarling support using a custom table view.

In my case, more like "let's get rid of old references, but in case of necessity, we can use views as a last resort aiming for backwards compatibility, despite introducing potential challenges (they are updatable as long as they have a 1:1 relationship with the underlying table)"

In T589#2709460, @jcrespo wrote:

@jcrespo and @tstarling support using a custom table view.

In my case, more like "let's get rid of old references, but in case of necessity, we can use views as a last resort aiming for backwards compatibility, despite introducing potential challenges (they are updatable as long as they have a 1:1 relationship with the underlying table)"

I think we may be misunderstanding each other. As far as I'm concerned we won't have any state during which we interact with both the new and old tables (other than the seconds during which the scap operation runs).

We typically only support 1 database version in MediaWiki. We'll prep the breaking change in the master branch. Any extensions that interact with the table directly, will have to update their code within the same 6-month release cycle. We'll update Wikimedia-deployed extensions as soon as possible (sometime before the next branch cut) so that we can continue to deploy from master once a week.

The migration we're talking about here is not the period during which developers update their code. From a cluster perspective, that period will not exist. This migration is about running the schema change in a way that we can keep the existing code as-is, and once the schema change is done (which we expect will take several days), we'll switch to the new code at once.

This means, during the migration, the new tables is unused as all read/write traffic goes to the old table. Once the schema change is done, we deploy the next version of MediaWiki.

This is just one way of doing it, there are other ways and that's why we're discussing it here :)

@jcrespo Is using a view suitable for this particular way of migrating? Or should we do that in a way that doesn't involve custom view? Or perhaps we can utitlize the custom view if we migrate in a different way?

In T589#2746546, @Krinkle wrote:

The migration we're talking about here is not the period during which developers update their code. From a cluster perspective, that period will not exist. This migration is about running the schema change in a way that we can keep the existing code as-is, and once the schema change is done (which we expect will take several days), we'll switch to the new code at once.

Yes, that is my understanding also.

This means, during the migration, the new tables is unused as all read/write traffic goes to the old table. Once the schema change is done, we deploy the next version of MediaWiki.

I was imagining a scheme like the following:

• Rename image to filerevision
• Create a view called image which reads from the underlying filerevision
• Add new fields to filerevision
• Create the file table
• Disable image uploads
• Run a script which populates file from filerevision and updates filerevision with suitable links to file
• Copy all rows from oldimage to filerevision (but the image view somehow filters out these copied rows)
• Deploy the new MW version; start using file/filerevision
• Re-enable image uploads
• Drop table oldimage
• Drop view image

The view would be only useful to maintain compatibility regarding extension, I would be almost certain they would not work for the migration process itself I do not see how a view will be useful in this case (views have trouble with indexes, because they don't have them, only the underlying table has them):

In T589#2747454, @tstarling wrote:

• Rename image to filerevision
• Create a view called image which reads from the underlying filerevision
• Add new fields to filerevision
• Create the file table
• Disable image uploads
• Run a script which populates file from filerevision and updates filerevision with suitable links to file
• Copy all rows from oldimage to filerevision (but the image view somehow filters out these copied rows)
• Deploy the new MW version; start using file/filerevision
• Re-enable image uploads
• Drop table oldimage
• Drop view image

Something like:

• Write some sanity check scripts for current and old structure "is this consistent?"
• Make sure mediawiki that ignores extra columns on image
• Add new fields to image
• Create empty file table
• Deploy new version of mediawiki that starts maintaining file and image table (new cols, rows) for new events
• Copy all rows from oldimage to image
• Run a script which populates file from image
• Run sanity checks
• Deploy the new MW version using the new structure at the same time that we rename image to filerevision
• Drop table oldimage
• Drop old unused fields

I know this requires an extra deployment, but I am not sincerely sure a view will work in the above case (still doing the right query plans), but if you do the work checking it, and prefer avoid deploying an intermediate software stage, compatible with the previous, but using the new one, I can help testing it. Remember I thought you wanted views for extension compatibility, that could work- I am not sure for core functionality, I prefer the extra deployment.

I also do not like disabling uploads for an extended time, instead of creating backwards compatibility and then backfill, which is more or less my strategy. Some devs here have done things like this in the past, at a smaller scale, I think, maybe we can ask for tips.

The other issue is that there is too much copying on the original proposal- that is challenging for many reasons- copying rows in such a huge table will be either slow, or cause lag, or both (performance team would not be happy).

It is not that I do not want to do much DBA work, it is that it creates IO stress, which normally the users notice. I do copies, but of very small tables (oldimage), and try to disrupt users as little as possible, plus renames should be almost instant. In exchange, there is an extra release, but that is half of the final code, it is not like a complete new version has to be created. 0:-)

I am probably not right here, but I would like you to at least consider such an alternative model, and have into account the potential implications of yours.

In T589#2690728, @Krinkle wrote:

• img_media_type/img_major_mime/img_minor_mime: TBD. Preference is to remove, but currently used by <Special:MIMESearch>. Could be implemented on top of SearchEngine instead. Depending on short-term feasibility, we may want to keep for now and remove later.

In T589#2801172, @Krinkle wrote:

In T589#2690728, @Krinkle wrote:

• img_media_type/img_major_mime/img_minor_mime: TBD. Preference is to remove, but currently used by <Special:MIMESearch>. Could be implemented on top of SearchEngine instead. Depending on short-term feasibility, we may want to keep for now and remove later.

Perhaps we should kill img_media_type (that's pretty useless), but having the mime in the db has proven useful in the past where we had to debug things (e.g. only affects this type of image with this type of metadata)

Its also used by Special:MediaTypeStatistics

I've updated https://www.mediawiki.org/wiki/Requests_for_comment/image_and_oldimage_tables to reflect the latest state of this proposal based on the second IRC conversation (October 2016) and this weeks' meeting of the Architecture Committee.

The only remaining unresolved question was around whether to keep or remove the media type and mime time columns. Our current recommendation is to keep them (due to existing usage) and treat it as an orthogonal issue for later. We recognise the overhead of continued maintenance for this index, but those are not in scope of this RFCs problem statement and isn't a requirement to fulfilling the presented use cases.

In T589#2801188, @Bawolff wrote:

Perhaps we should kill img_media_type (that's pretty useless)

Since it's now exposed via Cirrus, it allows me to search for "video". That's pretty nice. We don't need it in the table really, but the search engine has to get it from *somewhere*. it can be computed from the file data itself, but not in all cases from meta-data (ogg files need to be inspected to find out if they are video, for instance). Since we currently have no other place to store this info, it should probably stay in the table for now.

In T589#2951491, @daniel wrote:

In T589#2801188, @Bawolff wrote:

Perhaps we should kill img_media_type (that's pretty useless)

Since it's now exposed via Cirrus, it allows me to search for "video". That's pretty nice. We don't need it in the table really, but the search engine has to get it from *somewhere*. it can be computed from the file data itself, but not in all cases from meta-data (ogg files need to be inspected to find out if they are video, for instance). Since we currently have no other place to store this info, it should probably stay in the table for now.

Its not calculated correctly as it stands (webm file with only audio tracks are videos, ogg files with metadata mentioning the word theora in metadata are videos even if they are not, etc)

Since the last call email went out: Do we have an estimate about how muchtime images are going to be read only during the migration on commons? If we are talking several hours im sure people wont mind. If we are talking several days the commons community will be seriously unhappy

In T589#2459544, @GWicke wrote:

The RFC could use a more detailed problem statement, which would help to establish a scope.

https://www.mediawiki.org/wiki/Requests_for_comment/image_and_oldimage_tables#Problems is helpful, but I'll note that it's been many months since I last read an RfC presenting such strong use cases:

In T589#2453326, @daniel wrote:

The result would be:

• oldimage and filearchive table can be removed
• full integration of file uploads with the page history, including patrolling, reverts, undeletion, etc
• image table is still available for queries
• file metadata would become available in XML dumps.

Thanks for making it clear what the benefits would be for users.

In T589#2440645, @jcrespo wrote:

These are 2 ancient rules regarding databases (that I have just made up) regarding database design:

:-)