Create maintenance script to import all existing images to mediamoderation_scan table
Closed, ResolvedPublic3 Estimated Story Points
Actions

Assigned To

Authored By

	Dreamy_Jazz
	Nov 9 2023, 12:27 PM

Description

A maintenance script should be added that allows the importing of images from the image, oldimage and filearchive tables into the mediamoderation_scan table.

The table will contain the SHA-1 values of the images from these tables and null on all other row values.

This maintenance script should be run when update.php is run. However, this should only be done once T350323: Write an empty row to scan table on file upload is complete so that files uploaded after the maintenance script is run are added to the table without needing to run this script again.

Acceptance criteria

Create this maintenance script
Adequately phpunit test this script to ensure reliability

Details

Subject	Repo	Branch	Lines +/-
Add maintenance script to import existing files to scan table	mediawiki/extensions/MediaModeration	wmf/1.42.0-wmf.7	+1 K -0
Add importExistingFilesToScanTable.php to update.php	mediawiki/extensions/MediaModeration	master	+2 -0
On force make ImportExistingFilesToScanTable skip updatelog output	mediawiki/extensions/MediaModeration	master	+6 -0
Add maintenance script to import existing files to scan table	mediawiki/extensions/MediaModeration	master	+1 K -0
Change methods to accept ArchivedFile object as well as File	mediawiki/extensions/MediaModeration	master	+215 -32

Customize query in gerrit

Related Objects
Search...

Status	Subtype	Assigned	Task
Open		None	T351200 [EPIC] Start automated scanning
Open		None	T351400 Run the maintenance script scanning images in mediamoderation_scan on WMF wikis
Resolved		kostajh	T351544 [Sub EPIC] Start test wiki scan
Resolved		None	T351546 [Sub EPIC] Set up rows
Resolved		Tchanders	T350865 Import all existing images to the mediamoderation_scan table on WMF wikis
Resolved		Dreamy_Jazz	T350863 Create maintenance script to import all existing images to mediamoderation_scan table
Resolved		Dreamy_Jazz	T350323 Write an empty row to scan table on file upload
Resolved		Dreamy_Jazz	T350864 [M] Create a service to manage rows in the mediamoderation_scan table
Resolved	BUG REPORT	Dreamy_Jazz	T352234 MediaModerationFileProcessor::canScanFile incorrectly registers audio and video files as scannable when TimedMediaHandler extension is installed
Resolved		Dreamy_Jazz	T351477 [S] Explore how many rows in the file tables have duplicated timestamp values on WMF wikis
Open		None	T353442 Update MediaModerationPhotoDNAServiceProvider::getThumbnailForFile to choose a thumbnail that meets minimum height requirements for PhotoDNA API
Resolved	BUG REPORT	Dreamy_Jazz	T353758 MediaModerationPhotoDNAServiceProvider provides false to FileBackend::getFileContents causing a PHP Notice
Resolved		Dreamy_Jazz	T351399 Create a maintenance script to automatically scan files listed in mediamoderation_scan
Resolved		kostajh	T351401 Create service(s) to send an image to PhotoDNA for a scan
Resolved		Dreamy_Jazz	T351407 Create a service that sends an email when a file matches
Resolved		Dreamy_Jazz	T351417 Create a service that creates LocalFile and ArchivedFile objects for files that have a given SHA-1
Resolved		Dreamy_Jazz	T351416 [S] Explore how many rows in the file tables have duplicated SHA-1 values on WMF wikis
Resolved		Dreamy_Jazz	T351409 Expand the database lookup service to get rows from the mediamoderation_scan table
Resolved		Dreamy_Jazz	T351112 [S] Create an initial service to lookup scan rows from the database
Resolved		Dreamy_Jazz	T350321 [M] Create database table to store status of scans

Event Timeline

Dreamy_Jazz created this task.Nov 9 2023, 12:27 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 9 2023, 12:27 PM

Dreamy_Jazz added a project: Trust and Safety Product Team.Nov 9 2023, 12:27 PM

Dreamy_Jazz mentioned this in T350864: [M] Create a service to manage rows in the mediamoderation_scan table.Nov 9 2023, 12:33 PM

Dreamy_Jazz added a subtask: T350864: [M] Create a service to manage rows in the mediamoderation_scan table.

Dreamy_Jazz mentioned this in T350865: Import all existing images to the mediamoderation_scan table on WMF wikis.Nov 9 2023, 12:35 PM

Dreamy_Jazz added a parent task: T350865: Import all existing images to the mediamoderation_scan table on WMF wikis.

Madalina added a project: Trust and Safety Product Sprint.Nov 14 2023, 12:28 PM

Dreamy_Jazz closed subtask T350864: [M] Create a service to manage rows in the mediamoderation_scan table as Resolved.Nov 15 2023, 12:22 PM

Dreamy_Jazz updated the task description. (Show Details)Nov 15 2023, 5:38 PM

Dreamy_Jazz added a subtask: T350323: Write an empty row to scan table on file upload.

Dreamy_Jazz updated the task description. (Show Details)

