Page MenuHomePhabricator

Give user_properties a primary key
Closed, ResolvedPublic

Description

user_properties doesn't have a primary key; see T17441.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 25 2016, 7:26 AM

Oh, so I guess user_properties actually does have a unique index? Does it still need a primary key, then?

From mediawiki/core.git:maintenance/tables.sql as of 090d0267daa4721ffb154e7e604804201365f9dd (October 1, 2016):

--
-- User preferences and perhaps other fun stuff. :)
-- Replaces the old user.user_options blob, with a couple nice properties:
--
-- 1) We only store non-default settings, so changes to the defauls
--    are now reflected for everybody, not just new accounts.
-- 2) We can more easily do bulk lookups, statistics, or modifications of
--    saved options since it's a sane table structure.
--
CREATE TABLE /*_*/user_properties (
  -- Foreign key to user.user_id
  up_user int NOT NULL,

  -- Name of the option being saved. This is indexed for bulk lookup.
  up_property varbinary(255) NOT NULL,

  -- Property value as a string.
  up_value blob
) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);

I can't guarantee that Wikimedia's database servers have this exact schema, but it's probably pretty close.

Tjlsangria added a comment.EditedOct 1 2016, 6:53 PM

So what's the upshot? Should the addition of user_properties.up_id be removed from https://gerrit.wikimedia.org/r/#/c/313670/ ?

jcrespo added a subscriber: jcrespo.Feb 5 2017, 4:38 PM

Largest issue is that a PK on user_properties should also be a foreign key to users (or at least virtually). The types are differents, being user UNSIGNED there and SIGNED here. That should also be fixed.

As a note, as part of: T164185 we are converting user_properties UNIQUE key into a PK: up_user,up_property across all the shards
Once having a PK we will be able to switch it to another one in the future if we find that that one isn't the best one.

Marostegui closed this task as Resolved.Aug 4 2017, 6:34 AM
Marostegui claimed this task.
Marostegui added a project: DBA.
jcrespo reopened this task as Open.Aug 4 2017, 7:19 AM
jcrespo moved this task from Triage to Done on the DBA board.

Reopening, this has been done on WMF, but not on tables.sql (+mediawiki patch): https://phabricator.wikimedia.org/source/mediawiki/browse/master/maintenance/tables.sql;b8a7d492ebf6dc199efedb110785cef3a89ef850$215 . Only the DBA part is done, Mediawiki patch is pending.

jcrespo removed Marostegui as the assignee of this task.Aug 4 2017, 7:19 AM
Reedy closed this task as Resolved.Aug 29 2017, 5:47 PM
Reedy claimed this task.