Page MenuHomePhabricator
Create Task
Maniphest T148967

Fix PK on S5 dewiki.revision
Closed, ResolvedPublic
Actions

Description

S5 dewiki revision table needs fixing in its PK and its UNIQUE key like it was done with S4 commonswiki (T147305).
This is the current state (I have excluded both RC slaves)

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts | egrep -v "db2038|db1026|dbstore2001|db1069" | cut -f1 -d " "`; do echo $i; mysql -h$i dewiki -e "show create table revision\G"| egrep "PRIMARY|UNIQUE";done
dbstore2002.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2045.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2052.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2059.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2066.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2023.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
labsdb1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
labsdb1003.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
dbstore1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
dbstore1002.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1045.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1070.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1071.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1082.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1087.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1092.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1049.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),

So the table can look like this:

 *************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159496703 DEFAULT CHARSET=binary

So the following command needs to be executed - I will start with codfw hosts:

#./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id)"

Details

SubjectRepoBranchLines +/-
db-eqiad.php: Depool db1045operations/mediawiki-configmaster+4 -4
db-eqiad.php: Depool db1082operations/mediawiki-configmaster+1 -1
db-eqiad: Depool db1087operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1045operations/mediawiki-configmaster+4 -4
db-eqiad.php: Depool db1087operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1082operations/mediawiki-configmaster+1 -1
db-eqiad.php: Repool db1071operations/mediawiki-configmaster+4 -5
db-eqiad.php: Depool db1071 for maintenanceoperations/mediawiki-configmaster+5 -4
db-eqiad.php: Depool db1070 for maintenanceoperations/mediawiki-configmaster+5 -4
Customize query in gerrit

Related Objects
Search...

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
• Marostegui added a comment.Nov 2 2016, 3:02 PM

ALTER running in db2059

• Marostegui added a comment.Nov 3 2016, 9:34 AM

db2059 finished

MariaDB MARIADB db2059.codfw.wmnet dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159324163 DEFAULT CHARSET=binary
1 row in set (0.04 sec)
Stashbot added a comment.Nov 3 2016, 9:36 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-03T09:36:40Z] <marostegui> Deploy schema change s5 dewiki.revision - only codfw https://phabricator.wikimedia.org/T148967

• Marostegui added a comment.Nov 3 2016, 11:50 AM

ALTER running in db2066

• Marostegui added a comment.Nov 3 2016, 1:18 PM

db2066 is now finished:

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb2066.codfw.wmnet -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159330927 DEFAULT CHARSET=binary

From codfw the only pending server is db2023 which is the master and I will do it Monday morning @jcrespo

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts | grep codf | cut -f1 -d " " | egrep -v "db2038|dbstore*"`; do echo $i; mysql -h$i dewiki -e "show create table revision\G" | grep PRIMARY;done
db2045.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2052.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2059.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2066.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2023.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
#./software/dbtools/osc_host.sh --host=db2023.codfw.wmnet --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page, rev_id)"
• Marostegui added a comment.EditedNov 7 2016, 2:48 PM

I will not do this today, but tomorrow Tuesday as the ALTER of T147305 (db2019 - master) is still running and I do not want to have two masters running long ALTER tables at the same time.

Stashbot added a comment.Nov 8 2016, 7:30 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-08T07:30:03Z] <marostegui> Deploy schema change s5 dewiki.revision on codfw master (db2023) - T148967

• Marostegui added a comment.Nov 8 2016, 7:32 AM

This is now running on db2023

• Marostegui added a comment.Nov 8 2016, 12:12 PM

db2023 is now done

root@neodymium:~# mysql -hdb2023.codfw.wmnet -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159496703 DEFAULT CHARSET=binary
• Marostegui updated the task description. (Show Details)Nov 8 2016, 12:13 PM
• Marostegui added a comment.Nov 8 2016, 12:23 PM

codfw is finished so now eqiad needs fixing, the following hosts needs to get the right PK and the right indexes:

db1069.eqiad.wmnet
PRIMARY KEY (`rev_page`,`rev_id`),
labsdb1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
labsdb1003.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
dbstore1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
dbstore1002.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1045.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1070.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1071.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1082.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1087.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1092.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1049.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),

So the alter to run is:

#./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page, rev_id)"
Stashbot added a comment.Nov 24 2016, 8:59 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-24T08:59:46Z] <marostegui> Deploy alter table S5 - dewiki.revision on db1092 (depooled) - T148967

