Update page_assessments_projects schema for subprojects in production
Closed, ResolvedPublic

Description

  1. The ALTER TABLEs to run: https://gerrit.wikimedia.org/r/#/c/329386/4/db/patch-subprojects.sql
  2. Where to run those changes: testwiki, enwikivoyage, enwiki
  3. When to run those changes: Any time (ASAP)
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: Tested on Beta Cluster
  6. If it involves new columns or tables, if the data should be made available on the labs replicas and/or dumps: New column isn't private. It should be copied to replicas just like the rest of the table.
kaldari created this task.Jan 25 2017, 9:03 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJan 25 2017, 9:03 PM
kaldari renamed this task from Update PageAssessment schemas for subprojects in production to Update page_assessments_projects schema for subprojects in production.Jan 25 2017, 10:31 PM
kaldari assigned this task to jcrespo.
kaldari updated the task description. (Show Details)
kaldari moved this task from Untriaged to Community/Communication on the Community-Tech board.

FYI, this should be very quick and easy. The extension is only installed on 3 wikis and even on en.wiki the table only has 1432 rows.

kaldari triaged this task as High priority.Feb 1 2017, 5:27 PM

Marking high priority since this is needed for T156856 and T156857 (which we're starting on this sprint).

Pinging @Marostegui in case he has time to look into this.

jcrespo added a comment.EditedFeb 1 2017, 6:13 PM

@kaldari, when I ask on the "form": "where", is to speed up the implementation. I do not know which 3 wikis those are- and while I could look them, mediawiki has 20 different configuration files. Making that explicit or linking to the config line if it can change will really speed up the process!

Thank you.

jcrespo reassigned this task from jcrespo to kaldari.Feb 1 2017, 8:37 PM
jcrespo added a subscriber: jcrespo.
kaldari updated the task description. (Show Details)Feb 1 2017, 8:40 PM
jcrespo moved this task from Triage to In progress on the DBA board.

Thanks, doing now.

For the record, I am not trying to be an ass (which I may be in any case), but we have lots and lots of dev requests and limited time to process them.

@jcrespo: Thanks for the info. I've updated the description to explicitly list the 3 wikis.

Mentioned in SAL (#wikimedia-operations) [2017-02-01T22:08:45Z] <jynus> deploying schema change to page_assessments_projects on testwiki T156305

Can you verify testwiki on all hosts? This was done in a rush and there is probably errors on some hosts:

dbstore2001.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
dbstore2002.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db2036.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db2043.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db2050.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db2057.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db2018.codfw.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
labsdb1001.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
labsdb1003.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1069.eqiad.wmnet 3313
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
labsdb1009.eqiad.wmnet 3306
ERROR 1045 (28000): Access denied for user 'root'@'10.64.32.20' (using password: YES)
labsdb1010.eqiad.wmnet 3306
ERROR 1045 (28000): Access denied for user 'root'@'10.64.32.20' (using password: YES)
labsdb1011.eqiad.wmnet 3306
ERROR 1045 (28000): Access denied for user 'root'@'10.64.32.20' (using password: YES)
dbstore1001.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
dbstore1002.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1095.eqiad.wmnet 3306
ERROR 1045 (28000): Access denied for user 'root'@'10.64.32.20' (using password: YES)
db1015.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1035.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1038.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1044.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1077.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1078.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary
db1075.eqiad.wmnet 3306
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=binary

@jcrespo: Everything looks correct on all the servers I was able to connect to. I wasn't able to connect to the dbstore servers or db1069:

kaldari@terbium:~$ sql testwiki -h db1069
ERROR 2003 (HY000): Can't connect to MySQL server on 'db1069' (110)

kaldari@terbium:~$ sql testwiki -h dbstore1001
ERROR 1045 (28000): Access denied for user 'wikiadmin'@'10.64.32.13' (using password: YES)

dbstore1001 didn't have the alter done for enwikivoyage and enwiki.
I have altered them:

root@dbstore1001.eqiad.wmnet[enwiki]> show create table enwiki.page_assessments_projects\G show create table enwikivoyage.page_assessments_projects\G
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=1552 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=229 DEFAULT CHARSET=binary
1 row in set (0.03 sec)

db1069 had the same issue with: enwiki and enwikivoyage.
I have altered those.

MariaDB SANITARIUM localhost enwiki > show create table page_assessments_projects\G
*************************** 1. row ***************************
       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=1556 DEFAULT CHARSET=binary
1 row in set (0.00 sec)


       Table: page_assessments_projects
Create Table: CREATE TABLE `page_assessments_projects` (
  `pap_project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pap_project_title` varbinary(255) DEFAULT NULL,
  `pap_parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pap_project_id`),
  UNIQUE KEY `pap_project_title` (`pap_project_title`)
) ENGINE=InnoDB AUTO_INCREMENT=399 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

@Marostegui No server has the change for enwiki and enwikivoyage, I only started with test for now.

@Marostegui No server has the change for enwiki and enwikivoyage, I only started with test for now.

Ah! Sorry - misread your comment! Well, at least those are done now then :)

Mentioned in SAL (#wikimedia-operations) [2017-02-02T08:57:02Z] <jynus> deploying schema change to page_assessments_projects on enwiki T156305

Mentioned in SAL (#wikimedia-operations) [2017-02-02T09:19:28Z] <jynus> deploying schema change to page_assessments_projects on enwikivoyage T156305

jcrespo moved this task from In progress to Done on the DBA board.Feb 2 2017, 9:24 AM

This should be done now, please recheck all servers you can (forget about db1069 and dbstore1001, those are not mediawiki servers). If I missed even one, replication could break and affect not only page assessments functionality, but put all wikis in read only.

Please check and resolve if ok.

kaldari closed this task as Resolved.Feb 2 2017, 6:40 PM

Checked on all database servers for enwiki (s1) and enwikivoyage (s3).

@jcrespo: What's the procedure for getting the table updated on Tool Labs?

@kaldari, can you check the new labsdb servers, it may be already there? We may start to unmaintain the old ones, at some point depending on the change. Maybe it has already been done but the views that wrap them have to be updated.

@jcrespo: The schema has been updated on labsdb1001:

mysql:wikiadmin@labsdb1001 [enwiki]> describe page_assessments_projects;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| pap_project_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| pap_project_title | varbinary(255)   | YES  | UNI | NULL    |                |
| pap_parent_id     | int(10) unsigned | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

I can't connect to any of the other labsdb servers from terbium:

kaldari@terbium:~$ sql enwiki -h labsdb1003
ERROR 1045 (28000): Access denied for user 'wikiadmin'@'10.64.32.13' (using password: YES)
kaldari@terbium:~$ sql enwiki -h labsdb1009
ERROR 2003 (HY000): Can't connect to MySQL server on 'labsdb1009' (110)
kaldari@terbium:~$ sql enwiki -h labsdb1010
ERROR 2003 (HY000): Can't connect to MySQL server on 'labsdb1010' (110)
kaldari@terbium:~$ sql enwiki -h labsdb1011
ERROR 2003 (HY000): Can't connect to MySQL server on 'labsdb1011' (110)

To update the views, how do you add new columns? The table (page_assessments_projects) is listed in /modules/role/templates/labsdb/maintain-views.yaml but it doesn't list columns specifically.

A script has to be run on labs, please file a separate task for them, it is not a DBA task nor has anything to do with production schema changes.

Created new task at T158097.