Page MenuHomePhabricator

Extend echo_unread_wikis.euw_wiki
Closed, ResolvedPublic

Description

WHAT:
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/Echo/+/602983/7/db_patches/patch-increase-varchar-echo_unread_wikis-euw_wiki.sql

ALTER TABLE /*_*/echo_unread_wikis MODIFY euw_wiki VARCHAR(64) NOT NULL;

WHERE:

  • wikishared

WHEN: Whenever (no rush; WMF prod doesn't have wiki db names > 30 chars)

BACK COMPAT: Yes

TESTED: Yes (run on beta)

Progress:

x1:

  • eqiad
  • codfw

Event Timeline

Marostegui triaged this task as Medium priority.Jun 12 2020, 4:58 AM
Marostegui moved this task from Triage to Backlog on the DBA board.

While this can proceed on the slaves, changing the data type of the column cannot be done online and the table is big enough that will be locked for a few minutes, so this needs a master switchover to be able to be completed on the master.

As we use RBR on x1, this needs to be performed directly on the master - it cannot be done slave by slave first as otherwise the slaves breaks replication when an insert arrives:

Last_Error: Column 2 of table 'wikishared.echo_unread_wikis' cannot be converted from type 'varchar(30)' to type 'varbinary(64)'

I have reverted the alter on db2101 and set it back to varchar(30).
We'd need to put x1 on RO to be able to perform this, as otherwise all the writes to this table will pile up.

Note I believe some wikis have echo tables embedded into the metadata database CC @Reedy T119154 Do these need alter too?

LSobanski subscribed.

Progressing with this task safely requires developing a way to make x1 read only from MediaWiki, which is currently not planned.

I am going to start working on this in January. It will require x1 to go to SBR though for a few days.

metawiki, mediawikiwiki, labswiki,officewiki aren't having echo_unread_wikis table anymore.
So this is only needed in x1 (wikishared)

Change 865593 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/software/schema-changes@master] change_echo_unread_wikis_T255174.py: New schema change

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

Change 865593 merged by Marostegui:

[operations/software/schema-changes@master] change_echo_unread_wikis_T255174.py: New schema change

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

Change 874776 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] mariadb: Change x1 to STATEMENT

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

Change 874776 merged by Marostegui:

[operations/puppet@production] mariadb: Change x1 to STATEMENT

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

Mentioned in SAL (#wikimedia-operations) [2023-01-03T11:04:48Z] <marostegui> Change x1 binlog format to STATEMENT T255174

I tried this on a slave and it broke:

Last_SQL_Error: Column 2 of table 'wikishared.echo_unread_wikis' cannot be converted from type 'varbinary(30)' to type 'varbinary(64)'

I am checking why

It's interesting to see that it complains about column 2 which isn't being changed. I think this must have something to do with the conversion from RBR and SBR (even though I have rotated the logs). Still checking

The binlog format was indeed changed on db2096 and even though the log was rotated several times (and binlog_format flag was correctly showing STATEMENT), the change wasn't effective (on the file itself, as it was still being written as RBR). What made it effective was to execute all this in the same transaction on db2096 (x1 codfw master):

stop slave; set global binlog_format=STATEMENT; flush binary logs; start slave;

I am going to leave db2131 running for 24h to make sure replication doesn't really break again (although I see new records being inserted correctly on the table)

Change 874816 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db2131: Disable notifications

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

Change 874816 merged by Marostegui:

[operations/puppet@production] db2131: Disable notifications

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

db2131 seems to be fine, so going to repool and start with the other hosts.

Mentioned in SAL (#wikimedia-operations) [2023-01-04T07:35:04Z] <marostegui> dbmaint eqiad deploy schema change on x1 T255174

Mentioned in SAL (#wikimedia-operations) [2023-01-04T07:35:24Z] <marostegui> dbmaint codfw deploy schema change on x1 T255174

Marostegui updated the task description. (Show Details)

This is done

Mentioned in SAL (#wikimedia-operations) [2023-01-04T08:20:51Z] <marostegui> dbmaint codfw deploy schema change on s8 T255174

Mentioned in SAL (#wikimedia-operations) [2023-01-04T08:22:08Z] <marostegui> dbmaint eqiad deploy schema change on s8 T255174

Mentioned in SAL (#wikimedia-operations) [2023-01-04T08:26:27Z] <marostegui> dbmaint eqiad deploy schema change on s4 T255174

Mentioned in SAL (#wikimedia-operations) [2023-01-04T08:26:31Z] <marostegui> dbmaint codfw deploy schema change on s4 T255174