Page MenuHomePhabricator

Apply change_tag and tag_summary primary key schema change to Wikimedia wikis
Closed, ResolvedPublic

Description

-- Primary key in tag_summary table

ALTER TABLE /*$wgDBprefix*/tag_summary
	ADD COLUMN ts_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
	ADD PRIMARY KEY (ts_id);
-- Primary key in change_tag table

ALTER TABLE /*$wgDBprefix*/change_tag
	ADD COLUMN ct_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
	ADD PRIMARY KEY (ct_id);
  • Where: All wikis
  • When: Any time, not dependent upon any code changes
  • Tested on beta: Once the patch is merged, it will automatically be applied to all beta cluster databases
  • No private data: New columns are all auto increment ids, and have no private data

Status as of 2017-04-27: Done on eqiad, pending on codfw

  • Status in codfw as of 2017-05-09
  • s1
  • s2
  • s3
  • s4
  • s5
  • s6
  • s7
  • silver
  • labtestweb2001
  • Check masters and hosts on both DCs as a final check

Details

Related Gerrit Patches:
operations/mediawiki-config : masterdb-eqiad.php: Depool db1067
operations/mediawiki-config : masterdb-eqiad.php: Depool db1097

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
jcrespo moved this task from Triage to Backlog on the DBA board.
jcrespo raised the priority of this task from Medium to High.Oct 19 2016, 9:08 AM

change_tag is causing issues T146261#2727832, I believe this schema change could mitigate the issue, directly or indirectly (because it will recreate it).

I can start with this on S1 in codfw if you agree @jcrespo

Mentioned in SAL (#wikimedia-operations) [2016-10-19T11:55:31Z] <marostegui> Deploying schema change db2055 - S1 enwiki.change_tag - T147166

Marostegui added a comment.EditedOct 19 2016, 12:13 PM

I have changed those two tables on S1.enwiki - db2055.codfw.wmnet

MariaDB PRODUCTION s1 localhost enwiki > show create table tag_summary\G show create table change_tag\G
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16672623 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23675689 DEFAULT CHARSET=binary
1 row in set (0.00 sec)
Marostegui moved this task from Backlog to In progress on the DBA board.Oct 20 2016, 11:22 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-24T07:09:10Z] <marosteg1i> Deploying alter table s1.enwiki on codfw - T147166

I have applied this change to another host on S1.enwiki codfw - db2062:

MariaDB PRODUCTION s1 localhost enwiki > show create table tag_summary\G show create table change_tag\G
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16758597 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23820662 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

This was executed:

./software/dbtools/osc_host.sh --host=db2062.codfw.wmnet --port=3306 --db=enwiki --table=tag_summary --method=ddl --no-replicate "ADD COLUMN ts_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (ts_id);"

./software/dbtools/osc_host.sh --host=db2062.codfw.wmnet --port=3306 --db=enwiki --table=change_tag --method=ddl --no-replicate "ADD COLUMN ct_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (ct_id);"

Mentioned in SAL (#wikimedia-operations) [2016-10-27T07:24:48Z] <marostegui> Deploying schema change db2034- enwiki.change_tag/tag_summary - T147166

db2034:

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb2034.codfw.wmnet enwiki -e "show create table tag_summary\G show create table change_tag\G"
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16817408 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23931414 DEFAULT CHARSET=binary

db2048

*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16818363 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23917652 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-10-28T09:06:34Z] <marostegui> Deploying schema change s1.enwiki - only codfw - T147166

db2069, db2070, dbstore2002, db2042 are done.
The only pending one in codfw db2016 (the master, which will be done on Monday)

root@neodymium:/home/marostegui/git/software/dbtools# for i in db2069 db2070 dbstore2002 db2042; do echo $i ; mysql -h$i.codfw.wmnet enwiki -e "show create table change_tag\G show create table tag_summary\G" ;done
db2069
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23949983 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16838066 DEFAULT CHARSET=binary
db2070
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23949979 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16838062 DEFAULT CHARSET=binary
dbstore2002
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23949972 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16838057 DEFAULT CHARSET=binary
db2042
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23949965 DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16838044 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-10-31T07:10:39Z] <marostegui> Deploying schema change s1 enwiki codfw (db2016 - master) - T147166

While attempting to do this in db2016 (codfw master) it broke replication in all the slaves

Error 'Duplicate entry '16890654' for key 'PRIMARY'' on query. Default database: 'enwiki'. Query: 'REPLACE /* ChangeTags::updateTagSummaryRow  */ INTO `tag_summary` (ts_rc_id,ts_rev_id,ts_tags) VALUES ('xx','xx','mobile edit,mobile web edit')'

@jcrespo and myself decided to do the following to solve this issue:

We altered all the boxes (including the master) in codfw to remove the PK and to change the ts_id column to DEFAULT NULL and remove the AUTO_INC:

alter table tag_summary drop primary key, change column ts_id ts_id int unsigned default null;

Once that was done, we ran an update to make all the records in ts_id NULL:

set sql_log_bin=0; update tag_summary set ts_id=NULL;

A trigger was created to fix all the incoming transactions and make them NULL too:

SET SQL_LOG_BIN=0; CREATE TRIGGER tag_summary_BI BEFORE INSERT ON tag_summary FOR EACH ROW SET @ts_id = NULL; CREATE TRIGGER tag_summary_BU BEFORE UPDATE ON tag_summary FOR EACH ROW SET @ts_id = NULL;

We started replication and that worked fine.
We have removed the triggers once the server caught up.
Replication between equiad and codfw was stopped during all this maintenance just in case.

The current table layout is:

CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned DEFAULT NULL,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
Marostegui moved this task from In progress to Next on the DBA board.Nov 10 2016, 11:06 AM
jcrespo moved this task from Next to Backlog on the DBA board.Nov 10 2016, 12:12 PM

This may need the same strategy than T130067#2376998 (thus the T138810 dependency), or maybe we can think of an alternative method with just a small read-only period. I have to think about it.