Dreamy_Jazz updated the task description. (Show Details)Nov 15 2023, 5:40 PM

Change 974687 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/MediaModeration@master] [WIP] Add maintenance script to import existing files to scan table

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

gerritbot added a project: Patch-For-Review.Nov 15 2023, 9:11 PM

Dreamy_Jazz renamed this task from Create maintenance script to import all existing images to mediamoderation_scan table to [M] Create maintenance script to import all existing images to mediamoderation_scan table.Nov 15 2023, 9:11 PM

Dreamy_Jazz edited projects, added Trust and Safety Product Sprint (Sprint Bodhrán); removed Trust and Safety Product Sprint.

Dreamy_Jazz moved this task from Priority Backlog to In Progress on the Trust and Safety Product Sprint (Sprint Bodhrán) board.

Change 974686 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/MediaModeration@master] [WIP] Change methods to accept ArchivedFile object as well as File

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

Dreamy_Jazz added a parent task: T351399: Create a maintenance script to automatically scan files listed in mediamoderation_scan.Nov 16 2023, 1:20 PM

Dreamy_Jazz added a subtask: T351416: [S] Explore how many rows in the file tables have duplicated SHA-1 values on WMF wikis.Nov 16 2023, 11:06 PM

Dreamy_Jazz added a subtask: T351477: [S] Explore how many rows in the file tables have duplicated timestamp values on WMF wikis.Nov 16 2023, 11:12 PM

Dreamy_Jazz claimed this task.Nov 16 2023, 11:32 PM

Dreamy_Jazz closed subtask T351477: [S] Explore how many rows in the file tables have duplicated timestamp values on WMF wikis as Resolved.

Dreamy_Jazz added a parent task: T351546: [Sub EPIC] Set up rows .Nov 20 2023, 10:47 AM

Dreamy_Jazz removed a parent task: T351399: Create a maintenance script to automatically scan files listed in mediamoderation_scan.Nov 20 2023, 10:52 AM

Dreamy_Jazz edited projects, added Trust and Safety Product Sprint (Sprint Shamisen (20th Nov - 1st Dec. 2023)); removed Trust and Safety Product Sprint (Sprint Bodhrán).Nov 20 2023, 12:02 PM

Dreamy_Jazz renamed this task from [M] Create maintenance script to import all existing images to mediamoderation_scan table to Create maintenance script to import all existing images to mediamoderation_scan table.Nov 20 2023, 4:19 PM

Dreamy_Jazz set the point value for this task to 2.

Dreamy_Jazz moved this task from Priority Backlog to In Progress on the Trust and Safety Product Sprint (Sprint Shamisen (20th Nov - 1st Dec. 2023)) board.

Change 974686 merged by jenkins-bot:

[mediawiki/extensions/MediaModeration@master] Change methods to accept ArchivedFile object as well as File

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

ReleaseTaggerBot added a project: MW-1.42-notes (1.42.0-wmf.7; 2023-11-28).Nov 21 2023, 1:00 PM

Dreamy_Jazz changed the point value for this task from 2 to 3.Nov 22 2023, 5:53 PM

Change 977215 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/MediaModeration@master] Add importExistingFilesToScanTable.php to update.php

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

Dreamy_Jazz moved this task from In Progress to Needs review on the Trust and Safety Product Sprint (Sprint Shamisen (20th Nov - 1st Dec. 2023)) board.Nov 24 2023, 3:11 PM

Change 974687 merged by jenkins-bot:

[mediawiki/extensions/MediaModeration@master] Add maintenance script to import existing files to scan table

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

Dreamy_Jazz updated the task description. (Show Details)Nov 30 2023, 5:51 PM

I will write suggested QA steps for betawikis by tommorrow. QA should wait until QA on T350323 an T352234 is completed.

Suggested QA steps for betawikis:

Make sure you have access to betawiki DBs, and if not get access
Go to https://meta.wikimedia.beta.wmflabs.org/wiki/Special:SiteMatrix and choose a wikipedia from the list
Go to that betawiki and load Special:ListFiles. Check that images appear in that list (ignore audio/video). If not, repeat step 2 to choose a different beta wikipedia.
Connect to the betawiki over ssh (e.g. ssh deployment-deploy03.deployment-prep.eqiad1.wikimedia.cloud)
Open the DB for your chosen wikipedia (e.g. sql dewiki)
Run the following SQL and keep a note of the output:

SELECT COUNT(*) FROM mediamoderation_scan;

Run the maintenance script. This can be done via mwscript extensions/MediaModeration/maintenance/importExistingFilesToScanTable.php --wiki=dewiki replacing dewiki with the name of the wiki you chose
Make sure the maintenance script runs without any errors and wait until it completes fully.
Repeat steps 5 and 6.
Verify that the second time you ran the query a larger count was present

You can repeat the steps for the same wiki, but the count will not increase when you run the query for the second time and you would need to add --force to the end of the command in step 7.

You can also specify the options --start-timestamp, --batch-size, and --table. You may wish to repeat the steps with these specified. In more detail:

--start-timestamp allows you to import images that were uploaded at or after a given timestamp. The timestamp is provided in the format YYYYMMDDHHMMSS. If this is specified, the script will not be marked as complete. You would add this to the end of the command in step 7. For example, it could be --start-timestamp 20230504030201
--table allows you to specify the database table(s) to import images from. This can be specified multiple times and the valid values are image, oldimage, and filearchive. You could add this to the end of the command in step 7. If specified or not specifying all three options, the maintenance script will not be marked as complete. For example, this could be --table image --table oldimage
--batch-size allows you to control the number of files to import per batch. This takes an integer number and this would be added to the end of the command in step 7. For example, this could be --batch-size 40

Change 979167 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/MediaModeration@master] On force make ImportExistingFilesToScanTable skip updatelog output

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

Change 979167 merged by jenkins-bot:

[mediawiki/extensions/MediaModeration@master] On force make ImportExistingFilesToScanTable skip updatelog output

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

ReleaseTaggerBot edited projects, added MW-1.42-notes (1.42.0-wmf.9; 2023-12-12); removed MW-1.42-notes (1.42.0-wmf.7; 2023-11-28).Nov 30 2023, 10:00 PM

Dreamy_Jazz closed subtask T350323: Write an empty row to scan table on file upload as Resolved.Dec 1 2023, 10:35 AM

I have verified that the new code has been implemented and is functioning and displaying as expected... Thank you for the QA Steps @Dreamy_Jazz

Count of images before running the maintenance script on zhwiki:

Count of images after running the maintenance script on zhwiki:

Count of images before running the maintenance script on viwiki:

Count of images after running the maintenance script on viwiki using the --start-timestamp:

Count of images before running the maintenance script on fawiki:

Count of images after running the maintenance script on fawiki using the --table filearchive:

Count of images before after running the maintenance script on fawiki using the --table archive:

Count of images after running the maintenance script on fawiki using the --table image:

Count of images before running the maintenance script on dewiki:

Count of images after running the maintenance script on dewiki using the --table oldimage:

Count of images after running the maintenance script on dewiki using the --table image:

Count of images after running the maintenance script on dewiki using the --table filearchive:

Count of images before running the maintenance script on enwiki:

Count of images after running the maintenance script on enwiki:

Dreamy_Jazz closed this task as Resolved.Dec 4 2023, 4:44 PM

Dreamy_Jazz reopened this task as Open.

Change 977215 merged by jenkins-bot:

[mediawiki/extensions/MediaModeration@master] Add importExistingFilesToScanTable.php to update.php

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

The last change (https://gerrit.wikimedia.org/r/c/mediawiki/extensions/MediaModeration/+/977215/) shouldn't need QA, so this can be marked as resolved.

Maintenance_bot removed a project: Patch-For-Review.Dec 4 2023, 8:30 PM

Change 979700 had a related patch set uploaded (by Kosta Harlan; author: Dreamy Jazz):

[mediawiki/extensions/MediaModeration@wmf/1.42.0-wmf.7] Add maintenance script to import existing files to scan table

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

gerritbot added a project: Patch-For-Review.Dec 5 2023, 12:16 PM

Change 979700 merged by jenkins-bot:

[mediawiki/extensions/MediaModeration@wmf/1.42.0-wmf.7] Add maintenance script to import existing files to scan table

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

Mentioned in SAL (#wikimedia-operations) [2023-12-05T14:23:51Z] <urbanecm@deploy2002> Started scap: Backport for [[gerrit:979698|User impact: update quantizeViews to process small series of view data (T352349)]], [[gerrit:979700|Add maintenance script to import existing files to scan table (T350863)]], [[gerrit:979701|Only allow drawing and bitmap media types to be scanned (T352234)]]

Mentioned in SAL (#wikimedia-operations) [2023-12-05T14:25:08Z] <urbanecm@deploy2002> kharlan and urbanecm: Backport for [[gerrit:979698|User impact: update quantizeViews to process small series of view data (T352349)]], [[gerrit:979700|Add maintenance script to import existing files to scan table (T350863)]], [[gerrit:979701|Only allow drawing and bitmap media types to be scanned (T352234)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Maintenance_bot removed a project: Patch-For-Review.Dec 5 2023, 2:30 PM

Mentioned in SAL (#wikimedia-operations) [2023-12-05T14:32:47Z] <urbanecm@deploy2002> Finished scap: Backport for [[gerrit:979698|User impact: update quantizeViews to process small series of view data (T352349)]], [[gerrit:979700|Add maintenance script to import existing files to scan table (T350863)]], [[gerrit:979701|Only allow drawing and bitmap media types to be scanned (T352234)]] (duration: 08m 55s)

	F41551711: image.png
	Dec 1 2023, 8:05 PM

	F41551707: image.png
	Dec 1 2023, 8:05 PM

	F41551654: image.png
	Dec 1 2023, 8:05 PM

	F41551630: image.png
	Dec 1 2023, 8:05 PM

Create maintenance script to import all existing images to mediamoderation_scan tableClosed, ResolvedPublic3 Estimated Story PointsActions