Page MenuHomePhabricator

T6. Update two indexes in extension1db clusters `flowdb` database to be non-unique
Closed, ResolvedPublic

Description

Cluster: extension1db
Database: flowdb

Reason:

The ref_target field describes a URL, the current limit of 255 characters is too small to correctly hold all possible URLs. For this reason we are changing to a BLOB type, which matches the core externallinks table.

The indexes themselves have no need to be unique, this was an oversight in the original design of the table. As the index will only hold a couple hundred bytes any long urls that vary only at the end result in undesired unique index violations. The code that inserts to this table and the core code which reports information to the user both do their own deduplication.

Changes:

DROP INDEX /*i*/flow_wiki_ref_pk ON /*_*/flow_wiki_ref;
DROP INDEX /*i*/flow_wiki_ref_revision ON /*_*/flow_wiki_ref;

CREATE INDEX /*i*/flow_wiki_ref_idx ON /*_*/flow_wiki_ref ( ref_src_namespace, ref_src_title, ref_type, ref_target_namespace, ref_target_title, ref_src_object_type, ref_src_object_id );
CREATE INDEX /*i*/flow_wiki_ref_revision ON /*_*/flow_wiki_ref ( ref_src_namespace, ref_src_title, ref_src_object_type, ref_src_object_id, ref_type, ref_target_namespace, ref_target_title );

  • drop unique constraint, change url column to blob & recreate index

DROP INDEX /*i*/flow_ext_ref_pk ON /*_*/flow_ext_ref;
DROP INDEX /*i*/flow_ext_ref_revision ON /*_*/flow_ext_ref;
ALTER TABLE /*_*/flow_ext_ref CHANGE ref_target ref_target BLOB;
CREATE INDEX /*i*/flow_ext_ref_idx ON /*_*/flow_ext_ref ( ref_src_namespace, ref_src_title, ref_type, ref_target(255), ref_src_object_type, ref_src_object_id );
CREATE INDEX /*i*/flow_ext_ref_revision ON /*_*/flow_ext_ref ( ref_src_namespace, ref_src_title, ref_src_object_type, ref_src_object_id, ref_type, ref_target(255) );

Event Timeline

EBernhardson updated the task description. (Show Details)
EBernhardson raised the priority of this task from to Normal.
EBernhardson assigned this task to Springle.
EBernhardson added a subscriber: EBernhardson.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 17 2015, 4:37 PM

The related change in Flow is T92284

So these tables no longer have primary keys?

That's ok for now, but it will be problematic in the future as it limits our ability to perform online schema changes.

If possible, add a surrogate primary key.

Schema change is done.

DannyH renamed this task from Update two indexes in extension1db clusters `flowdb` database to be non-unique to T6. Update two indexes in extension1db clusters `flowdb` database to be non-unique.Mar 25 2015, 7:33 PM
DannyH raised the priority of this task from Normal to High.

Thanks sean, i've added T95203 to track adding a surrogate primary key to these two tables.