Mentioned in SAL (#wikimedia-operations) [2017-04-24T13:16:21Z] <marostegui> Remove replication codfw - eqiad on s3 (db2018 codfw master will not be a slave of eqiad master) - https://phabricator.wikimedia.org/T130067 https://phabricator.wikimedia.org/T147166 T162133

Mentioned in SAL (#wikimedia-operations) [2017-04-25T13:22:41Z] <marostegui> Deploy alter table on s3 (only etwiki) for tag_summary and change_tag tables - T147166

As well as we did here: T130067#3206224
I have started to deploy this on a small wiki on s3, to see how it goes. I have chosen etwiki again.

root@PRODUCTION s3[etwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1075     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s3[etwiki]> show create table tag_summary\G show create table change_tag\G
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

root@PRODUCTION s3[etwiki]> select count(*) from tag_summary ; select count(*) from change_tag;
+----------+
| count(*) |
+----------+
|   403645 |
+----------+
1 row in set (0.13 sec)

+----------+
| count(*) |
+----------+
|   410938 |
+----------+
1 row in set (0.13 sec)


root@PRODUCTION s3[etwiki]> ALTER TABLE tag_summary ADD COLUMN ts_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (ts_id);
Query OK, 0 rows affected (4.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@PRODUCTION s3[etwiki]> show create table tag_summary\G
*************************** 1. row ***************************
       Table: tag_summary
Create Table: CREATE TABLE `tag_summary` (
  `ts_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ts_rc_id` int(11) DEFAULT NULL,
  `ts_log_id` int(11) DEFAULT NULL,
  `ts_rev_id` int(11) DEFAULT NULL,
  `ts_tags` blob NOT NULL,
  PRIMARY KEY (`ts_id`),
  UNIQUE KEY `ts_rc_id` (`ts_rc_id`),
  UNIQUE KEY `ts_log_id` (`ts_log_id`),
  UNIQUE KEY `ts_rev_id` (`ts_rev_id`),
  UNIQUE KEY `tag_summary_rc_id` (`ts_rc_id`),
  UNIQUE KEY `tag_summary_log_id` (`ts_log_id`),
  UNIQUE KEY `tag_summary_rev_id` (`ts_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=403646 DEFAULT CHARSET=binary

root@PRODUCTION s3[etwiki]> ALTER TABLE change_tag ADD COLUMN ct_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (ct_id);
Query OK, 0 rows affected (7.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@PRODUCTION s3[etwiki]> show create table change_tag\G
*************************** 1. row ***************************
       Table: change_tag
Create Table: CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(11) DEFAULT NULL,
  `ct_log_id` int(11) DEFAULT NULL,
  `ct_rev_id` int(11) DEFAULT NULL,
  `ct_tag` varbinary(255) NOT NULL DEFAULT '',
  `ct_params` blob,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
  UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
  UNIQUE KEY `change_tag_log_tag` (`ct_log_id`,`ct_tag`),
  UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),
  KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=410939 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

root@PRODUCTION s3[etwiki]> select count(*) from tag_summary ; select count(*) from change_tag;
+----------+
| count(*) |
+----------+
|   403646 |
+----------+
1 row in set (0.06 sec)

+----------+
| count(*) |
+----------+
|   410939 |
+----------+
1 row in set (0.06 sec)

Replication is going fine so far

Everything looks fine after this change. The writes have continued fine on the production hosts and dbstore1002, sanitarium and labs have replicated the changes without any issues. So I am going to go ahead and start deploying it.

Mentioned in SAL (#wikimedia-operations) [2017-04-26T06:10:54Z] <marostegui> Deploy alter table on s3, on db1075 (eqiad master) for tables: change_tag and tag_summary - T147166

s3 is done. The following wikis already had the correct structure.

arbcom_cswiki
dtywiki
ecwikimedia
fiwikivoyage
olowiki
pawikisource
projectcomwiki
ptwikimedia
wbwikimedia

Mentioned in SAL (#wikimedia-operations) [2017-04-26T06:45:27Z] <marostegui> Deploy alter table on s2, on db1054 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

s2 is now done.

Mentioned in SAL (#wikimedia-operations) [2017-04-26T07:09:50Z] <marostegui> Deploy alter table on s6, on db1061 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

s6 is done (db1022 is obviously broken so it never got the change: T163778, so ignoring it).

Mentioned in SAL (#wikimedia-operations) [2017-04-26T07:24:47Z] <marostegui> Deploy alter table on s4, on db1068 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

Mentioned in SAL (#wikimedia-operations) [2017-04-26T07:30:40Z] <marostegui> Deploy alter table on s7, on db1062 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

s7 is now done for the wikis with that table (centralauth doesn't have it)

Mentioned in SAL (#wikimedia-operations) [2017-04-26T07:48:58Z] <marostegui> Deploy alter table on s1, on db1052 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

Mentioned in SAL (#wikimedia-operations) [2017-04-26T08:29:59Z] <marostegui> Deploy alter table on change_tag and tag_summary on silver and labtestweb2001 - T147166

I have deployed the change on silver (labswiki) and on labtestweb2001 (labtestwiki)

s1 is done.
Pending s5 which I will wait until the switchover is done: T162133
Meanwhile I will double check all the done shards to make sure all the wikis with that table have the changes.

Mentioned in SAL (#wikimedia-operations) [2017-04-26T10:44:42Z] <marostegui> Deploy alter table on s5, on db1063 (eqiad master) for tables: change_tag and tag_summary - https://phabricator.wikimedia.org/T147166

s5 is now done.

I have double checked all the shards and all of those databases that have the table have been altered (in eqiad of course).

jcrespo updated the task description. (Show Details)Apr 27 2017, 10:47 AM
Marostegui moved this task from Backlog to In progress on the DBA board.May 5 2017, 9:38 AM

Mentioned in SAL (#wikimedia-operations) [2017-05-09T07:27:30Z] <marostegui> Disable replication codfw > eqiad on s6 - T147166 T130067

Mentioned in SAL (#wikimedia-operations) [2017-05-09T07:31:22Z] <marostegui> Stop replication at the same position on db1050 and db2028 - T147166 T130067

Marostegui updated the task description. (Show Details)May 9 2017, 8:06 AM

s6 in codfw is done:

root@neodymium:/home/marostegui/git/software/dbtools# for i in frwiki jawiki ruwiki; do echo $i; mysql --skip-ssl -hdb2028.codfw.wmnet $i -e "show create table tag_summary\G show create table change_tag\G" | grep PRIMARY;done
frwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
jawiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
ruwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 9 2017, 9:01 AM

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-05-09T09:37:12Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1097 - T147166 T130067 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-05-09T09:41:57Z] <marostegui> Stop replication at the same position on db1097 and db2019 - https://phabricator.wikimedia.org/T147166 https://phabricator.wikimedia.org/T130067

s4 in codfw is done:

root@neodymium:/home/marostegui/git/software/dbtools#  mysql --skip-ssl -hdb2019.codfw.wmnet commonswiki -e "show create table tag_summary\G show create table change_tag\G" | grep PRIMARY
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 9 2017, 11:19 AM

Mentioned in SAL (#wikimedia-operations) [2017-05-09T11:27:15Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Restore original weight for db1097 - T147166 T130067 (duration: 00m 39s)

Mentioned in SAL (#wikimedia-operations) [2017-05-09T11:47:53Z] <marostegui> Stop replication at the same position on db1049 and db2023 - https://phabricator.wikimedia.org/T147166 https://phabricator.wikimedia.org/T130067

s5 in codfw is done:

root@neodymium:/home/marostegui/git/software/dbtools# for i in dewiki wikidatawiki; do echo $i; mysql --skip-ssl -hdb2023.codfw.wmnet $i -e "show create table tag_summary\G show create table change_tag\G" | grep PRIMARY;done
dewiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
wikidatawiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 9 2017, 3:17 PM

Mentioned in SAL (#wikimedia-operations) [2017-05-10T07:16:04Z] <marostegui> Disable replication codfw > eqiad on s2 -T147166 T130067

Mentioned in SAL (#wikimedia-operations) [2017-05-10T07:30:41Z] <marostegui> Stop replication at the same position on db10418 and db2017 - T147166 https://phabricator.wikimedia.org/T130067

s2 in codfw is done:

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat /home/marostegui/s7_dbs`; do echo $i; mysql --skip-ssl -hdb2017.codfw.wmnet $i -e "show create table tag_summary\G show create table change_tag\G"| grep PRIMARY;done
bgwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
bgwiktionary
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
cswiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
enwikiquote
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
enwiktionary
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
eowiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
fiwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
idwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
itwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
nlwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
nowiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
plwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
ptwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
svwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
thwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
trwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
zhwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 10 2017, 10:35 AM

Mentioned in SAL (#wikimedia-operations) [2017-05-10T10:43:08Z] <marostegui> Disable replication codfw > eqiad on s7 - T147166 T130067

Mentioned in SAL (#wikimedia-operations) [2017-05-10T10:50:51Z] <marostegui> Stop replication at the same position on db1033 and db2029 - T147166 T130067

s7 in codfw is done:

root@neodymium:~# for i in `cat /home/marostegui/s2_dbs`; do echo $i; mysql --skip-ssl -hdb2029.codfw.wmnet $i -e "show create table tag_summary\G show create table change_tag\G"| grep PRIMARY;done
arwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
cawiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
eswiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
fawiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
frwiktionary
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
hewiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
huwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
kowiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
metawiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
rowiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
ukwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
viwiki
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 10 2017, 12:55 PM

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

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

Change 353061 merged by Marostegui:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1067

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

Mentioned in SAL (#wikimedia-operations) [2017-05-10T14:50:01Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1067 - T147166 T130067 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-05-10T14:50:17Z] <marostegui> Stop replication at the same position on db1067 and db2016 - https://phabricator.wikimedia.org/T147166 https://phabricator.wikimedia.org/T130067

Mentioned in SAL (#wikimedia-operations) [2017-05-10T15:32:54Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1067 - T147166 T130067 (duration: 01m 43s)

s1 in codfw is done:

root@neodymium:~#  mysql --skip-ssl -hdb2016.codfw.wmnet enwiki -e "show create table tag_summary\G show create table change_tag\G"| grep PRIMARY
  PRIMARY KEY (`ts_id`),
  PRIMARY KEY (`ct_id`),
Marostegui updated the task description. (Show Details)May 11 2017, 5:56 AM

Mentioned in SAL (#wikimedia-operations) [2017-05-11T05:56:54Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1067 - T147166 T130067 (duration: 00m 57s)

Mentioned in SAL (#wikimedia-operations) [2017-05-16T06:37:47Z] <marostegui> Stop replication at the same position on db1044 and db2018 - https://phabricator.wikimedia.org/T147166 https://phabricator.wikimedia.org/T130067

Marostegui updated the task description. (Show Details)May 16 2017, 9:18 AM

s3 in codfw is done (some examples):

zh_yuewiki
  PRIMARY KEY (`ts_id`),
zhwikibooks
  PRIMARY KEY (`ts_id`),
zhwikinews
  PRIMARY KEY (`ts_id`),
zhwikiquote
  PRIMARY KEY (`ts_id`),
zhwikisource
  PRIMARY KEY (`ts_id`),
zhwikivoyage
  PRIMARY KEY (`ts_id`),
zhwiktionary
  PRIMARY KEY (`ts_id`),
zuwiki
  PRIMARY KEY (`ts_id`),
zuwikibooks
  PRIMARY KEY (`ts_id`),
zuwiktionary
  PRIMARY KEY (`ts_id`),

And for change_tag

fiwikisource
  PRIMARY KEY (`ct_id`),
fiwikiversity
  PRIMARY KEY (`ct_id`),
fiwikivoyage
  PRIMARY KEY (`ct_id`),
fiwiktionary
  PRIMARY KEY (`ct_id`),
fjwiki
  PRIMARY KEY (`ct_id`),
fjwiktionary
  PRIMARY KEY (`ct_id`),
flaggedrevs_labswikimedia
  PRIMARY KEY (`ct_id`),
foundationwiki
  PRIMARY KEY (`ct_id`),
fowiki
  PRIMARY KEY (`ct_id`),

I am going to check this again across all the shards, masters and random hosts before closing this task.

Marostegui updated the task description. (Show Details)May 16 2017, 9:20 AM
Marostegui closed this task as Resolved.May 16 2017, 10:04 AM

Everything is looking good.
The only host which still doesn't have the new column is dbstore2001 as it is our delayed slave. It should start getting it tomorrow

Marostegui updated the task description. (Show Details)May 16 2017, 10:05 AM