*_minor_mime are varbinary(32) on WMF sites, out of sync with varbinary(100) in MW core
Open, NormalPublic

Description

looking at [[foundation:Special:MediaStatistics]]

Mime types like application/vnd.oasis.opendocument.spreadsheet get cut off to application/vnd.oasis.opendocument.spreadshe because img_minor_mime field isn't long enough.


Version: unspecified
Severity: normal

Details

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

Mentioned in SAL [2016-07-05T12:08:12Z] <jynus> running schema change on db1019 T73563

Hi,

The current status of this change in s4 (commonswiki):

filearchive table - all of them have: fa_minor_mime varbinary(100) DEFAULT 'unknown', - Nothing to do here.

image table - they are all: img_minor_mime varbinary(32) NOT NULL DEFAULT 'unknown', - OSC needed with: MODIFY COLUMN img_minor_mime varbinary(100) NOT NULL default "unknown";

oldimage table - they are all: oi_minor_mime varbinary(32) NOT NULL DEFAULT 'unknown', - OSC needed with: MODIFY COLUMN oi_minor_mime varbinary(100) NOT NULL default "unknown";

Given that I need to depool the servers for: T160415 I will also apply those changes too

jcrespo changed the task status from "Stalled" to "Open".
jcrespo moved this task from Backlog to In progress on the DBA board.

Change 343081 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2065

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

Starting with the image table first on: dbstore2001 and db2065

Change 343081 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2065

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

