Run refreshImageMetadata.php --force
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	Bawolff
	Sep 18 2011, 2:32 AM

Description

1.18 added a lot of new image metadata properties (Exif and friends) that are now extracted that weren't previously. Please run refreshImageMetadata.php after the 1.18 deployment, which will go through every image and re-extract all the metadata.

Also, the script outputs the name of any image that had a larger img_metadata field before the refresh relative to after it. This could indicate a bug in new img_metadata stuff since almost all of the old properties are still extracted, so the img_metadata field should either stay the same or increase in size. (There is one exception to this, but it should be very rare). It'd be great if stdout of that script could be redirected to a file and posted somewhere, so I could investigate any cases of the metadata decreasing in size.

Cheers.

The command to be executed:

foreachwiki refreshImageMetadata.php --force --mediatype=BITMAP

Details

Reference: bz30961

	Title	Reference	Author	Source Branch	Dest Branch
	Add banner to the top of the page	sebastian-berlin-wmse/wikipedia-homepage!18	sebastian-berlin-wmse	banner	main

Customize query in GitLab

Related Objects
Search...

Status	Assigned	Task
Open	None	T32961 Run refreshImageMetadata.php --force
Resolved	Bawolff	T33740 (Jpeg\|Gif)MetadataExtractor lacks bounds checking, spams error logs with E_WARNING
Resolved	MoritzMuehlenhoff	T140419 Deploy a PHP and HHVM patch (Exif values retrieved incorrectly if they appear before IFD)
Resolved	matmarex	T97253 Exif values retrieved incorrectly if they appear before IFD
Resolved	matmarex	T140446 Consider our options for fixing existing files affected by T97253

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

