Page MenuHomePhabricator

Perform schema change to echo_target_page changing from a 1 to 1 mapping between pages and user/notification to a 1 to many.
Closed, ResolvedPublic

Description

The echo_target_page table currently represents a page that, when visited, will automatically mark the relevant notification as read. This feature is being expanded to allow multiple target pages for a specific notification. To acomplish that the unique index currently acting as a primary key needs to be dropped and replaced with a standard index. Since this primary key is being dropped it also needs a new auto_increment primary key added

ALTER TABLE /*_*/echo_target_page ADD etp_id int unsigned not null primary key auto_increment;
DROP INDEX /*i*/echo_target_page_user_event ON /*_*/echo_target_page;
CREATE INDEX /*i*/echo_target_page_user_event ON /*_*/echo_target_page (etp_user, etp_event);

Event Timeline

EBernhardson assigned this task to Springle.
EBernhardson raised the priority of this task from to Needs Triage.
EBernhardson updated the task description. (Show Details)
EBernhardson added a subscriber: EBernhardson.
Restricted Application added a project: Collaboration-Team-Triage. · View Herald TranscriptMar 30 2015, 4:45 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
EBernhardson set Security to None.

This will probably roll forward to the next sprint, i just added it to Sprint-T so it doesn't get lost.

EBernhardson triaged this task as Normal priority.Apr 7 2015, 5:51 PM

To be clear:

  • This needs to apply on echo_target_page in every wiki on x1-master
  • I can deploy this anytime now, without breaking code

Right?

Springle raised the priority of this task from Normal to High.May 22 2015, 10:30 AM
Springle reassigned this task from Springle to jcrespo.May 22 2015, 10:34 AM

Converting right now the schema. This is faster than I expected, even if I am doing the changes slowly. It should be done within an hour. I will update with details when finished.

@Mattflaschen May 22 14:49:48 UTC all tables named echo_target_page on x1 where successfully converted to:

CREATE TABLE `echo_target_page` (
  `etp_user` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_page` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_event` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`etp_id`),
  KEY `echo_target_page_user_page_event` (`etp_user`,`etp_page`,`etp_event`),
  KEY `echo_target_page_user_event` (`etp_user`,`etp_event`)
) ENGINE=InnoDB AUTO_INCREMENT=N DEFAULT CHARSET=latin1`

Please note the new column position, so that it can be as compatible as posible.

3 wikis could not be converted because the table didn't exist on those schemas. As I suspect they can be private or special wikis, please ask me in private if clarification is needed.

If all this is valid for you, please close the ticket as Resolved. Thank you!

Quiddity closed this task as Resolved.May 27 2015, 4:27 PM
Quiddity added a subscriber: Quiddity.

To be clear:

  • This needs to apply on echo_target_page in every wiki on x1-master

My mistake. I did not realize that it also has to be applied on the four wikis listed at https://git.wikimedia.org/blob/operations%2Fmediawiki-config/20e51990e4775c1fffa0380aab5b94b2ba1c109e/wmf-config%2FInitialiseSettings.php#L14509 .

I believe this is now causing T107835: Fix new topic notification on MediaWiki.org . Sorry about that.

Mattflaschen-WMF reopened this task as Open.Aug 24 2015, 9:08 PM
jcrespo added a comment.EditedAug 25 2015, 6:44 AM

I performed an online ALTER TABLE, with the same structure as the original one, but on the core databases of mediawikiwiki, metawiki and officewiki. And a regular alter table on labswiki, on silver.

CREATE TABLE `echo_target_page` (
  `etp_user` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_page` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_event` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`etp_id`),
  KEY `echo_target_page_user_page_event` (`etp_user`,`etp_page`,`etp_event`),
  KEY `echo_target_page_user_event` (`etp_user`,`etp_event`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=binary

Please indicate if we need to perform it on any more host (there were mentions of test[2]wiki, but those are not in the list you indicated).

Please indicate if we need to perform it on any more host (there were mentions of test[2]wiki, but those are not in the list you indicated).

I got one success 8 hours ago, but I'm still testing.

Please indicate if we need to perform it on any more host (there were mentions of test[2]wiki, but those are not in the list you indicated).

No. My testing and log search indicates it's fixed, and the only affected wikis should be the ones I linked. The test/test2 was based on suspecting a different cause.

Thanks again, and sorry for overlooking this earlier.

Checked general Echo functionality in betalabs.
Checked in db betalabs for enwiki - the changes seem to be applied.

MariaDB [enwiki]>  SHOW CREATE TABLE echo_target_page\G
*************************** 1. row ***************************
       Table: echo_target_page
Create Table: CREATE TABLE `echo_target_page` (
  `etp_user` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_page` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_event` int(10) unsigned NOT NULL DEFAULT '0',
  `etp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`etp_id`),
  KEY `echo_target_page_user_page_event` (`etp_user`,`etp_page`,`etp_event`),
  KEY `echo_target_page_user_event` (`etp_user`,`etp_event`)
) ENGINE=InnoDB AUTO_INCREMENT=144246 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Does not look as they much used though - could be specific to betalbs@enwiki env

[enwiki]> select * from information_schema.INDEX_STATISTICS WHERE TABLE_SCHEMA='enwiki' and TABLE_NAME='echo_target_page' ORDER BY Rows_read DESC;
+--------------+------------------+----------------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME       | INDEX_NAME                       | ROWS_READ |
+--------------+------------------+----------------------------------+-----------+
| enwiki       | echo_target_page | echo_target_page_user_event      |         2 |
| enwiki       | echo_target_page | echo_target_page_user_page_event |         1 |
+--------------+------------------+----------------------------------+-----------+
2 rows in set (0.01 sec)
DannyH closed this task as Resolved.Aug 28 2015, 5:28 PM
DannyH added a subscriber: DannyH.