Mentioned in SAL (#wikimedia-operations) [2017-03-16T16:32:51Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2065 - T160415 - T73563 (duration: 00m 42s)

db2065 and dbstore2001 are done:

root@neodymium:~# mysql --skip-ssl -hdb2065.codfw.wmnet commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

root@neodymium:~# mysql --skip-ssl -hdbstore2001.codfw.wmnet commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Mentioned in SAL (#wikimedia-operations) [2017-03-17T08:00:16Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2065 - T160415 - T73563 (duration: 00m 44s)

Change 343255 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2058

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

Change 343255 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2058

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

Mentioned in SAL (#wikimedia-operations) [2017-03-17T08:31:53Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2058 - T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-17T08:32:07Z] <marostegui> Deploy schema change on dbstore2002 and db2058 (s4) - T160415 T73563

dbstore2002 and db2058 are done:

root@neodymium:~# for i in db2058 dbstore2002; do echo $i; mysql --skip-ssl -h$i.codfw.wmnet commonswiki -e "show create table image\G show create table oldimage\G" | grep 100 ;done
db2058
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
dbstore2002
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Mentioned in SAL (#wikimedia-operations) [2017-03-17T13:39:08Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2058 - T160415 - T73563 (duration: 01m 06s)

Change 343303 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2051

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

Change 343303 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2051

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

Mentioned in SAL (#wikimedia-operations) [2017-03-17T14:00:54Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2051 - T160415 - T73563 (duration: 00m 42s)

Mentioned in SAL (#wikimedia-operations) [2017-03-17T14:01:37Z] <marostegui> Deploy schema change on dbstore1001 and db2051 (s4) - T160415 - T73563

Mentioned in SAL (#wikimedia-operations) [2017-03-21T06:49:27Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2051 - T160415 - T73563 (duration: 01m 07s)

db2051 and dbstore1001 are done

root@neodymium:~# for i in db2051.codfw.wmnet dbstore1001.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G show create table oldimage\G" | grep 100 ;done
db2051.codfw.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
dbstore1001.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 343823 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2044

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

Change 343823 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2044

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

Mentioned in SAL (#wikimedia-operations) [2017-03-21T07:18:02Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2044 - T160415 - T73563 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-03-21T07:18:09Z] <marostegui> Deploy schema change on db2044 and labsdb1009 (s4) - https://phabricator.wikimedia.org/T160415 - https://phabricator.wikimedia.org/T73563

db2044 and labsdb1009 are done:

root@neodymium:/home/marostegui/databases_s6# for i in db2044.codfw.wmnet labsdb1009.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G show create table oldimage\G" | grep 100 ;done
db2044.codfw.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
labsdb1009.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

I am investigating why labsdb1009's replication is complaining about this

Last_SQL_Error: Column 8 of table 'commonswiki.image' cannot be converted from type 'varchar(32)' to type 'varbinary(100)'
root@labsdb1009[commonswiki]> show create table image\G
*************************** 1. row ***************************
       Table: image
Create Table: CREATE TABLE `image` (
  `img_name` varbinary(255) NOT NULL DEFAULT '',
  `img_size` int(8) unsigned NOT NULL DEFAULT '0',
  `img_width` int(5) NOT NULL DEFAULT '0',
  `img_height` int(5) NOT NULL DEFAULT '0',
  `img_metadata` mediumblob NOT NULL,
  `img_bits` int(3) NOT NULL DEFAULT '0',
  `img_media_type` enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE') DEFAULT NULL,
  `img_major_mime` enum('unknown','application','audio','image','text','video','message','model','multipart') NOT NULL DEFAULT 'unknown',
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `img_description` tinyblob NOT NULL,
  `img_user` int(5) unsigned NOT NULL DEFAULT '0',
  `img_user_text` varbinary(255) NOT NULL DEFAULT '',
  `img_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `img_sha1` varbinary(32) NOT NULL DEFAULT '',
  `img_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`img_name`),
  KEY `img_size` (`img_size`),
  KEY `img_timestamp` (`img_timestamp`),
  KEY `img_usertext_timestamp` (`img_user_text`,`img_timestamp`),
  KEY `img_sha1` (`img_sha1`),
  KEY `img_media_mime` (`img_media_type`,`img_major_mime`,`img_minor_mime`),
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
jcrespo added a comment.EditedTue, Mar 21, 12:28 PM

Oh, yes, that is binlog row being pendantic. There may be a temporary way to disable that behaviour. Let's start by converting all masters and slaves, we will see how to handle it- worse case scenario, we can do the changes manually from the statement binlog.

This might be the thing to play with: https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-features-different-data-types.html

mysql:root@localhost [(none)]> show global variables like '%slave_type%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_type_conversions |       |
+------------------------+-------+
1 row in set (0.01 sec)

Yes, that looks like it- I would do a quick test to confirm it works well- then apply it (either temporarily or permanently).

It could also be our fault- the mime may be only internally a varchar, and we may be explicitly converting it- causing issues.

Change 343867 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Repool db2044, depool db2037

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

Change 343867 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Repool db2044, depool db2037

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

Mentioned in SAL (#wikimedia-operations) [2017-03-21T14:01:55Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2044, depool db2037 T160415 - T73563 (duration: 00m 42s)

I have reproduced the error on a test vm and looks like the following mode fixes it. And the information looks sane and not broken:

set global slave_type_conversions = "ALL_NON_LOSSY";

From the doc:

This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.

Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error.

+1 to deploy it.

Change 343879 had a related patch set uploaded (by Marostegui):
[operations/puppet] labsdb-replica.my.cnf: Set slave_type_conversions

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

Change 343879 merged by Marostegui:
[operations/puppet] labsdb-replica.my.cnf: Set slave_type_conversions

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

Change 344081 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Repool db2037

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

db2037 is done:

root@neodymium:~# mysql --skip-ssl -hdb2037.codfw.wmnet commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344081 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Repool db2037

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T06:43:28Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2037 T160415 - T73563 (duration: 00m 43s)

Change 344086 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Depool db1091

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

Change 344087 had a related patch set uploaded (by Marostegui):
[operations/puppet] sanitarium2.my.cnf: Set slave_type_conversions

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

Change 344086 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Depool db1091

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T07:40:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1091 T160415 - T73563 (duration: 00m 43s)

codfw is only pending the master. I will do it once I am done with eqiad hosts (which I have started already). It will generate lag on the whole codfw datacenter for 2-3 hours the alter lasts on not so powerful servers.

Anyways, db1091 is done now:

root@neodymium:~# mysql --skip-ssl -hdb1091 commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:29:47Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1091 T160415 - T73563 (duration: 00m 43s)

Change 344094 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Depool db1084

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

Change 344094 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Depool db1084

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:39:40Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1084 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:40:05Z] <marostegui> Deploy alter table s4 (commonswiki) db1084 - T73563 T160415

Change 344122 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Repool db1084,depool db1081

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

Change 344087 merged by Marostegui:
[operations/puppet] sanitarium2.my.cnf: Set slave_type_conversions

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

Change 344122 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Repool db1084,depool db1081

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T12:23:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1084, depool db1081 T160415 - T73563 (duration: 00m 43s)

db1084 and labsdb1010 are done:

root@neodymium:~# for i in  labsdb1010.eqiad.wmnet db1084.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G show create table oldimage\G" | grep 100 ;done
labsdb1010.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
db1084.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Mentioned in SAL (#wikimedia-operations) [2017-03-22T12:26:37Z] <marostegui> Deploy schema change on s4 to db1081 and labsdb1011 - T160415 T73563

Change 344147 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Repool db1081, depool db1068

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

db1081 is done:

root@neodymium:~# mysql --skip-ssl -hdb1081 commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344147 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Repool db1081, depool db1068

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T14:18:52Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1081, depool db1068 T160415 - T73563 (duration: 00m 43s)

Change 344308 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1059, repool db1068

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

db1068 and labsdb1011 are done:

root@neodymium:~# for i in  labsdb1011.eqiad.wmnet db1068.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G show create table oldimage\G" | grep 100 ;done
labsdb1011.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
db1068.eqiad.wmnet
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344308 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1059, repool db1068

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

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:36:05Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1068, depool db1059 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:37:53Z] <marostegui> Deploy schema change s4 on db1068 and labsdb1001 T160415 - T73563

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:37:53Z] <marostegui> Deploy schema change s4 on db1068 and labsdb1001 T160415 - T73563

s/db1068/db1059

db1059 and labsdb1001 are done:

root@neodymium:~# mysql --skip-ssl -hdb1059 commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

[root@labsdb1001 12:10 /root]
# mysql --skip-ssl commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344362 had a related patch set uploaded (by Marostegui):
[operations/puppet@production] labs.my.cnf: Set slave_type_conversions

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

Change 344362 merged by Marostegui:
[operations/puppet@production] labs.my.cnf: Set slave_type_conversions

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

Change 344365 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1064, repool db1059

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

Change 344365 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1064, repool db1059

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

Mentioned in SAL (#wikimedia-operations) [2017-03-23T12:58:26Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1059, depool db1064 T160415 - T73563 (duration: 00m 43s)

labsdb1003 is done:

[root@labsdb1003 15:44 /root]
# mysql --skip-ssl commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

db1095 and db1064 are done:

root@neodymium:~# for i in db1095 db1064; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G show create table oldimage\G" | grep 100; done
db1095
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
db1064
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344579 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Change 344579 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Mentioned in SAL (#wikimedia-operations) [2017-03-24T07:48:22Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1056, repool db1059 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-24T07:49:05Z] <marostegui> Deploy schema change s4 on db1069 and db1056 - T160415 - T73563

Change 344586 had a related patch set uploaded (by Marostegui):
[operations/puppet@production] sanitarium.my.cnf: Set slave_type_conversions

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

Change 344586 merged by Marostegui:
[operations/puppet@production] sanitarium.my.cnf: Set slave_type_conversions

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

db1056 and db1069 are done:

root@neodymium:/home/marostegui/databases_s5# mysql --skip-ssl -hdb1056 commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

root@neodymium:~# mysql --skip-ssl -hdb1069 -P3314 commonswiki -e "show create table image\G show create table oldimage\G" | grep 100
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `oi_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',

Change 344605 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Repool db1056

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

Change 344605 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Repool db1056

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

Mentioned in SAL (#wikimedia-operations) [2017-03-24T12:30:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1056 T160415 - T73563 (duration: 00m 44s)