The theoretical container and path of these 2 files should be, in theory:

wikipedia-commons-local-public.16
archive/1/16/20240116211741!Алфавітно-предметний_покажчик_за_1938_рік_до_Збірника_постанов_і_розпоряджень_Уряду_Української_Радянської_Соціалістичної_Республіки.pdf

wikipedia-commons-local-public.1e
archive/1/1e/20231203130229!ДАЖО_127-1-68.1897._Геодезичний_опис_ділянки_землі_вічного_чиншовика_Антона_Станіслава_Гарбовських_села_Рудня-Старики_Овруцького_повіту.pdf

Please check by truncating the name or without the /archive prefix and the date! one, if not found, as we don't know at which point of the process those files would be lost.

The first exists:

root@ms-fe1009:~# swift stat wikipedia-commons-local-public.16 'archive/1/16/20240116211741!Алфавітно-предметний_покажчик_за_1938_рік_до_Збірника_постанов_і_розпоряджень_Уряду_Української_Радянської_Соціалістичної_Республіки.pdf'
               Account: AUTH_mw
             Container: wikipedia-commons-local-public.16
                Object: archive/1/16/20240116211741!Алфавітно-предметний_покажчик_за_1938_рік_до_Збірника_постанов_і_розпоряджень_Уряду_Української_Радянської_Соціалістичної_Республіки.pdf
          Content Type: application/pdf
        Content Length: 1330605
         Last Modified: Tue, 16 Jan 2024 21:18:05 GMT
                  ETag: ac929ceaf65d932bf2bfe683643b47de
       Meta Sha1Base36: ja3vvtx04izk863x7mzzwc3wjkptjbn
           X-Timestamp: 1705439884.96510
         Accept-Ranges: bytes
            X-Trans-Id: tx8e6e322995b04547a920c-0065e72bf1
X-Openstack-Request-Id: tx8e6e322995b04547a920c-0065e72bf1

As does the second:

root@ms-fe1009:~# swift stat wikipedia-commons-local-public.1e 'archive/1/1e/20231203130229!ДАЖО_127-1-68.1897._Геодезичний_опис_ділянки_землі_вічного_чиншовика_Антона_Станіслава_Гарбовських_села_Рудня-Старики_Овруцького_повіту.pdf'
               Account: AUTH_mw
             Container: wikipedia-commons-local-public.1e
                Object: archive/1/1e/20231203130229!ДАЖО_127-1-68.1897._Геодезичний_опис_ділянки_землі_вічного_чиншовика_Антона_Станіслава_Гарбовських_села_Рудня-Старики_Овруцького_повіту.pdf
          Content Type: application/pdf
        Content Length: 23751233
         Last Modified: Sat, 09 Dec 2023 03:08:11 GMT
                  ETag: bf7ae1c816785fe887ad2846e13d8e11
       Meta Sha1Base36: a9bue5nc4oj88z3bf65tbh339kjh4un
           X-Timestamp: 1702091290.63527
         Accept-Ranges: bytes
            X-Trans-Id: tx6abde957d45f4e978361f-0065e72d4d
X-Openstack-Request-Id: tx6abde957d45f4e978361f-0065e72d4d

In both cases, these files are present in both ms-eqiad and ms-codfw clusters.

The issue is that the path is stored as varbinary(255) and path length is checked at upload to not exceed that. But then archiving adds archive and a date string to the start of the path, resulting in truncation.

After investigating, it seems the issue goes beyond invalid UTF-8 characters, there is metadata loss due to the pseudo-path stored on the database being silently truncated. When the path is calcualted, the date + an admiration sign is added as prefix, leading to the full path being cut at the end, sometimes, if cut on multi-byte characters, to invalid data (but on any case truncated).

Example:

2016-01-22_08_42_09_View_south_along_the_outer_loop_of_the_Capital_Beltway_(Interstate_495)_at_Exit_50_(U.S._Route_50-Arlington_Boulevard,_To_U.S._Route_29)_along_the_edge_of_West_Falls_Church_and_Merrifield_in_Virginia.jpg                                 | 20161029115225!2016-01-22_08_42_09_View_south_along_the_outer_loop_of_the_Capital_Beltway_(Interstate_495)_at_Exit_50_(U.S._Route_50-Arlington_Boulevard,_To_U.S._Route_29-Lee_Highway,_Fairfax,_Arlington)_along_the_edge_of_West_Falls_Church_and_Merrifield_

Another bug that could have been prevented if:

• Media files were not touched (storage wise) after being uploaded (bad architecture patterns for media storage) T28741
• Strict mode was enabled on the database, leading to hard, rather than silent failures on vital db operations T108255

T28741: Migrate file tables to a modern layout (image/oldimage; file/file_revision; add primary keys) will be picked up next Q so buckle up. For now, can we just move the files? (sorry If I sound stupid, I haven't managed to get my head around this yet). I can ask the community to try it.

Based on the above, I would like to mostly unblock media backups by fixing the UTF8 invalid characters first on production. For that, It would like to just remove the invalid characters on the first 2 files, and then we can maybe discuss how to move forward for the main bug (code fixes, a schema change on oldimage to make oi_archive_name larger?).

Thoughts: @Bawolff @Ladsgroup @Marostegui ?

Names of files don't matter much, I'd say do whatever you want with the existing cases. Only question is that are we sure it can't be re-introduced again?

My suggestion would be, for a short-ish term, to make the field longer (the archival table - oldimage- will be very small) and fill-in the data from the original name, when possible. Although I haven't checked the deleted one, it may suffer from the same issue with deleted, archived files.

A longer term prevention is to do proper checks on code about the size, but that is why I suggested a joint decision, as it may be preferred to do the deeper rearchitecture I sigaled on a previous comment.

I am going, for now, to truncate half a character further the 2 invalid character files, to at least host proper utf8 paths (I cannot add the full path because there isn't enough space yet).

Mentioned in SAL (#wikimedia-operations) [2024-03-05T16:24:04Z] <jynus> patching oldimage table for commons T359176

Looking good, backups unblocked:

root@db1150[commonswiki]> select count(*) FROM oldimage WHERE NOT (oi_archive_name = CONVERT(oi_archive_name USING utf8mb4));
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.098 sec)

jynus opened https://gitlab.wikimedia.org/repos/sre/mediabackups/-/merge_requests/1

sql: Increase the maximum storage path to 300 bytes

So it sounds like the underlying problem, is max file name size is checked at upload but not during file rename (T359294).

This is an heuristic I got by doing:

SELECT oi_name, oi_archive_name
FROM oldimage
WHERE (length(oi_name) >= 240 or length(oi_archive_name) > 240) AND RIGHT(oi_archive_name, LENGTH(oi_name))

Having a length(oi_archive_name) > 240 should be expected in MW. MW tries to keep length(oi_name) below 240, and length(oi_archive_name) below 255 bytes.

It seems like the best solution here would be to simply rename files with illegal names back to legal ones (edit: maybe we have to do something more complex for images with old versions, since if they are cut off then moving might break it further)

Thanks a lot, @Bawolff, this is the kind of take I really needed- as my initial reaction was to just make the field larger.

I have two followup questions-

a) In your opinion, should we try to make the field larger anyway out of precaution (varchars won't use more space if not used) or just abandon it in favor of a deeper rearchitecture in the long term, as it is quite resource intensive to deploy a schema change? In other words, can we be sure this only happens on rename, and not in other non-trivial operations like undelete, etc.

and,

b) as the original file could have been renamed, the list of files may require a more nuisanced query, as I may be missing deleted and later files renamed to shorter names, making the shortening non-trivial. Would it be possible to create a rename maintenance tool that works on illegal/invalid names (as I am guessing the current one will fail to work, if not also on the metadata, at least finding the swift files)? Or should we just try to do it out-of-band?

Meanwhile, I am going to merge and deploy https://gitlab.wikimedia.org/repos/sre/mediabackups/-/merge_requests/1 in any case (workaround for media backups), as backups should be compatible and be able to recover files with illegal names, independently of being a mw bug, as I mention in the review.

jynus closed https://gitlab.wikimedia.org/repos/sre/mediabackups/-/merge_requests/1

sql: Increase the maximum storage path to 300 bytes

jynus reopened https://gitlab.wikimedia.org/repos/sre/mediabackups/-/merge_requests/1

sql: Increase the maximum storage path to 300 bytes

jynus merged https://gitlab.wikimedia.org/repos/sre/mediabackups/-/merge_requests/1

sql: Increase the maximum storage path to 300 bytes

In T359176#9601441, @Ladsgroup wrote:

T28741: Migrate file tables to a modern layout (image/oldimage; file/file_revision; add primary keys) will be picked up next Q so buckle up. For now, can we just move the files? (sorry If I sound stupid, I haven't managed to get my head around this yet). I can ask the community to try it.

holy mother-of-tickets-I-never-thought-would-ever-get-picked-up !

Worth noting that It will be picked up soon but when it will be finished is another matter altogether :P

as I may be missing deleted

I haven't checked, but i don't think deleted files would have this issue because they are stored under their hash.