user_properties doesn't have a primary key; see T17441.
Description
Status | Subtype | Assigned | Task | |
---|---|---|---|---|
· · · | ||||
Resolved | LSobanski | T17441 Some tables lack unique or primary keys, may allow confusing duplicate data | ||
Resolved | Reedy | T157227 MediaWiki DB tables with columns which references other columns but have different type (tracking) | ||
Resolved | Reedy | T146570 Give user_properties a primary key | ||
Resolved | Reedy | T172514 Add new PKs to tables.sql (mysql and sqlite) | ||
· · · |
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 https://gerrit.wikimedia.org/r/#/c/313670/ ?
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.
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.