• Marostegui added a comment.Nov 24 2016, 9:03 AM

Running ALTER table on db1092 which is currently depooled due to: T151272

• Marostegui added a comment.Nov 24 2016, 10:30 AM

db1092 is done:

MariaDB PRODUCTION s5 localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160012432 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 28 2016, 7:42 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-28T07:42:46Z] <marostegui> Deploying alter table s5 dewiki.revision - T148967

• Marostegui added a comment.Nov 28 2016, 7:48 AM

ALTER running on db1069 s5 instance.

• Marostegui added a comment.Nov 28 2016, 3:55 PM
In T148967#2826356, @Marostegui wrote:

ALTER running on db1069 s5 instance.

This was accidentally killed - after talking to @jcrespo we decided that it is really not worth the time, but we will do it on: labsdb1009/10/11 instead of db1069 and labsdb1001 and 1003

Stashbot added a comment.Nov 29 2016, 2:47 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-29T14:47:26Z] <marostegui> Deploye alter table dbstore1001 - dewiki.revision - T148967

• Marostegui added a comment.Nov 29 2016, 2:54 PM

Alter is running on dbstore1001

Stashbot added a comment.Nov 30 2016, 7:17 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-30T07:17:32Z] <marostegui> Deploy alter table dbstore1002 - dewiki.revision - T148967

• Marostegui added a comment.Nov 30 2016, 7:20 AM

dbstore1001 is done

root@neodymium:~# mysql -hdbstore1001 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=TokuDB AUTO_INCREMENT=160178427 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'

Now running alter on dbstore1002

• Marostegui added a comment.Nov 30 2016, 4:36 PM

dbstore1002 is done

MariaDB DBSTORE localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=TokuDB AUTO_INCREMENT=160214633 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'
gerritbot subscribed.Dec 1 2016, 6:35 AM

Change 324658 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1070 for maintenance

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

gerritbot added a project: Patch-For-Review.Dec 1 2016, 6:35 AM
gerritbot added a comment.Dec 1 2016, 7:02 AM

Change 324658 merged by jenkins-bot:
db-eqiad.php: Depool db1070 for maintenance

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

Stashbot added a comment.Dec 1 2016, 7:05 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:05:52Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db1070 - T148967 (duration: 02m 31s)

Stashbot added a comment.Dec 1 2016, 7:06 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:06:56Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1070 - T148967 (duration: 00m 48s)

Stashbot added a comment.Dec 1 2016, 7:32 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:32:42Z] <marostegui> Deploy alter table db1070 - dewiki.revision - T148967

• Marostegui added a comment.Dec 1 2016, 7:33 AM

Alter running on db1070

• Marostegui added a comment.Dec 1 2016, 1:52 PM

db1070 is done

MariaDB PRODUCTION s5 localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160255973 DEFAULT CHARSET=binary
Stashbot added a comment.Dec 1 2016, 1:57 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-01T13:57:10Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1070 - T148967 (duration: 00m 45s)

gerritbot added a comment.Dec 2 2016, 7:44 AM

Change 324861 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1071 for maintenance

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

gerritbot added a comment.Dec 2 2016, 7:48 AM

Change 324861 merged by jenkins-bot:
db-eqiad.php: Depool db1071 for maintenance

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

Stashbot added a comment.Dec 2 2016, 7:51 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-02T07:51:56Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1071 - T148967 (duration: 02m 22s)

Stashbot added a comment.Dec 2 2016, 7:59 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-02T07:59:51Z] <marostegui> Deploy alter table db1071 - dewiki.revision - T148967

• Marostegui added a comment.Dec 2 2016, 11:05 AM

db1071 is done:

root@neodymium:~# mysql -hdb1071 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160278947 DEFAULT CHARSET=binary
gerritbot added a comment.Dec 2 2016, 11:08 AM

Change 324881 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Repool db1071

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

gerritbot added a comment.Dec 2 2016, 11:09 AM

Change 324881 merged by jenkins-bot:
db-eqiad.php: Repool db1071

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

Stashbot added a comment.Dec 2 2016, 11:10 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-02T11:10:46Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1071 - T148967 (duration: 00m 45s)

Stashbot added a comment.Dec 2 2016, 11:21 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-02T11:21:39Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1071 - T148967 (duration: 00m 44s)

gerritbot added a comment.Dec 5 2016, 7:40 AM

Change 325260 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1082

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

gerritbot added a comment.Dec 5 2016, 7:42 AM

