Page MenuHomePhabricator

Type of flow_ext_ref.ref_target incorrect on some tables on Beta Cluster
Open, NormalPublic

Description

Somehow https://gerrit.wikimedia.org/r/#/c/196660/ did not get fully applied everywhere on Beta Cluster:

1mattflaschen@deployment-bastion:~$ echo 'SHOW CREATE TABLE flow_ext_ref;'|sql aawiki
2Table Create Table
3flow_ext_ref CREATE TABLE `flow_ext_ref` (\n `ref_src_object_id` binary(11) NOT NULL,\n `ref_src_object_type` varbinary(32) NOT NULL,\n `ref_src_workflow_id` binary(11) NOT NULL,\n `ref_src_namespace` int(11) NOT NULL,\n `ref_src_title` varbinary(255) NOT NULL,\n `ref_target` varbinary(255) NOT NULL,\n `ref_type` varbinary(16) NOT NULL,\n `ref_src_wiki` varbinary(16) NOT NULL,\n KEY `flow_ext_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target`,`ref_src_object_type`,`ref_src_object_id`),\n KEY `flow_ext_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target`)\n) ENGINE=InnoDB DEFAULT CHARSET=binary
4
5mattflaschen@deployment-bastion:~$ echo 'SHOW CREATE TABLE flow_ext_ref;'|sql enwiki
6Table Create Table
7flow_ext_ref CREATE TABLE `flow_ext_ref` (\n `ref_src_object_id` binary(11) NOT NULL,\n `ref_src_object_type` varbinary(32) NOT NULL,\n `ref_src_workflow_id` binary(11) NOT NULL,\n `ref_src_namespace` int(11) NOT NULL,\n `ref_src_title` varbinary(255) NOT NULL,\n `ref_target` blob,\n `ref_type` varbinary(16) NOT NULL,\n `ref_src_wiki` varchar(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,\n KEY `flow_ext_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target`(255),`ref_src_object_type`,`ref_src_object_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

flow_ext_ref.ref_target is wrongly still varbinary(255) on some tables.

Event Timeline

Mattflaschen-WMF raised the priority of this task from to Needs Triage.
Mattflaschen-WMF updated the task description. (Show Details)
Restricted Application added a project: Collaboration-Team-Triage. · View Herald TranscriptAug 27 2015, 2:02 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Side note, there are also inconsistencies in CHARSET, but this might be DB-wide.

Catrope renamed this task from ref_target of flow_ext_ref incorrect on some tables on Beta Cluster to Type of flow_ext_ref.ref_target incorrect on some tables on Beta Cluster.Sep 2 2015, 11:38 PM
Catrope triaged this task as Normal priority.
Catrope set Security to None.
Catrope added a subscriber: Catrope.

While upgrading to 1.27, I got the following from Flow (which may be the cause of the columns having the wrong type?):

Modifying ref_target field of table flow_ext_ref ...A database query error has occurred.
Query: ALTER TABLE `flow_ext_ref` CHANGE ref_target ref_target BLOB NOT NULL

Function: DatabaseBase::sourceFile( /onewiki/dev/extensions/Flow/db_patches/patch-ref_target_not_null.sql )
Error: 1170 BLOB/TEXT column 'ref_target' used in key specification without a key length (localhost)

Changing patch-ref_target_not_null.sql to the following did the trick:

ALTER TABLE /*_*/flow_ext_ref DROP KEY flow_ext_ref_idx_v2;
ALTER TABLE /*_*/flow_ext_ref CHANGE ref_target ref_target BLOB NOT NULL;
CREATE INDEX /*i*/flow_ext_ref_idx_v2 ON /*_*/flow_ext_ref
	(ref_src_wiki, ref_src_namespace, ref_src_title, ref_type, ref_target(255), ref_src_object_type, ref_src_object_id);

Patch incoming.

Change 308912 had a related patch set uploaded (by MarkAHershberger):
Drop and re-create the index

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

Change 308912 merged by jenkins-bot:
Drop and re-create the index

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

Patch got merged two weeks ago.

Can this task be closed as resolved, or what is left to do here?

No, this is still not fixed. Rerunning Matt's command still gives the same results. Someone (not sure who) needs to fix the DB schema in beta labs.

Change 314639 had a related patch set uploaded (by MarkAHershberger):
Drop and re-create the index

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

Change 314639 merged by jenkins-bot:
Drop and re-create the index

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

ALTER TABLE /*_*/flow_ext_ref DROP KEY flow_ext_ref_idx_v2;
ALTER TABLE /*_*/flow_ext_ref CHANGE ref_target ref_target BLOB NOT NULL;
CREATE INDEX /*i*/flow_ext_ref_idx_v2 ON /*_*/flow_ext_ref
	(ref_src_wiki, ref_src_namespace, ref_src_title, ref_type, ref_target(255), ref_src_object_type, ref_src_object_id);

gave me a direct error for the 2nd alter

#1170 - BLOB/TEXT column 'ref_target' used in key specification without a key length

@Subfader Mark's patch should fix errors like that by dropping and re-creating the index (the second alter shouldn't give an error since at that point there should be no index with that column).

What do you see from:

SHOW CREATE TABLE flow_ext_ref

after the initial drop? Maybe you have an additional index beyond that one (containing the column).

Aklapper changed the task status from Open to Stalled.Dec 21 2016, 8:52 AM

@Subfader: Can you please answer the last question?

Aklapper closed this task as Resolved.Jan 25 2017, 7:16 PM

Unfortunately closing this report as no further information has been provided.

@Subfader: Please reopen this report (by changing its status) after you have provided the information asked for and if this still happens. Thanks!

Mattflaschen-WMF reopened this task as Open.Jan 25 2017, 10:39 PM

Unfortunately, this bug got diverted into a related but separate issue.

Please use it for the original intended purpose (see the original report, "flow_ext_ref.ref_target is wrongly still varbinary(255) on some tables."), the Beta Cluster issue. It is still inconsistent:

mattflaschen@deployment-tin:~$ echo 'SHOW CREATE TABLE flow_ext_ref;'|sql aawiki 
Table   Create Table
flow_ext_ref    CREATE TABLE `flow_ext_ref` (\n  `ref_src_object_id` binary(11) NOT NULL,\n  `ref_src_object_type` varbinary(32) NOT NULL,\n  `ref_src_workflow_id` binary(11) NOT NULL,\n  `ref_src_namespace` int(11) NOT NULL,\n  `ref_src_title` varbinary(255) NOT NULL,\n  `ref_target` varbinary(255) NOT NULL,\n  `ref_type` varbinary(16) NOT NULL,\n  `ref_src_wiki` varbinary(16) NOT NULL,\n  `ref_id` binary(11) NOT NULL,\n  PRIMARY KEY (`ref_id`),\n  KEY `flow_ext_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target`)\n) ENGINE=InnoDB DEFAULT CHARSET=binary

mattflaschen@deployment-tin:~$ echo 'SHOW CREATE TABLE flow_ext_ref;'|sql enwiki 
Table   Create Table
flow_ext_ref    CREATE TABLE `flow_ext_ref` (\n  `ref_src_object_id` binary(11) NOT NULL,\n  `ref_src_object_type` varbinary(32) NOT NULL,\n  `ref_src_workflow_id` binary(11) NOT NULL,\n  `ref_src_namespace` int(11) NOT NULL,\n  `ref_src_title` varbinary(255) NOT NULL,\n  `ref_target` blob NOT NULL,\n  `ref_type` varbinary(16) NOT NULL,\n  `ref_src_wiki` varchar(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,\n  `ref_id` binary(11) NOT NULL,\n  PRIMARY KEY (`ref_id`),\n  KEY `flow_ext_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target`(255)),\n  KEY `flow_ext_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target`(255),`ref_src_object_type`,`ref_src_object_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

Change https://gerrit.wikimedia.org/r/314639 has started breaking beta's updater as the DROP KEY mysql command fails as it says that doesn't exist. I also do not see any key for this in flow.sql instead it's an index.

https://integration.wikimedia.org/ci/job/beta-update-databases-eqiad/17315/

Restricted Application added a project: Growth-Team. · View Herald TranscriptSat, Oct 12, 4:11 AM