Page MenuHomePhabricator
Create Task
Maniphest T249972

Wikitech has lots of database drifts with core and rest of the databases
Closed, ResolvedPublic
Actions

Description

Working on T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas (T104459#6048024) The results for s10 (db1133.eqiad.wmnet) really stood out.

This the list of db drifts between core and production that only exists in db1133 (ordered alphabetically):

  • categorylinks
    • categorylinks cl_collation index-mismatch-prod-extra
    • categorylinks cl_collation_ext index-mismatch-code-extra
    • categorylinks cl_from index-mismatch-prod-extra
  • externallinks
    • externallinks el_backlinks_namespace index-mismatch-prod-extra
    • externallinks el_from_namespace field-mismatch-prod-extra (one host in s5 has this drift as well)
  • imagelinks
    • imagelinks il_backlinks_namespace index-columns-mismatch
    • imagelinks il_from index-mismatch-prod-extra
  • ipblocks_restrictions ir_type_value index-mismatch-code-extra
  • iwlinks iwl_from index-mismatch-prod-extra
  • langlinks ll_from index-mismatch-prod-extra
  • log_search ls_field_val index-mismatch-prod-extra
  • module_deps md_module_skin index-mismatch-prod-extra
  • pagelinks pl_backlinks_namespace index-columns-mismatch
  • querycache_info qci_type index-mismatch-prod-extra
  • recentchanges rc_name_type_patrolled_timestamp index-mismatch-code-extra
  • searchindex
    • searchindex si_text index-mismatch-prod-extra
    • searchindex si_title index-mismatch-prod-extra
  • site_stats ss_row_id index-mismatch-prod-extra
  • templatelinks tl_backlinks_namespace index-columns-mismatch
  • user user_email index-columns-mismatch
  • user_former_groups ufg_user_group index-mismatch-prod-extra
  • user_properties user_properties_user_property index-mismatch-prod-extra

This is not good.

Related Objects
Search...

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
· · ·
ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
· · ·

Event Timeline

Ladsgroup created this task.Apr 11 2020, 12:04 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 11 2020, 12:04 AM
Reedy added a subscriber: Reedy.Apr 11 2020, 1:41 AM

Are we sure about these?

MariaDB [labswiki]> show create table searchindex;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| searchindex | CREATE TABLE `searchindex` (
  `si_page` int(10) unsigned NOT NULL,
  `si_title` varchar(255) NOT NULL DEFAULT '',
  `si_text` mediumtext NOT NULL,
  UNIQUE KEY `si_page` (`si_page`),
  FULLTEXT KEY `si_title` (`si_title`),
  FULLTEXT KEY `si_text` (`si_text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [labswiki]>

Looks a lot like tables.sql bar the charset, which it's not even complaining about

CREATE TABLE /*_*/searchindex (
  -- Key to page_id
  si_page int unsigned NOT NULL,

  -- Munged version of title
  si_title varchar(255) NOT NULL default '',

  -- Munged version of body text
  si_text mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE UNIQUE INDEX /*i*/si_page ON /*_*/searchindex (si_page);
CREATE FULLTEXT INDEX /*i*/si_title ON /*_*/searchindex (si_title);
CREATE FULLTEXT INDEX /*i*/si_text ON /*_*/searchindex (si_text);

What does index-mismatch-prod-extra even mean? ;)

Reedy updated the task description. (Show Details)Apr 11 2020, 1:43 AM
Reedy updated the task description. (Show Details)
Reedy updated the task description. (Show Details)
Reedy updated the task description. (Show Details)Apr 11 2020, 1:48 AM
Reedy updated the task description. (Show Details)Apr 11 2020, 1:51 AM
Reedy updated the task description. (Show Details)Apr 11 2020, 1:55 AM
Reedy updated the task description. (Show Details)Apr 11 2020, 2:02 AM
Reedy added a comment.Apr 11 2020, 2:04 AM

It seems T172514: Add new PKs to tables.sql (mysql and sqlite) didn't get applied on wikitech

Reedy updated the task description. (Show Details)Apr 11 2020, 2:07 AM
Reedy updated the task description. (Show Details)Apr 11 2020, 2:14 AM
Reedy added a comment.Apr 11 2020, 2:16 AM
In T249972#6048173, @Reedy wrote:

Are we sure about these?

MariaDB [labswiki]> show create table searchindex;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| searchindex | CREATE TABLE `searchindex` (
  `si_page` int(10) unsigned NOT NULL,
  `si_title` varchar(255) NOT NULL DEFAULT '',
  `si_text` mediumtext NOT NULL,
  UNIQUE KEY `si_page` (`si_page`),
  FULLTEXT KEY `si_title` (`si_title`),
  FULLTEXT KEY `si_text` (`si_text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [labswiki]>

Looks a lot like tables.sql bar the charset, which it's not even complaining about

CREATE TABLE /*_*/searchindex (
  -- Key to page_id
  si_page int unsigned NOT NULL,

  -- Munged version of title
  si_title varchar(255) NOT NULL default '',

  -- Munged version of body text
  si_text mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE UNIQUE INDEX /*i*/si_page ON /*_*/searchindex (si_page);
CREATE FULLTEXT INDEX /*i*/si_title ON /*_*/searchindex (si_title);
CREATE FULLTEXT INDEX /*i*/si_text ON /*_*/searchindex (si_text);

What does index-mismatch-prod-extra even mean? ;)

I'm pretty sure this one is right, as in prod we don't use the table, so it's a false positive

MariaDB [enwiki]> show create table searchindex;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                               |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| searchindex | CREATE TABLE `searchindex` (
  `si_page` int(8) unsigned NOT NULL DEFAULT '0',
  `si_title` varbinary(255) NOT NULL DEFAULT '',
  `si_text` mediumblob NOT NULL,
  UNIQUE KEY `si_page` (`si_page`)
) ENGINE=InnoDB DEFAULT CHARSET=binary |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Reedy added a comment.Apr 11 2020, 2:19 AM

I think this is done for wikitech now, and I dropped a couple of other orphaned extra indexes that aren't in tables.sql

Ladsgroup closed this task as Resolved.Apr 11 2020, 6:44 AM
Ladsgroup assigned this task to Reedy.

What does index-mismatch-prod-extra even mean? ;)

it means the index doesn't exist in the code but exists in production.

Thanks for fixing those. I think we are done with wikitech. I try to figure out what's wrong with searchindex. Thanks!

Ladsgroup added a comment.Apr 11 2020, 7:37 AM
In T249972#6048199, @Reedy wrote:

I'm pretty sure this one is right, as in prod we don't use the table, so it's a false positive

Yup, thanks. It's because the parser I wrote for SQL doesn't count for "fulltext" index (only unique). I fix the code now.

Marostegui added a comment.Apr 13 2020, 6:18 AM

Thank you both for detecting and fixing this so fast!

Ladsgroup mentioned this in T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Apr 13 2020, 12:39 PM
Reedy added a comment.Apr 13 2020, 5:17 PM

I've done labtestwiki now too that T250002: Labtestwiki database not reachable from deploy1001 using the standard `sql` helper script is fixed

Marostegui added a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.May 4 2020, 7:07 AM
Ladsgroup mentioned this in T312538: Collect list of tickets done for fixing core drifts .Jul 7 2022, 12:59 PM
Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL