Dropping a note here after a chat with @Cparle that we'd ideally want to add the proposed new column for perceptual hashes (T121797 / T251026) as part of this work, rather than migrating the tables twice.

This one too T362352

Another things to potentially work on together (similar to perceptual hashes) is T158989: Add img_sha256, related columns with SHA-256 hash of file.

also T119173: RFC: Discourage use of MySQL's ENUM type: media type and mime should not use enum.

An alternative is T96384: Integrate file revisions with description page history and store file metadata as JSON.

Hi @Bugreporter thank you for your comments. I am aware of the tickets you brought up but for most of them, they won't be implemented in this migration.

Note: the proposed new schema tie media type to media file, not revision. It will be awkward if user uploads a new revision of file with same file name (and thus file extension) but different media type, since at that time media type is not updated. For example, videos can be uploaded using .ogg extension, though there are bots to move them in Commons

Change #1111240 had a related patch set uploaded (by Krinkle; author: Krinkle):

[mediawiki/core@master] file: Move filerevision insert within endAtomic() block

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

Change #660422 abandoned by Ladsgroup:

[mediawiki/core@master] Introduce img_id column

Reason:

Not needed

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

In T28741#10443375, @Bugreporter wrote:

Note: the proposed new schema tie media type to media file, not revision. It will be awkward if user uploads a new revision of file with same file name (and thus file extension) but different media type, since at that time media type is not updated. For example, videos can be uploaded using .ogg extension, though there are bots to move them in Commons

This has always been the case though, so I don't see it as a problem, and it is why we have T6421 as well.

I do agree that the new layout may cause a bit of an issue when we need to render thumbnails of an old revision. I think that in that case, we will have to reparse the old file revision, to retrieve its correct actual media type, instead of relying what is the already known type.. And the same might come into play when you revert between two revisions and the file type has changed.

Has anyone looked at how to support thumb nailing for old file revisions, with the new structure ? If I understand the tickets correctly, the earlier concept had the type at the file revision AND the file table, but I'm not entirely sure.

Change #1111240 merged by jenkins-bot:

[mediawiki/core@master] file: Move filerevision insert within endAtomic() block

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

In T28741#10458159, @TheDJ wrote:

In T28741#10443375, @Bugreporter wrote:

Note: the proposed new schema tie media type to media file, not revision. It will be awkward if user uploads a new revision of file with same file name (and thus file extension) but different media type, since at that time media type is not updated. For example, videos can be uploaded using .ogg extension, though there are bots to move them in Commons

This has always been the case though, so I don't see it as a problem, and it is why we have T6421 as well.

I do agree that the new layout may cause a bit of an issue when we need to render thumbnails of an old revision. I think that in that case, we will have to reparse the old file revision, to retrieve its correct actual media type, instead of relying what is the already known type.. And the same might come into play when you revert between two revisions and the file type has changed.

Has anyone looked at how to support thumb nailing for old file revisions, with the new structure ? If I understand the tickets correctly, the earlier concept had the type at the file revision AND the file table, but I'm not entirely sure.

Shouldn't we just simply disallow re-uploading a new file with a different media type than the previous one? e.g. we don't allow for reupload of the exact same image (sha1 check). That can be added there. Of course the problem of old images with this problem remains, but it shouldn't be too hard to sort out.

In T28741#10458304, @Ladsgroup wrote:

Shouldn't we just simply disallow re-uploading a new file with a different media type than the previous one? e.g. we don't allow for reupload of the exact same image (sha1 check). That can be added there. Of course the problem of old images with this problem remains, but it shouldn't be too hard to sort out.

Ideally yes, but sometimes types change. Especially with the videos, where we rely on the information to be present with the first X bytes. When it isn't we fallback to a generic or audio type for instance. Then if you modify the file, to ensure the information is at the front of the file, it becomes a different type.

So yes we can do this, I just don't have a good overview of what kind of impact that would create. And of course we indeed have the historic entries.

In T28741#10458681, @TheDJ wrote:

In T28741#10458304, @Ladsgroup wrote:

Shouldn't we just simply disallow re-uploading a new file with a different media type than the previous one? e.g. we don't allow for reupload of the exact same image (sha1 check). That can be added there. Of course the problem of old images with this problem remains, but it shouldn't be too hard to sort out.

Ideally yes, but sometimes types change. Especially with the videos, where we rely on the information to be present with the first X bytes. When it isn't we fallback to a generic or audio type for instance. Then if you modify the file, to ensure the information is at the front of the file, it becomes a different type.

So yes we can do this, I just don't have a good overview of what kind of impact that would create. And of course we indeed have the historic entries.

I've looked at some numbers, it seems only 0.05% of the files have more than one triplet of oi_media_type, oi_major_mime, oi_minor_mime in their history. And most of them shouldn't break the thumbnailing at all. So I'm inclined not to attend to the problem immediately.

Personal checklist of stuff to do:

• Get the migration script patch merged
• Run it in beta cluster and make sure it works as expected
• Write tests for write both in LocalFile and get it merged
• Deploy the file tables into production
• start write both in testwikis and a couple small ones
• Communicate to the communities that these changes are coming
• Run the migration script on the small and test wikis
• Enable write both more everywhere
• Start the migration script everywhere
• Start building support for read new
• switch beta cluster to read new
• switch small wikis to read new
• switch it everywhere
• add support for stopping to the old tables
• flip the switch
• drop the old tables
• drop the old code and migration config

(between each step there will be bugs discovered and addressed)

Note after this we may want to introduce a FileRevision class to replace the OldLocalFile class.

@Ladsgroup do you have a timeline for this migration?

@mforns any conflicts in this migration for Commons-Impact-Metrics?

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.

Does this mean that img table dump will change after migration to new schema automatically?

Yes!

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.

@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.)