Change 325260 merged by jenkins-bot:
db-eqiad.php: Depool db1082

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

Stashbot added a comment.Dec 5 2016, 7:45 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T07:45:20Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1082 - T148967 (duration: 02m 12s)

Stashbot added a comment.Dec 5 2016, 7:48 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T07:48:12Z] <marostegui> Deploy alter table db1082 - dewiki.revision - T148967

• Marostegui added a comment.Dec 5 2016, 7:51 AM

This is running on db1082

• Marostegui added a comment.Dec 5 2016, 10:52 AM

db1082 is done

root@neodymium:~# mysql -hdb1082 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160358943 DEFAULT CHARSET=binary
Stashbot added a comment.Dec 5 2016, 10:58 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T10:58:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1082 - T148967 (duration: 00m 57s)

gerritbot added a comment.Dec 5 2016, 1:19 PM

Change 325294 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1087

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

gerritbot added a comment.Dec 5 2016, 1:23 PM

Change 325294 merged by jenkins-bot:
db-eqiad.php: Depool db1087

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

Stashbot added a comment.Dec 5 2016, 1:42 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T13:42:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T148967 (duration: 00m 44s)

Stashbot added a comment.Dec 5 2016, 1:48 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T13:48:22Z] <marostegui> Deploy alter table db1087 - dewiki.revision - T148967

• Marostegui added a comment.Dec 5 2016, 4:01 PM

db1087 is done:

root@neodymium:~# mysql -hdb1087 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160367851 DEFAULT CHARSET=binary
Stashbot added a comment.Dec 5 2016, 4:07 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T16:07:07Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 (duration: 00m 59s)

Stashbot added a comment.Dec 5 2016, 4:08 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-05T16:08:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 (duration: 00m 49s)

gerritbot added a comment.Dec 7 2016, 8:14 AM

Change 325745 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1045

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

gerritbot added a comment.Dec 7 2016, 8:29 AM

Change 325745 merged by jenkins-bot:
db-eqiad.php: Depool db1045

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

Stashbot added a comment.Dec 7 2016, 8:33 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-07T08:32:58Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1045 - T148967 (duration: 02m 10s)

Stashbot added a comment.Dec 7 2016, 8:58 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-07T08:58:44Z] <marostegui> Deploy ALTER table db1045 dewiki.revision - T148967

• Marostegui added a comment.Dec 7 2016, 12:55 PM

db1045 is done

root@neodymium:~# mysql -hdb1045 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160424430 DEFAULT CHARSET=binary
Stashbot added a comment.Dec 7 2016, 12:59 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-07T12:59:51Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1045 - T148967 (duration: 00m 56s)

• Marostegui added a comment.Dec 7 2016, 1:02 PM

The only pending host is db1049 (eqiad master)

Stashbot added a comment.Dec 12 2016, 9:05 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-12T09:05:51Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - T148967

• Marostegui added a comment.Dec 12 2016, 9:19 AM

I am about to run the following ALTER on db1049, which is the master:

./software/dbtools/osc_host.sh --host=db1049 --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key rev_page_id (rev_page, rev_id)"

Even though with the slaves I have seen it is an ONLINE operation and caused no lag, I have double checked MySQL doc and it indeed says that adding/dropping a PK can be done INPLACE

• Marostegui added a comment.Dec 12 2016, 2:06 PM

I will be running the above ALTER tomorrow morning around 7:45-8AM (UTC+1) as looks like it is the best time to avoid long running slow queries.

Stashbot added a comment.Dec 13 2016, 7:06 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T07:06:23Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - https://phabricator.wikimedia.org/T148967

• Marostegui added a comment.Dec 13 2016, 7:15 AM

This was started around 10 minutes ago.

• Marostegui added a comment.Dec 13 2016, 12:16 PM

The master db1049 is done

root@neodymium:/home/marostegui/git/software# mysql -hdb1049 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160601168 DEFAULT CHARSET=binary
gerritbot added a comment.Dec 13 2016, 12:22 PM

Change 326926 had a related patch set uploaded (by Marostegui):
db-eqiad: Depool db1087

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

gerritbot added a comment.Dec 13 2016, 12:25 PM

Change 326926 merged by jenkins-bot:
db-eqiad: Depool db1087

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

Stashbot added a comment.Dec 13 2016, 12:27 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:27:15Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T148967 (duration: 00m 46s)

Stashbot added a comment.Dec 13 2016, 12:27 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:27:52Z] <marostegui> Deploy alter table db1087 dewiki.revision - T148967

