Page MenuHomePhabricator

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

Description

Working on T104459: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and slaves (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.

Event Timeline

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

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!

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.

Thank you both for detecting and fixing this so fast!