S9. LQT: Unique index flow_ext_ref_pk blocking false positives
Closed, ResolvedPublic2 Estimated Story Points
Actions

Assigned To

Authored By

	matthiasmullie
	Mar 10 2015, 4:08 PM

Description

T90025 revealed some pretty large external links, like:

The unique index flow_ext_ref_pk constraint results in this post not being inserted, even though the links are different.
The index is too short to hold the entire value, and the differences are near the end of the very long links.

Apart from the index, we may also want to think about the size of that column (currently varbinary(255))

Details

	Subject	Repo	Branch	Lines +/-
	Remove unique constraints from flow_*_ref	mediawiki/extensions/Flow	master	+48 -7

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Resolved	• DannyH	T171977 Annual Plan 2017-2018, Audiences 5: Increase current editor retention and engagement
Resolved	• DannyH	T171982 Annual Plan 2017-2018, Audiences 5, Goal 3: Provide better workflow and communication experiences
Open	None	T167928 Improve structured discussion features for the communities that use them, based on user feedback and prioritising technical debt
Open	None	T89426 Allow to uninstall all LiquidThreads code except the parts necessary for the remaining content, to be moved into (?)WikimediaMessages
Resolved	• Catrope	T106302 Convert LQT->Flow on se.wikimedia.org
Resolved	• Mattflaschen-WMF	T92303 Convert LQT pages on MediaWiki.org to Flow (tracking)
Resolved	matthiasmullie	T92284 S9. LQT: Unique index flow_ext_ref_pk blocking false positives

Event Timeline

matthiasmullie created this task.Mar 10 2015, 4:08 PM

matthiasmullie raised the priority of this task from to Medium.

matthiasmullie updated the task description. (Show Details)

matthiasmullie added projects: StructuredDiscussions, Collaboration-Team-Triage.

matthiasmullie subscribed.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 10 2015, 4:08 PM

matthiasmullie mentioned this in T90025: R6. LQT conversion test for Mediawiki.org VE/Feedback.Mar 10 2015, 4:09 PM

matthiasmullie updated the task description. (Show Details)

matthiasmullie set Security to None.

The easy way to punt on this is increase the size of the field, its current 256 but urls can obviously be bigger than that. In core they use a 'blob' type instead of varchar to hold many more characters, but we hold a unique index against this field, and indexes must have a specific length to include not just "all of a blob". One workaround could be to include an md5 of Reference::getTargetIdentifier() and use that as the primary key.

also note that the links in the description arn't the ones that trigger the error, those are < 100 characters. There is another link in that post that is several hundred characters

matthiasmullie updated the task description. (Show Details)Mar 10 2015, 5:22 PM

I've updated with the real links it was failing on: 369 chars (those others were 156chars, it seems)

But with ref_src_namespace, ref_src_title & ref_type prefixing ref_target, the index will fill up way earlier than the column does (and the new examples also max out that one already)

• Mattflaschen-WMF added a parent task: T92303: Convert LQT pages on MediaWiki.org to Flow (tracking).Mar 11 2015, 6:13 PM

• DannyH moved this task from Untriaged to Team discussion on the Collaboration-Team-Triage board.Mar 11 2015, 6:23 PM

• DannyH edited a custom field.Mar 11 2015, 6:28 PM

• DannyH moved this task from Team discussion to Ready for next sprint on the Collaboration-Team-Triage board.

• DannyH moved this task from Ready for next sprint to Sprint S (temporary for meeting) on the Collaboration-Team-Triage board.Mar 11 2015, 6:44 PM

EBernhardson added a project: Collaboration-Team-Sprint-S-2015-03-25.Mar 11 2015, 9:28 PM

• DannyH renamed this task from Unique index flow_ext_ref_pk blocking false positives to S9. LQT: Unique index flow_ext_ref_pk blocking false positives.Mar 11 2015, 9:47 PM

• DannyH removed a project: Collaboration-Team-Triage.

• DannyH assigned this task to matthiasmullie.Mar 12 2015, 5:39 PM

• DannyH moved this task from Sprint S - ends March 25 to In Development on the Collaboration-Team-Sprint-S-2015-03-25 board.

Change 196660 had a related patch set uploaded (by Matthias Mullie):
Remove unique constraints from flow_*_ref

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

gerritbot added a project: Patch-For-Review.Mar 13 2015, 9:08 PM

matthiasmullie moved this task from In Development to Needs Code Review on the Collaboration-Team-Sprint-S-2015-03-25 board.Mar 16 2015, 12:41 PM

EBernhardson moved this task from Needs Code Review to Code Review Started on the Collaboration-Team-Sprint-S-2015-03-25 board.Mar 16 2015, 11:14 PM

Change 196660 merged by jenkins-bot:
Remove unique constraints from flow_*_ref

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

matthiasmullie moved this task from Code Review Started to Product Review on the Collaboration-Team-Sprint-S-2015-03-25 board.Mar 17 2015, 5:37 PM

EBernhardson mentioned this in T92979: T6. Update two indexes in extension1db clusters `flowdb` database to be non-unique.Mar 17 2015, 7:05 PM