Update page_assessments_projects schema for subprojects in production
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	kaldari
	Jan 25 2017, 9:03 PM

Description

The ALTER TABLEs to run: https://gerrit.wikimedia.org/r/#/c/329386/4/db/patch-subprojects.sql
Where to run those changes: testwiki, enwikivoyage, enwiki
When to run those changes: Any time (ASAP)
If the schema change is backwards compatible: Yes
If the schema change has been tested already on some of the test/beta wikis: Tested on Beta Cluster
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.

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		• TBolliger	T141154 [Epic] Fix and improve Mr.Z's popular pages report
		Resolved		jcrespo	T156305 Update page_assessments_projects schema for subprojects in production

Event Timeline

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 New & TBD Tickets to CL, QA, Data analysis backlog 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 added a parent task: T141154: [Epic] Fix and improve Mr.Z's popular pages report.Feb 1 2017, 5:22 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.

@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 subscribed.

kaldari updated the task description. (Show Details)Feb 1 2017, 8:40 PM

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.

In T156305#2992453, @jcrespo wrote:

@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

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.

jcrespo moved this task from Backlog to Done on the Schema-change-in-production board.Feb 2 2017, 9:24 AM

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

kaldari moved this task from CL, QA, Data analysis backlog to Archive on the Community-Tech board.Feb 2 2017, 11:59 PM

@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.

kaldari mentioned this in T158097: Replicate new pap_parent_id column in page_assessments_projects table on Labs.Feb 14 2017, 6:50 PM

Created new task at T158097.

Niharika moved this task from Ready to Done on the MediaWiki-extensions-PageAssessments board.Nov 21 2018, 11:59 PM

Update page_assessments_projects schema for subprojects in productionClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Update page_assessments_projects schema for subprojects in production
Closed, ResolvedPublic
Actions

Related Objects
Search...