Page MenuHomePhabricator

Database replica drift on web and analytics clusters
Closed, ResolvedPublic

Description

Found via Zombie images database report:

$ sql --cluster web commonswiki 'SELECT 1 FROM commonswiki_p.image WHERE img_name = "Stuttgart_train_station..jpg"'
+---+
| 1 |
+---+
| 1 |
+---+

This should be empty as in production.

Update from T179767#3735491
Same for the other zombie file:

SELECT * FROM image WHERE img_name = "Is-sur-Tille_Motocross_finale_du_championnat_de_France_féminin_2015_-_Erell_BOULOUARD-12.jpg"\G

Event Timeline

Dispenser created this task.Nov 4 2017, 5:57 PM
Marostegui moved this task from Triage to Next on the DBA board.EditedNov 4 2017, 6:28 PM

I have checked and it is a valid drift (it is present on sanitarium and all the labs hosts). I guess this comes from the marathon of fixing image table that Jaime did (T162593#3599903), and he was able to fix most of the issues on most of the servers, which is a tremendous effort, but this is an easy one that could have slipped thru.

It should be an easy one to fix, just a delete on sanitarium and the labs hosts.

From https://commons.wikimedia.org/w/index.php?title=Special%3ALog&page=File%3AStuttgart+train+station..jpg:

13:41, 4 August 2015 1Veertje (talk | contribs) moved page File:Stuttgart train station..jpg to File:Stuttgart train station.jpg without leaving a redirect

13:40, 4 August 2015 1Veertje (talk | contribs) uploaded File:Stuttgart train station..jpg (User created page with UploadWizard)
MariaDB [commonswiki_p]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1003 |
+------------+
1 row in set (0.00 sec)

MariaDB [commonswiki_p]> SELECT * FROM commonswiki_p.image WHERE img_name = "Stuttgart_train_station..jpg"\G
Empty set (0.01 sec)
MariaDB [commonswiki_p]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1011 |
+------------+
1 row in set (0.00 sec)

MariaDB [commonswiki_p]> SELECT * FROM commonswiki_p.image WHERE img_name = "Stuttgart_train_station..jpg"\G
*************************** 1. row ***************************
       img_name: Stuttgart_train_station..jpg
       img_size: 2439680
      img_width: 3744
     img_height: 5616
   img_metadata: a:40:{s:4:"Make";s:5:"Canon";s:5:"Model";s:20:"Canon EOS 5D Mark II";s:11:"XResolution";s:5:"240/1";s:11:"YResolution";s:5:"240/1";s:14:"ResolutionUnit";i:2;s:8:"Software";s:39:"Adobe Photoshop Lightroom 4.4 (Windows)";s:8:"DateTime";s:19:"2015:08:03 10:17:09";s:6:"Artist";s:12:"Jeroen Komen";s:12:"ExposureTime";s:5:"1/250";s:7:"FNumber";s:5:"56/10";s:15:"ExposureProgram";i:2;s:15:"ISOSpeedRatings";i:100;s:11:"ExifVersion";s:4:"0230";s:16:"DateTimeOriginal";s:19:"2013:09:14 15:14:58";s:17:"DateTimeDigitized";s:19:"2013:09:14 15:14:58";s:17:"ShutterSpeedValue";s:15:"7965784/1000000";s:13:"ApertureValue";s:15:"4970854/1000000";s:17:"ExposureBiasValue";s:3:"0/1";s:16:"MaxApertureValue";s:3:"3/1";s:12:"MeteringMode";i:5;s:5:"Flash";i:16;s:11:"FocalLength";s:4:"75/1";s:18:"SubSecTimeOriginal";s:2:"17";s:19:"SubSecTimeDigitized";s:2:"17";s:21:"FocalPlaneXResolution";s:12:"5616000/1459";s:21:"FocalPlaneYResolution";s:11:"3744000/958";s:24:"FocalPlaneResolutionUnit";i:2;s:14:"CustomRendered";i:0;s:12:"ExposureMode";i:0;s:12:"WhiteBalance";i:0;s:16:"SceneCaptureType";i:0;s:12:"SerialNumber";s:10:"2231305136";s:4:"Lens";s:24:"EF70-200mm f/2.8L IS USM";s:6:"Rating";s:1:"4";s:16:"DateTimeMetadata";s:19:"2015:08:03 12:17:09";s:18:"OriginalDocumentID";s:32:"D3FA253D96F1263BEE2DF4D0543405E8";s:10:"ObjectName";a:2:{s:9:"x-default";s:109:"Stuttgart train station. Billions are spent to  convert this head station into an underground transit station";s:5:"_type";s:4:"lang";}s:8:"Keywords";a:4:{i:0;s:6:"Flickr";i:1;s:7:"ManMade";i:2;s:8:"StGallen";s:5:"_type";s:2:"ul";}s:10:"iimVersion";i:4;s:22:"MEDIAWIKI_EXIF_VERSION";i:2;}
       img_bits: 8
 img_media_type: BITMAP
 img_major_mime: image
 img_minor_mime: jpeg
img_description: User created page with UploadWizard
       img_user: 1162075
  img_user_text: 1Veertje
  img_timestamp: 20150804134055
       img_sha1: bbsftqai5vjxkuoqqk2xhgnnvce5fbw
    img_deleted: 0
1 row in set (0.00 sec)

From https://commons.wikimedia.org/w/index.php?title=Special%3ALog&page=File%3AStuttgart+train+station..jpg:

13:41, 4 August 2015 1Veertje (talk | contribs) moved page File:Stuttgart train station..jpg to File:Stuttgart train station.jpg without leaving a redirect
13:40, 4 August 2015 1Veertje (talk | contribs) uploaded File:Stuttgart train station..jpg (User created page with UploadWizard)

Yep File:Stuttgart train station.jpg does exist in production (and on labs). But File:Stuttgart train station..jpg does not exist in production and it does exists on sanitarium+labs

Marostegui added a comment.EditedNov 4 2017, 6:34 PM
MariaDB [commonswiki_p]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1003 |
+------------+
1 row in set (0.00 sec)
MariaDB [commonswiki_p]> SELECT * FROM commonswiki_p.image WHERE img_name = "Stuttgart_train_station..jpg"\G
Empty set (0.01 sec)
MariaDB [commonswiki_p]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1011 |
+------------+
1 row in set (0.00 sec)
MariaDB [commonswiki_p]> SELECT * FROM commonswiki_p.image WHERE img_name = "Stuttgart_train_station..jpg"\G
*************************** 1. row ***************************
       img_name: Stuttgart_train_station..jpg
       img_size: 2439680
      img_width: 3744
     img_height: 5616
   img_metadata: a:40:{s:4:"Make";s:5:"Canon";s:5:"Model";s:20:"Canon EOS 5D Mark II";s:11:"XResolution";s:5:"240/1";s:11:"YResolution";s:5:"240/1";s:14:"ResolutionUnit";i:2;s:8:"Software";s:39:"Adobe Photoshop Lightroom 4.4 (Windows)";s:8:"DateTime";s:19:"2015:08:03 10:17:09";s:6:"Artist";s:12:"Jeroen Komen";s:12:"ExposureTime";s:5:"1/250";s:7:"FNumber";s:5:"56/10";s:15:"ExposureProgram";i:2;s:15:"ISOSpeedRatings";i:100;s:11:"ExifVersion";s:4:"0230";s:16:"DateTimeOriginal";s:19:"2013:09:14 15:14:58";s:17:"DateTimeDigitized";s:19:"2013:09:14 15:14:58";s:17:"ShutterSpeedValue";s:15:"7965784/1000000";s:13:"ApertureValue";s:15:"4970854/1000000";s:17:"ExposureBiasValue";s:3:"0/1";s:16:"MaxApertureValue";s:3:"3/1";s:12:"MeteringMode";i:5;s:5:"Flash";i:16;s:11:"FocalLength";s:4:"75/1";s:18:"SubSecTimeOriginal";s:2:"17";s:19:"SubSecTimeDigitized";s:2:"17";s:21:"FocalPlaneXResolution";s:12:"5616000/1459";s:21:"FocalPlaneYResolution";s:11:"3744000/958";s:24:"FocalPlaneResolutionUnit";i:2;s:14:"CustomRendered";i:0;s:12:"ExposureMode";i:0;s:12:"WhiteBalance";i:0;s:16:"SceneCaptureType";i:0;s:12:"SerialNumber";s:10:"2231305136";s:4:"Lens";s:24:"EF70-200mm f/2.8L IS USM";s:6:"Rating";s:1:"4";s:16:"DateTimeMetadata";s:19:"2015:08:03 12:17:09";s:18:"OriginalDocumentID";s:32:"D3FA253D96F1263BEE2DF4D0543405E8";s:10:"ObjectName";a:2:{s:9:"x-default";s:109:"Stuttgart train station. Billions are spent to  convert this head station into an underground transit station";s:5:"_type";s:4:"lang";}s:8:"Keywords";a:4:{i:0;s:6:"Flickr";i:1;s:7:"ManMade";i:2;s:8:"StGallen";s:5:"_type";s:2:"ul";}s:10:"iimVersion";i:4;s:22:"MEDIAWIKI_EXIF_VERSION";i:2;}
       img_bits: 8
 img_media_type: BITMAP
 img_major_mime: image
 img_minor_mime: jpeg
img_description: User created page with UploadWizard
       img_user: 1162075
  img_user_text: 1Veertje
  img_timestamp: 20150804134055
       img_sha1: bbsftqai5vjxkuoqqk2xhgnnvce5fbw
    img_deleted: 0
1 row in set (0.00 sec)

Yeah, as I said on T179767#3735453, this is probably a leftover from the crazy work of fixing consistency across image table across all the s4 production servers done on: T162593
Deleting that row on the sanitarium host and let it replicate should fix it as the correct image is already there.

Same thing happens with the other zombie file:

SELECT * FROM image WHERE img_name = "Is-sur-Tille_Motocross_finale_du_championnat_de_France_féminin_2015_-_Erell_BOULOUARD-12.jpg"\G

Doesn't exist on production but exists on sanitarium+labs, again as a left over probably from inconsistencies that existed on production before these hosts were built and production was fixed.

Marostegui updated the task description. (Show Details)Nov 4 2017, 7:10 PM
Marostegui updated the task description. (Show Details)

I have backuped those two rows just in case on the sanitarium host:

root@db1102:~/T179767# pwd
/root/T179767
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Nov 4 2017, 11:59 PM
Marostegui closed this task as Resolved.Nov 6 2017, 8:33 AM
Marostegui claimed this task.

I have fixed this, thanks again for reporting it.