(In reply to comment #5)

Still running ? Finished ? Halted ?

I believe its halted pending me fixing bug 31740 (which i plan to soon)

Yes, it was stopped for that bug, and I've now backported and deployed the bugfix and restarted the script.

It looks like it just crashed due to OOM, while doing commonswiki.

Core dumping indicates that the bulk of the leaked memory is in image metadata structures, such as XMP. It's possible that it is leaking LocalFile objects.

Thehelpfulonewiki wrote:

Has this been run successfully?

(In reply to comment #10)

Has this been run successfully?

I imagine not, as no one has addressed the memory leaks

Was scheduled for 1.18 deployment. Adding to 1.20wmf3 deployment now.

(In reply to comment #12)

Was scheduled for 1.18 deployment. Adding to 1.20wmf3 deployment now.

There's no point. The script needs fixing first

(In reply to comment #13)

(In reply to comment #12)

Was scheduled for 1.18 deployment. Adding to 1.20wmf3 deployment now.

There's no point. The script needs fixing first

Aha, I didn't read the previous comment right. Makes sense now.

(In reply to comment #6)

(In reply to comment #5)

Still running ? Finished ? Halted ?

I believe its halted pending me fixing bug 31740 (which i plan to soon)

That bug has been resolved as fixed in the mean time.

(In reply to comment #9)

Core dumping indicates that the bulk of the leaked memory is in image metadata
structures, such as XMP. It's possible that it is leaking LocalFile objects.

(In reply to comment #13)

(In reply to comment #12)

Was scheduled for 1.18 deployment. Adding to 1.20wmf3 deployment now.

There's no point. The script needs fixing first

What has to be fixed still? Can a new bug be opened please as blocker to this one. Other wise, how about another run?

What has to be fixed still? Can a new bug be opened please as blocker to this
one. Other wise, how about another run?

The script appearently has memory leaks. Personally I'm not sure how or why that happens in a garbage collected language like php (furthermore one needs a test wiki with more than 10 files on it to really reproduce that issue), so fixing is a bit beyond me.

(In reply to comment #15)

The script needs fixing first

What has to be fixed still? Can a new bug be opened please as blocker to this
one. Other wise, how about another run?

Could somebody please specify in a new bug report what's need to fix (any pointers etc) in the script, and make it block this report?

High priority for 20 months & no changes for 9 months => resetting to normal.

High priority was probably an exageration, and its especially not now. As people notice a file without metadata, they can purge it if it bothers them.

As for fixing the bug. Im not really sure how or why there is a mem leak in the script or how to debug it.

Liuxinyu970226 subscribed.Feb 20 2015, 3:45 PM

tomasz removed a project: Shell.Feb 23 2015, 7:56 PM

tomasz set Security to None.

If someone is planning to work on it, please raise the priority to low (or normal if you're going to do it soon), see Z398.

Restricted Application added subscribers: JEumerus, Matanya. · View Herald TranscriptApr 14 2016, 11:48 AM

Krinkle unsubscribed.Apr 21 2016, 8:53 PM

In T32961#346191, @Bawolff wrote:

The script appearently has memory leaks. Personally I'm not sure how or why that happens in a garbage collected language like php (furthermore one needs a test wiki with more than 10 files on it to really reproduce that issue), so fixing is a bit beyond me.

The last attempt to run the script was on Oct 25 2011 (T32961#346087). I can't find out whether we were using PHP 5.2 or 5.3 back then, but it's worth noting that 5.2 was unable to garbage collect cycles (http://php.net/manual/pl/features.gc.collecting-cycles.php). Perhaps it was just that, and we could successfully run the script today. I think we should try that first and if it still OOM's, then look into it. And if it's still a problem, we're all four years older than when this was last touched, I bet @Bawolff could debug it with his eyes closed today ;)

matmarex added subtasks: T140543: refreshImageMetadata.php maintenance script should handle oldimage.oi_metadata and filearchive.fa_metadata (in addition to image.img_metadata), T140419: Deploy a PHP and HHVM patch (Exif values retrieved incorrectly if they appear before IFD).Jul 16 2016, 9:35 PM

matmarex mentioned this in T140446: Consider our options for fixing existing files affected by T97253.Jul 16 2016, 9:40 PM

matmarex removed a parent task: T33391: Aspect ratio incorrect for rotated images uploaded prior to 1.18 deployment.

matmarex removed a parent task: T31876: 1.18 post-deployment actions (tracking).Jul 16 2016, 9:43 PM

@matmarex Please provide a test run procedure to determine how memory behaves.

matmarex renamed this task from Run refreshImageMetadata.php to Run refreshImageMetadata.php --force.Jul 16 2016, 9:45 PM

matmarex removed a subscriber: • wikibugs-l-list.

I'd say start the script, let it run for a day or two while watching its memory usage with top or something, and verify that it doesn't grow indefinitely? Anyway, there are a few things which we should do first, otherwise we'll have to run it twice.

(Assigning to self so that I don't forget about this, I'm not a deployer and I'll need someone else to actually do the deed.)

matmarex removed a subtask: T140543: refreshImageMetadata.php maintenance script should handle oldimage.oi_metadata and filearchive.fa_metadata (in addition to image.img_metadata).Aug 4 2016, 7:28 PM

Dereckson mentioned this in T17889: Write and run script to find non-existent images on Wikimedia wikis.Sep 9 2016, 5:59 AM

MoritzMuehlenhoff closed subtask T140419: Deploy a PHP and HHVM patch (Exif values retrieved incorrectly if they appear before IFD) as Resolved.Oct 7 2016, 7:52 AM

matmarex mentioned this in T145953: File displaying invalid metadata in Commons, when the original Exif seems fine..Oct 7 2016, 2:48 PM

matmarex added a subscriber: jcrespo.Oct 7 2016, 3:02 PM

I think there are no blockers here anymore. We should try doing this. But first:

@jcrespo The maintenance script will UPDATE all rows in the image table (I'd like it to be ran for all databases, the table is biggest on commonswiki with ~34 million rows). I just want to make sure that this is reasonable.
@aaron You previously removed metadata refreshing from action=purge, it was apparently implicated in T132921 (9120ee007ae32). Since this will refresh metadata for all files, it should probably be throttled heavily… Could you review the refreshImageMetadata.php script to see whether it is sane?

If you're both okay with doing this, we should probably start with testwiki for another sanity check (~5000 files). Then do all the smaller wikis and Commons (I did a select count(*) from image for each wiki in all.dblist to see the sizes: P4193).

As discussed earlier in this task, the last time we tried to run the script, it OOM'd. That was five years ago and on PHP 5.2 or 5.3, though, and I suspect that was just PHP's garbage collection bugs. But whoever runs the script (I nominate @aaron) should watch its memory usage.

matmarex mentioned this in T147015: Exif orientation problem (no metadata extracted due to empty JPEG segment).Oct 10 2016, 9:25 PM

Here are the tips:

Make sure you do not create lag- I have not checked it, but make sure your transactions are short, and you check for lag every second
Take your time and check concurrency- do not run one thread per wiki with 800 wikis on s3
If you intend to update a large amount of rows, add it to the week Deployments page ("week of") with an estimation on how much time it is going to take (see Long running tasks/scripts): https://wikitech.wikimedia.org/wiki/Deployments#Week_of_October_10th Also log when you start- specially for commons. This will make sure we do not run schema changes at the same time

It seems sane to me; and +1 to Jamie's comment.

@matmarex: You might want to go out of your way to skip audio/video file for now btw. Parsing that stuff is super expensive I believe, because as far as I've been told, it will fetch the entire file or something from the storage cluster to parse the metadata. Since video files tend to be large....

In T32961#2704871, @jcrespo wrote:

Make sure you do not create lag- I have not checked it, but make sure your transactions are short, and you check for lag every second

The script calls wfWaitForSlaves(); after every batch of 200 images, is that okay? Or should we do smaller batches?

Take your time and check concurrency- do not run one thread per wiki with 800 wikis on s3

Right, we usually run these sequentially (with foreachwiki).

If you intend to update a large amount of rows, add it to the week Deployments page ("week of") with an estimation on how much time it is going to take (see Long running tasks/scripts): https://wikitech.wikimedia.org/wiki/Deployments#Week_of_October_10th Also log when you start- specially for commons. This will make sure we do not run schema changes at the same time

At the moment I have no idea how long it's going to take. We should do one of the smaller wikis first, and estimate based on that.

In T32961#2707318, @TheDJ wrote:

@matmarex: You might want to go out of your way to skip audio/video file for now btw. Parsing that stuff is super expensive I believe, because as far as I've been told, it will fetch the entire file or something from the storage cluster to parse the metadata. Since video files tend to be large....

So, refreshImageMetadata.php --force --mediatype=BITMAP, I guess. (We do also have images of ridiculous sizes… but probably fewer.)

@aaron Would you be able to run the script for testwiki this week? (Less than 5000 files. We'll see how fast it goes, hopefully it'll take minutes at most…)

I guess that's a "no". I might not be around to watch this next week (I'll be at an off-site meeting), but I'll start poking people again the week after.

Terbium is currently busy with a Wikidata sanitize script.

MarkAHershberger unsubscribed.Oct 15 2016, 2:54 PM

MarkTraceur moved this task from Untriaged to Next up on the Multimedia board.Dec 2 2016, 9:36 PM

matmarex mentioned this in T152693: EXIF metadata displayed incorrectly.Dec 9 2016, 3:13 PM

For testwiki

Finished refreshing file metadata for 4644 files. 1 needed to be refreshed, 4643 did not need to be but were refreshed anyways, and 714 refreshes were suspicious.

real    12m46.582s
user    1m50.603s
sys     0m45.917s

Here are some statistics on the changes. I ran select img_name, img_metadata as img_metadata from image; before and after Reedy ran the maintenance, and compared the results with a crafty ugly little script.

Some of these are unexpected. We're apparently failing to extract GPS data from a number of files, that we could extract some years ago when they were uploaded? We should probably investigate this before running the script everywhere :/ I have a copy of the data if anyone is interested.

Key	Added	Removed	Identical	Changed
MEDIAWIKI_EXIF_VERSION	0	0	3921	0
DateTime	0	0	2231	0
Model	0	0	2593	0
Orientation	0	0	2490	0
ImageLength	0	0	1067	0
ImageWidth	0	0	1068	0
Make	0	0	2594	0
XResolution	0	0	2055	0
YResolution	0	0	2055	0
ResolutionUnit	0	0	2054	0
Copyright	0	0	59	0
WebStatement	0	0	22	0
Copyrighted	0	0	48	0
UsageTerms	0	0	21	0
iimVersion	0	0	165	0
YCbCrPositioning	0	0	1949	0
ISOSpeedRatings	0	0	1608	0
ExifVersion	0	0	2314	0
DateTimeOriginal	0	0	2165	0
DateTimeDigitized	0	0	2164	0
ComponentsConfiguration	0	0	1892	0
FocalLength	0	0	1955	0
FlashPixVersion	0	591	1350	0
ColorSpace	0	592	1830	0
CompressedBitsPerPixel	0	0	619	0
ExposureTime	0	0	1756	0
FNumber	0	0	1750	0
ExposureProgram	0	0	1596	0
ShutterSpeedValue	0	0	1478	0
ApertureValue	0	0	1451	0
BrightnessValue	0	0	1241	0
ExposureBiasValue	0	0	1242	0
MaxApertureValue	0	0	1261	0
SubjectDistance	0	0	358	0
MeteringMode	0	0	1719	0
LightSource	0	0	635	0
Flash	0	0	1721	0
FlashEnergy	0	0	312	0
SensingMethod	0	108	759	0
SceneType	0	107	436	0
CustomRendered	0	126	585	0
ExposureMode	0	582	1084	0
WhiteBalance	0	584	1098	0
DigitalZoomRatio	0	90	571	0
SceneCaptureType	0	583	1180	0
Contrast	0	117	386	0
Saturation	0	117	386	0
Sharpness	0	119	411	0
SubjectDistanceRange	0	27	411	0
ImageUniqueID	0	414	291	0
GPSTimeStamp	0	214	746	0
GPSDateStamp	0	212	671	0
version	0	0	206	0
streams	0	0	63	0
length	0	0	63	0
offset	0	0	58	0
Software	0	0	1339	0
SubSecTime	0	30	41	0
SubSecTimeOriginal	0	41	59	0
SubSecTimeDigitized	0	40	59	0
FocalPlaneXResolution	0	31	79	0
FocalPlaneYResolution	0	31	80	0
FocalPlaneResolutionUnit	0	31	80	0
GPSVersionID	0	244	174	3
frameCount	2	0	699	0
loopCount	2	0	655	0
duration	2	0	699	0
bitDepth	2	0	655	0
colorType	2	0	655	0
metadata	3	0	736	0
JPEGFileComment	0	0	539	0
looped	0	0	44	0
ImageDescription	0	0	91	0
Writer	0	0	34	0
SpecialInstructions	0	0	35	0
Artist	0	0	75	0
Source	0	0	34	0
ObjectName	0	0	87	0
CityDest	0	0	28	0
OriginalTransmissionRef	0	0	34	0
iimCategory	0	0	36	0
iimSupplementalCategory	0	0	35	0
Keywords	0	0	79	0
FileSource	0	121	166	0
CountryDest	0	0	40	0
Credit	0	0	34	0
FlashpixVersion	0	0	42	0
PixelXDimension	0	0	100	0
PixelYDimension	0	0	100	0
DateTimeMetadata	0	0	79	0
FocalLengthIn35mmFilm	0	103	440	0
GainControl	0	77	127	0
WhitePoint	0	0	13	0
PrimaryChromaticities	0	0	13	0
YCbCrCoefficients	0	0	13	0
UserComment	0	421	75	0
ProvinceOrStateDest	0	0	22	0
SerialNumber	0	0	75	0
Lens	0	0	76	0
PhotometricInterpretation	0	0	29	0
SamplesPerPixel	0	0	27	0
BitsPerSample	0	0	29	0
Rating	0	0	11	0
GPSLatitude	0	189	312	3
GPSLongitude	0	189	312	3
GPSAltitude	0	184	265	17
width	0	0	133	0
height	0	0	133	0
Contact	0	0	3	0
GETID3_VERSION	0	0	9	0
filesize	0	0	9	0
avdataoffset	0	0	9	0
avdataend	0	0	9	0
fileformat	0	0	9	0
audio	0	0	5	0
video	0	0	8	0
encoding	0	0	9	0
mime_type	0	0	9	0
playtime_seconds	0	0	9	0
bitrate	0	0	9	0
playtime_string	0	0	9	0
GPSImgDirectionRef	0	3	76	0
GPSImgDirection	0	4	76	0
CameraOwnerName	0	0	4	0
OriginalDocumentID	0	0	20	0
xml	0	0	2	0
description	0	0	8	0
SublocationDest	0	0	1	0
Compression	0	0	9	0
PlanarConfiguration	0	0	8	0
Label	0	0	3	0
RelatedSoundFile	0	5	0	0
ExposureIndex	0	6	0	0
animated	0	0	1	0
originalWidth	0	0	61	0
originalHeight	0	0	61	0
Producer	0	0	19	0
CreationDate	0	0	19	0
ModDate	0	0	17	0
Tagged	0	0	19	0
Pages	0	0	19	0
Encrypted	0	0	19	0
pages	0	0	19	0
File size	0	0	19	0
Optimized	0	0	19	0
PDF version	0	0	19	0
mergedMetadata	0	0	12	0
text	0	0	15	0
warning	0	0	3	0
GPSAltitudeRef	0	0	2	0
Author	0	0	8	0
Creator	0	0	16	0
page_data	0	0	15	0
page_count	0	0	15	0
first_page	0	0	15	0
last_page	0	0	15	0
exif	0	0	13	2
TIFF_METADATA_VERSION	0	0	15	0
Title	0	0	12	0
Subject	0	0	3	0
translations	0	0	45	0
ReferenceBlackWhite	0	0	4	0
title	0	0	7	0
GPSStatus	0	1	3	0
GPSMapDatum	0	3	35	0
Headline	0	0	6	0
CountryCodeDest	0	0	6	0
GPSDifferential	0	1	0	0
warnings	0	9	0	0
GPSSpeedRef	0	0	2	0
GPSSpeed	0	0	2	0
GPSMeasureMode	0	2	0	0
GPSDOP	0	2	0	0
error	0	0	1	0

At least the "Changed" ones are harmless.

Some values were previously recorded as rational numbers, as in the EXIF data; now they are stored as floats:

Changed: GPSAltitude old=0/1 new=0
Changed: GPSAltitude old=0/1 new=0
Changed: GPSAltitude old=0/1 new=0
Changed: GPSAltitude old=0/1000 new=0
Changed: GPSAltitude old=112/1 new=112
Changed: GPSAltitude old=15907/48 new=331.3958333333333
Changed: GPSAltitude old=180/1 new=180
Changed: GPSAltitude old=26178/83 new=315.3975903614458
Changed: GPSAltitude old=29553/94 new=314.3936170212766
Changed: GPSAltitude old=38365/124 new=309.39516129032256
Changed: GPSAltitude old=38365/124 new=309.39516129032256
Changed: GPSAltitude old=46799/1000 new=46.799
Changed: GPSAltitude old=46799/1000 new=46.799
Changed: GPSAltitude old=46799/1000 new=46.799
Changed: GPSAltitude old=48797/272 new=179.40073529411765
Changed: GPSAltitude old=63/1 new=63
Changed: GPSAltitude old=93785/283 new=331.3957597173145

Some have inconsequential differences due to some float precision stuff:

Changed: GPSLatitude old=32.24146391666667 new=32.241463920166666
Changed: GPSLatitude old=32.24146391666667 new=32.241463920166666
Changed: GPSLatitude old=32.24146391666667 new=32.241463920166666
Changed: GPSLongitude old=-110.94342988333334 new=-110.94342988566666
Changed: GPSLongitude old=-110.94342988333334 new=-110.94342988566666
Changed: GPSLongitude old=-110.94342988333334 new=-110.94342988566666

And some were sufferring from endianness issues:

Changed: GPSVersionID old=0.0.0.2 new=2.0.0.0
Changed: GPSVersionID old=0.0.0.2 new=2.0.0.0
Changed: GPSVersionID old=0.0.0.2 new=2.0.0.0

I've looked at a couple files in detail, and it looks like the "Removed" values are caused by a PHP bug: https://bugs.php.net/bug.php?id=72682 (the missing data is all after a MakerNote tag). It's already fixed in the latest version (or mostly fixed, someone is still complaining on the bug, but I've gotten good results locally with the files I tried), but we clearly have something older. (And the bug was apparently introduced by a security patch, https://bugs.php.net/bug.php?id=72603, which was probably backported everywhere…)

If possible, we should retry the maintenance script using HHVM rather than PHP, which shouldn't be suffering from this problem. Or we'll need to wait a while again until we upgrade PHP.

Reedy re-ran the script with HHVM. That looks much better, looks like nothing is getting lost, and even some new stuff is getting extracted. But there are also more changes?

Key	Added	Removed	Identical	Changed
MEDIAWIKI_EXIF_VERSION	0	0	3921	0
DateTime	3	0	2180	51
Model	54	0	2593	0
Orientation	53	0	2490	0
ImageLength	53	0	1067	0
ImageWidth	53	0	1068	0
Make	53	0	2593	1
XResolution	0	0	2054	1
YResolution	0	0	2054	1
ResolutionUnit	0	0	2054	0
Copyright	0	0	59	0
WebStatement	0	0	22	0
Copyrighted	0	0	48	0
UsageTerms	0	0	21	0
iimVersion	0	0	165	0
YCbCrPositioning	0	0	1949	0
ISOSpeedRatings	0	0	1608	0
ExifVersion	0	0	2314	0
DateTimeOriginal	0	0	2164	1
DateTimeDigitized	1	0	2164	0
ComponentsConfiguration	0	0	1892	0
FocalLength	1	0	1955	0
FlashPixVersion	0	0	1941	0
ColorSpace	0	0	2422	0
CompressedBitsPerPixel	0	0	618	1
ExposureTime	0	0	1755	1
FNumber	0	0	1749	1
ExposureProgram	0	0	1596	0
ShutterSpeedValue	0	0	1477	1
ApertureValue	0	0	1450	1
BrightnessValue	0	0	1241	0
ExposureBiasValue	0	0	1241	1
MaxApertureValue	0	0	1260	1
SubjectDistance	0	0	357	1
MeteringMode	0	0	1719	0
LightSource	0	0	635	0
Flash	0	0	1721	0
FlashEnergy	0	0	312	0
SensingMethod	0	0	867	0
SceneType	0	0	543	0
CustomRendered	0	0	711	0
ExposureMode	0	0	1666	0
WhiteBalance	0	0	1682	0
DigitalZoomRatio	0	0	661	0
SceneCaptureType	0	0	1763	0
Contrast	0	0	503	0
Saturation	0	0	503	0
Sharpness	0	0	530	0
SubjectDistanceRange	0	0	438	0
ImageUniqueID	0	0	705	0
GPSTimeStamp	16	0	960	0
GPSDateStamp	16	0	883	0
version	0	0	206	0
streams	0	0	63	0
length	0	0	63	0
offset	0	0	58	0
Software	0	0	1339	0
SubSecTime	0	0	71	0
SubSecTimeOriginal	0	0	100	0
SubSecTimeDigitized	0	0	99	0
FocalPlaneXResolution	1	0	110	0
FocalPlaneYResolution	0	0	110	1
FocalPlaneResolutionUnit	0	0	111	0
GPSVersionID	0	0	421	0
frameCount	2	0	699	0
loopCount	2	0	655	0
duration	2	0	699	0
bitDepth	2	0	655	0
colorType	2	0	655	0
metadata	3	0	736	0
JPEGFileComment	0	0	539	0
looped	0	0	44	0
FileSource	0	0	287	0
ImageDescription	0	0	91	0
Writer	0	0	34	0
SpecialInstructions	0	0	35	0
Artist	0	0	75	0
Source	0	0	34	0
ObjectName	0	0	87	0
CityDest	0	0	28	0
OriginalTransmissionRef	0	0	34	0
iimCategory	0	0	36	0
iimSupplementalCategory	0	0	35	0
Keywords	0	0	79	0
CountryDest	0	0	40	0
Credit	0	0	34	0
FlashpixVersion	0	0	42	0
PixelXDimension	0	0	100	0
PixelYDimension	0	0	100	0
DateTimeMetadata	0	0	79	0
FocalLengthIn35mmFilm	0	0	543	0
GainControl	0	0	204	0
WhitePoint	0	0	13	0
PrimaryChromaticities	0	0	13	0
YCbCrCoefficients	0	0	13	0
UserComment	0	0	496	0
ProvinceOrStateDest	0	0	22	0
SerialNumber	0	0	75	0
Lens	0	0	76	0
PhotometricInterpretation	0	0	29	0
SamplesPerPixel	0	0	27	0
BitsPerSample	0	0	29	0
Rating	0	0	11	0
GPSLatitude	16	0	504	0
GPSLongitude	16	0	504	0
GPSAltitude	16	0	449	17
width	0	0	133	0
height	0	0	133	0
Contact	0	0	3	0
GETID3_VERSION	0	0	9	0
filesize	0	0	9	0
avdataoffset	0	0	9	0
avdataend	0	0	9	0
fileformat	0	0	9	0
audio	0	0	5	0
video	0	0	8	0
encoding	0	0	9	0
mime_type	0	0	9	0
playtime_seconds	0	0	9	0
bitrate	0	0	9	0
playtime_string	0	0	9	0
GPSImgDirectionRef	0	0	79	0
GPSImgDirection	0	0	80	0
CameraOwnerName	0	0	4	0
OriginalDocumentID	0	0	20	0
xml	0	0	2	0
description	0	0	8	0
SublocationDest	0	0	1	0
Compression	0	0	9	0
PlanarConfiguration	0	0	8	0
Label	0	0	3	0
RelatedSoundFile	0	0	5	0
ExposureIndex	0	0	6	0
animated	0	0	1	0
originalWidth	0	0	61	0
originalHeight	0	0	61	0
Producer	0	0	19	0
CreationDate	0	0	19	0
ModDate	0	0	17	0
Tagged	0	0	19	0
Pages	0	0	19	0
Encrypted	0	0	19	0
pages	0	0	19	0
File size	0	0	19	0
Optimized	0	0	19	0
PDF version	0	0	19	0
mergedMetadata	0	0	12	0
text	0	0	15	0
warning	0	0	3	0
GPSAltitudeRef	0	0	2	0
Author	0	0	8	0
Creator	0	0	16	0
page_data	0	0	15	0
page_count	0	0	15	0
first_page	0	0	15	0
last_page	0	0	15	0
exif	0	0	13	2
TIFF_METADATA_VERSION	0	0	15	0
Title	0	0	12	0
Subject	0	0	3	0
translations	0	0	45	0
ReferenceBlackWhite	0	0	4	0
title	0	0	7	0
GPSStatus	0	0	4	0
GPSMapDatum	0	0	38	0
Headline	0	0	6	0
CountryCodeDest	0	0	6	0
GPSDifferential	0	0	1	0
warnings	0	9	0	0
GPSSpeedRef	0	0	2	0
GPSSpeed	0	0	2	0
GPSMeasureMode	0	0	2	0
GPSDOP	0	0	2	0
error	0	0	1	0

Looking at the new changes:

Some DateTimes have changed by a few seconds. Looking at the problematic files, they seem to have duplicate DateTime tags, with actually different values; and apparently old code took the first one, while new code takes that last one. That should be harmless.

Changed: DateTime old=2012:06:28 20:26:27 new=2012:06:28 20:26:23
Changed: DateTime old=2012:07:06 15:06:07 new=2012:07:06 15:06:03
Changed: DateTime old=2012:07:10 09:12:08 new=2012:07:10 09:11:54
Changed: DateTime old=2012:07:10 09:12:08 new=2012:07:10 09:11:54
Changed: DateTime old=2012:07:10 10:44:25 new=2012:07:10 10:44:22
Changed: DateTime old=2012:07:10 15:26:42 new=2012:07:10 15:26:38
Changed: DateTime old=2012:07:10 18:13:33 new=2012:07:10 18:13:30
Changed: DateTime old=2012:07:11 11:20:57 new=2012:07:11 11:20:54
Changed: DateTime old=2012:07:11 11:39:15 new=2012:07:11 11:39:12
Changed: DateTime old=2012:07:11 11:42:34 new=2012:07:11 11:42:32
Changed: DateTime old=2012:07:11 11:42:59 new=2012:07:11 11:42:57
Changed: DateTime old=2012:07:11 11:44:16 new=2012:07:11 11:44:14
Changed: DateTime old=2012:07:11 11:49:14 new=2012:07:11 11:49:11
Changed: DateTime old=2012:07:11 11:49:14 new=2012:07:11 11:49:11
Changed: DateTime old=2012:07:11 11:52:09 new=2012:07:11 11:52:06
Changed: DateTime old=2012:07:11 11:54:58 new=2012:07:11 11:54:55
Changed: DateTime old=2012:07:11 11:54:58 new=2012:07:11 11:54:55
Changed: DateTime old=2012:07:11 11:54:58 new=2012:07:11 11:54:55
Changed: DateTime old=2012:07:11 11:54:58 new=2012:07:11 11:54:55
Changed: DateTime old=2012:07:11 12:00:33 new=2012:07:11 12:00:30
Changed: DateTime old=2012:07:11 12:06:32 new=2012:07:11 12:06:29
Changed: DateTime old=2012:07:11 12:09:41 new=2012:07:11 12:09:38
Changed: DateTime old=2012:07:11 12:29:08 new=2012:07:11 12:29:04
Changed: DateTime old=2012:07:11 13:54:13 new=2012:07:11 13:54:08
Changed: DateTime old=2012:07:11 14:12:27 new=2012:07:11 14:12:24
Changed: DateTime old=2012:07:11 14:12:27 new=2012:07:11 14:12:24
Changed: DateTime old=2012:07:11 14:12:27 new=2012:07:11 14:12:24
Changed: DateTime old=2012:07:11 14:24:58 new=2012:07:11 14:24:55
Changed: DateTime old=2012:07:11 14:24:58 new=2012:07:11 14:24:55
Changed: DateTime old=2012:07:13 07:27:23 new=2012:07:13 07:27:16
Changed: DateTime old=2012:07:13 08:08:36 new=2012:07:13 08:08:31
Changed: DateTime old=2012:07:13 10:05:46 new=2012:07:13 10:05:38
Changed: DateTime old=2012:07:13 10:22:46 new=2012:07:13 10:22:40
Changed: DateTime old=2012:07:13 17:12:22 new=2012:07:13 17:12:17
Changed: DateTime old=2012:07:13 17:35:03 new=2012:07:13 17:34:59
Changed: DateTime old=2012:07:17 15:30:50 new=2012:07:17 15:30:46
Changed: DateTime old=2012:07:20 12:09:43 new=2012:07:20 12:09:40
Changed: DateTime old=2012:07:23 17:58:22 new=2012:07:23 17:58:18
Changed: DateTime old=2012:07:23 19:08:02 new=2012:07:23 19:07:57
Changed: DateTime old=2012:08:20 14:15:10 new=2012:08:20 14:15:04
Changed: DateTime old=2012:08:25 07:40:58 new=2012:08:25 07:40:54
Changed: DateTime old=2012:08:29 13:47:29 new=2012:08:29 13:47:25
Changed: DateTime old=2012:08:29 20:48:39 new=2012:08:29 20:48:30
Changed: DateTime old=2012:08:30 13:12:31 new=2012:08:30 13:12:23
Changed: DateTime old=2012:08:30 14:34:36 new=2012:08:30 14:34:24
Changed: DateTime old=2012:09:01 11:17:45 new=2012:09:01 11:17:40
Changed: DateTime old=2012:09:22 14:55:18 new=2012:09:22 14:55:15
Changed: DateTime old=2012:09:22 17:40:24 new=2012:09:22 17:40:19
Changed: DateTime old=2012:09:22 19:30:38 new=2012:09:22 19:30:29
Changed: DateTime old=2012:09:24 12:52:25 new=2012:09:24 12:52:14

The various instances of single changes appears to be a case of T97253, which is exactly what we're trying to fix, yay! (https://test.wikipedia.org/wiki/File:BumpassHell_8328.jpg) That file was apparently also affected by T5892 once upon a time, heh.

Changed: Make old=IF new=Canon
Changed: XResolution old=1124073500/1852796513 new=180/1
Changed: YResolution old=1851867904/1344302703 new=180/1
Changed: DateTime old=owerShot S110 new=2005:10:25 12:56:28
Changed: ExposureTime old=11796480/65536 new=1/800
Changed: FNumber old=808583168/825898288 new=72/10
Changed: DateTimeOriginal old=0:25 12:56:28 new=2005:10:25 12:56:28
Changed: CompressedBitsPerPixel old=976367904/842675765 new=3/1
Changed: ShutterSpeedValue old=808583224/825898288 new=309/32
Changed: ApertureValue old=892484144/976367904 new=183/32
Changed: ExposureBiasValue old=842675765/196664 new=-3/3
Changed: MaxApertureValue old=65536/20250624 new=194698/65536
Changed: SubjectDistance old=2097152/11993088 new=2215/1000
Changed: FocalPlaneYResolution old=2/145162241 new=1200000/155

Right, so I believe we're fine from the correctness perspective. 4644 files is not a huge sample, but testwiki tends to have all the weird files causing problems uploaded to it, so I think it's unlikely we missed any terrible bugs that are going to wreck our metadata.

Processing 4644 image files on testwiki took 12m46.582s, so my estimate for processing 34,000,000 image files on Commons is around two months. I'll talk to Greg and schedule this to happen some time soon (not sure if we want to do this during vacation, although I think it's low risk).

Note that the maintenance script must be run with the hhvm executable and not php5 (mwscript currently uses the latter!).

We might want to delay this by a few more days if that lets us deploy the HHVM patch for T148606 first.

matmarex updated the task description. (Show Details)Dec 9 2016, 10:26 PM

@jcrespo question for you on this: @matmarex estimates it'll take roughly 2 months to complete. Should we start now-ish or wait until after the new year? I'm on the fence, honestly.

my estimate for processing 34,000,000 image files on Commons is around two months

I (and probably Releng neither) will not allow a single script to run uninterruptedly for 2 months (that doesn't mean we will not allow running this for several months). It has to be puppetized, do (relatively) short runs by which it reloads its mediawiki version and database configuration, and survives a terbium restart, or a failover to codfw. In a few months we may be doing a datacenter failover or periods of read-only time, during which your script has to be stopped or reloaded. Also, new versions or mediawiki will be upgraded, and the script should be prepared to use those new versions.

2 month seem a bit too much- Is the script completely serialized? Have you considered ask for extra resources ( a dedicated server) and do multiple parallel runs (not on concurrent data) and only serialize database changes (I assume many rows will be unchanged?) ?

If this involves reading physically every single file we have, I would involve the operators of swift file backend, too.

/me waits for follow-up questions to be resolved :)

In T32961#2860921, @jcrespo wrote:

I (and probably Releng neither) will not allow a single script to run uninterruptedly for 2 months (that doesn't mean we will not allow running this for several months). It has to be puppetized, do (relatively) short runs by which it reloads its mediawiki version and database configuration, and survives a terbium restart, or a failover to codfw. In a few months we may be doing a datacenter failover or periods of read-only time, during which your script has to be stopped or reloaded. Also, new versions or mediawiki will be upgraded, and the script should be prepared to use those new versions.

Right, that is reasonable. The script can be interrupted (killed) at any time, and it can be resumed later using the --start=filename command line option (where filename should be the last filename in the output until the script was killed). It can be run in smaller batches using --start and --end (e.g. hhvm refreshImageMetadata.php --force --mediatype=BITMAP --start=A --end=B).

Using --start with --mediatype=BITMAP will result in queries like below. There is no ideal index for this, we only have separate indexes in img_name (PRIMARY) and img_media_type (img_media_mime), but bitmaps are the vast majority of files, so these should be okay as long as MySQL uses the PRIMARY index.

select * from commonswiki.image
where img_name>'B' and img_media_type='BITMAP'
order by img_name
limit 200;

I don't know how to puppetize it (or why we'd need to puppetize), do we have docs or examples?

2 month seem a bit too much- Is the script completely serialized? Have you considered ask for extra resources ( a dedicated server) and do multiple parallel runs (not on concurrent data) and only serialize database changes (I assume many rows will be unchanged?) ?

What do you mean by "completely serialized"? It processes files alphabetically, one by one, in a single thread. So I guess yes?

We could easily run multiple instances of refreshImageMetadata.php with the --start and --end options (see above). But I don't think we can coordinate database writes between them in any way. And I have no idea how many we can run in parallel before some part of our infrastructure gives out :) From my perspective, this doesn't need to be done quickly (but I'd like it to be done eventually), so I could wait those two months and I'd rather not ask for extra resources.

The script will currently issue an UPDATE query for every single row in the table, but the data will often be the same (in the testwiki run, 105 rows were changed, 4771 unchanged). (Should we make an effort to avoid these no-op UPDATEs, or can MySQL optimize them out?)

If this involves reading physically every single file we have, I would involve the operators of swift file backend, too.

It does (but assuming that we run with --mediatype=BITMAP, as proposed, it'll only be bitmap files, no videos etc.). @aaron said earlier on this task that it's okay, I guess we should CC @fgiunchedi too.

My 2¢: Instead of using a super-long running maintenance script, consider just having a script to (slowly) queue jobs in the job queue. We can set up a dedicated job runner for it, so the rest of the queue isn't impacted, and pretty well control parallelism, monitoring, etc. And it will (well, should) automatically handle slave lag and backoff appropriately.

Another thing that should be considered is whether this is a one-off run, or should we plan to run it more often as new metadata stuffs are supported by MW?

My 2¢: Instead of using a super-long running maintenance script, consider just having a script to (slowly) queue jobs in the job queue.

+1 to that. @matmarex puppetization can be as easy as setting up a cron on terbium. I can help with that, we just need to figure out the best way to coordinate several threads if we do that, or how to keep track of the latest successful execution/batches done. You have examples of mediawiki maintanance scripts here: https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/modules/mediawiki/manifests/maintenance/cirrussearch.pp (in this case a cron)

• Marostegui subscribed.Dec 12 2016, 12:12 PM

Agreed too using the jobqueue seems like a good idea to handle splitting in batches for start/end thresholds, handling failed batches, resuming, etc.
re: swift I'm not terribly concerned with parallelism unless we're talking in the ballpark of e.g >80-100 req/s, also does will metadata detection read back the whole file or request a range ?

Dispenser mentioned this in T155741: img_metadata missing.Jan 19 2017, 3:41 PM

matmarex mentioned this in T148606: File does not thumbnail, doesn't have extracted metadata, has reported zero width/height (due to garbage bytes between JPEG sections).Feb 13 2017, 10:11 PM

So next steps are to:

write a maintenance script or adapt current one to queue jobs to the job queue (blocked on dev)
provide to rOPS a crontab to run the script on Terbium (once previous step done, blocked on ops)

provide to rOPS a crontab to run the script on Terbium

Note you only need to be blocked here for deployment, all production crons are handled by puppet. It is really, really easy to send a CR for us, and we will help you. For example, this is how misc backups are handled:

https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/modules/role/manifests/mariadb/backup.pp;d8f2b63993a05c59eb3e2530381039ec886b849e$28

MarcoAurelio added a project: Wikimedia-maintenance-script-run.Mar 15 2017, 12:38 PM

MarcoAurelio removed a parent task: T31782: [DO NOT USE] Maintenance scripts waiting to be run on Wikimedia wikis (tracking) [superseded by #WMF-maintenance-script-run].Mar 15 2017, 12:48 PM

dr0ptp4kt raised the priority of this task from Medium to Needs Triage.May 30 2017, 3:13 PM

dr0ptp4kt moved this task from Next up to Tracking on the Multimedia board.

Not than I understand the details of the discussion ...but here I am as an uploader: if I have files affected with this exif-reading issue and no purging helped, can I give you (some mystical folks with the script) a list of files and hope for the better? Like, I've read a dozen of tickets on the rotation parameters from exif failure in Mediawiki and the corresponding dozen of discussions on Commons village pump... - just where should I go to have a bunch of files fixed, if possible?

According to comments above, it cannot be done until the maintenance script that does it is rewritten. Unfortunately I don't have free time to do that and I can't really justify working on it as part of my WMF job (a lot of other things to work on that are a lot closer to my responsibilities).

Reedy mentioned this in T179760: File metadata gets refreshed when accessing the file description page.Nov 4 2017, 3:15 PM

It may need to be done as part of the Structured Data project. Part of that project could involve copying useful values from Exif fields into a file's data page, and there's not much point in doing that until the corrupt values are fixed.

Meno25 unsubscribed.Nov 23 2018, 8:14 AM

Agabi10 subscribed.Nov 23 2018, 10:14 AM

Reedy updated the task description. (Show Details)Feb 10 2019, 1:30 AM

greg unsubscribed.Feb 12 2019, 11:21 PM

matmarex mentioned this in T231393: Force a refresh of the EXIF metadata of all jpgs in production once new EXIF code lands.Sep 11 2019, 11:11 PM

TheDJ mentioned this in T232779: Make it possible to refresh metadata of files without jumping through crazy hoops.Sep 12 2019, 8:01 PM

Krinkle updated the task description. (Show Details)Sep 13 2019, 12:35 AM