• Marostegui added a comment.Dec 13 2016, 12:29 PM

The following server are missing the page_user_timestamp index:

db1045
db1082
db1087
db1049 (master)

I am currently altering db1087

./software/dbtools/osc_host.sh --host=db1087 --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp)"
• Marostegui added a comment.Dec 13 2016, 12:38 PM

db1087 is done.

Stashbot added a comment.Dec 13 2016, 12:56 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:56:00Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 and T69223 (duration: 00m 47s)

Stashbot mentioned this in T69223: Schema change for page content language.Dec 13 2016, 12:56 PM
gerritbot added a comment.Dec 13 2016, 12:58 PM

Change 326931 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1082

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

gerritbot added a comment.Dec 13 2016, 1:01 PM

Change 326931 merged by jenkins-bot:
db-eqiad.php: Depool db1082

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

Stashbot added a comment.Dec 13 2016, 1:02 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:02:30Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1082 - T148967 and T69223 (duration: 00m 45s)

Stashbot added a comment.Dec 13 2016, 1:03 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:03:00Z] <marostegui> Deploy alter table db1082 dewiki.revision - T148967

• Marostegui added a comment.Dec 13 2016, 1:16 PM

db1082 is done

Stashbot added a comment.Dec 13 2016, 1:29 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:29:28Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1082 - T148967 and T69223 (duration: 00m 45s)

gerritbot added a comment.Dec 13 2016, 1:33 PM

Change 326935 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1045

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

hoo mentioned this in T85342: Wikimedia sites frequently switching to read-only.Dec 13 2016, 1:35 PM
gerritbot added a comment.Dec 13 2016, 1:37 PM

Change 326935 merged by jenkins-bot:
db-eqiad.php: Depool db1045

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

Stashbot added a comment.Dec 13 2016, 1:38 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:38:55Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1045 - T148967 (duration: 00m 45s)

Stashbot added a comment.Dec 13 2016, 1:41 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:41:37Z] <marostegui> Deploy alter table db1045 dewiki.revision - T148967

• Marostegui added a comment.Dec 13 2016, 2:08 PM

db1045 is done
The only pending one is the master - will be done tomorrow morning.

Stashbot added a comment.Dec 13 2016, 4:19 PM

Mentioned in SAL (#wikimedia-operations) [2016-12-13T16:19:39Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1045 - T148967 (duration: 00m 46s)

Stashbot added a comment.Dec 15 2016, 7:08 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-15T07:08:05Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - T148967

• Marostegui added a comment.Dec 15 2016, 7:44 AM

db1049 (master is done)

root@neodymium:~# mysql -hdb1049 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
• Marostegui added a comment.Dec 15 2016, 7:46 AM

dewiki.revision is now unified. We agreed on not doing labs/db1069 servers so I believe this is ready to be closed.

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts  | awk -F " " '{print $1}' | egrep -v "db2038|db1069|db1026"`; do echo $i; mysql -h$i -A dewiki -e "show create table revision\G" | grep KEY | grep -v COMPR | wc -l;done
cat:  : No such file or directory
dbstore2001.codfw.wmnet
7
dbstore2002.codfw.wmnet
7
db2045.codfw.wmnet
7
db2052.codfw.wmnet
7
db2059.codfw.wmnet
7
db2066.codfw.wmnet
7
db2023.codfw.wmnet
7
labsdb1001.eqiad.wmnet
6
labsdb1003.eqiad.wmnet
6
dbstore1001.eqiad.wmnet
7
dbstore1002.eqiad.wmnet
7
db1045.eqiad.wmnet
7
db1070.eqiad.wmnet
7
db1071.eqiad.wmnet
7
db1082.eqiad.wmnet
7
db1087.eqiad.wmnet
7
db1092.eqiad.wmnet
7
db1049.eqiad.wmnet
7
• Marostegui closed this task as Resolved.Dec 15 2016, 7:46 AM
Anomie mentioned this in T102532: revision page_user_timestamp index problematic on large wikis.Jan 5 2017, 2:55 PM
• Marostegui mentioned this in T108968: prop=revisions with rvdir=newer gives internal_api_error_DBQueryError for page with many version.Feb 6 2017, 7:46 AM
• Marostegui mentioned this in T158454: enwiki database on db1065 missing rev_timestamp index.Feb 20 2017, 7:07 AM
• Marostegui added a parent task: T142725: Clean up revision UNIQUE indexes.Jun 2 2017, 9:53 AM
Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:40 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL