d7_file_plain derived incorrectly when file_managed contains rows without mimetype

Problem/Motivation

Document media items                                            needs-review      299       62      299   100%      20%       255         18              237      idle      
      d7_file_plain::public                                                         254       62      254   100%      24%                                                      
      d7_file_plain:application:public                                               40        0       40   100%       0%                                                      
      d7_file_plain:text:public                                                       5        0        5   100%       0%                                                      
  Image media items                                               needs-review      192      192      192   100%     100%         1          1                0      idle      
      d7_file_plain:image:public                                                    192      192      192   100%     100%                                                      
  Video file media items                                          needs-review       14        0       14   100%       0%        14          0               14      idle      
      d7_file_plain:video:public                                                     14        0       14   100%       0%                                                      

- 40 in d7_file_plain:application:public
- 5 in d7_file_plain:text:public
- 192 in d7_file_plain:image:public
- 14 in d7_file_plain:video:public
together that's 251

But then there's also the mysterious d7_file_plain::public, with 254 rows.

But my file_managed D7 table contains only 271 rows in total! The D9 one contains 257 rows.

Turns out that I've got 17 rows in my D7 DB like this:

INSERT INTO `file_managed` (`fid`, `uid`, `filename`, `uri`, `filemime`, `filesize`, `status`, `timestamp`)
VALUES
	(145, 1, 'devel_themer_16528709924e70cb82a97d8', X'74656D706F726172793A2F2F646576656C5F7468656D65725F3136353238373039393234653730636238326139376438', 'application/octet-stream', 1050086, 1, 1316014977);

(Found these using SELECT * FROM `wimleers-d9`.file_managed RIGHT OUTER JOIN wimleers.file_managed ON `wimleers-d9`.file_managed.fid = wimleers.file_managed.fid;.)

Turns out I also have 3 files from the Drupal 5 era without a value for the managed_file.mimetype column. That explains why only 251 are found across the 4 d7_file_plain derivatives. Hence these 3 were not mapped to Media items!

271 - 17 + 3 = 254 👍

Finally, the Drupal 9 media module installs 3 media icons. So: 254 +3 = 257. 👍

Steps to reproduce

Turns out the solution was fairly simple: I needed to clean up my managed_files table:

1. all rows MUST have a mimetype (I added text/x-diff to mine — yes, these were Drupal 5/6 patches 🤣)
2. drush cr
3. d7_file_plain::public disappeared! 🥳

Proposed resolution

Auto-detect the presence of rows without a mimetype and complain loudly, before even doing anything!

Remaining tasks

API changes