Page MenuHomePhabricator

Give user_properties a primary key
Closed, ResolvedPublic


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

Event Timeline

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.

So what's the upshot? Should the addition of user_properties.up_id be removed from ?

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 claimed this task.
Marostegui added a project: DBA.
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):;b8a7d492ebf6dc199efedb110785cef3a89ef850$215 . Only the DBA part is done, Mediawiki patch is pending.

Reedy claimed this task.