mirror of
https://github.com/ZoneMinder/zoneminder.git
synced 2026-09-13 05:38:05 -04:00
Isaac Connor
f3658e6673
Nothing enforced uniqueness, but every reader already assumed one row per name: User::Preferences() hashes the rows by Name, montagereview.php and User_Preference::find_one() take the first match, and TagOrder::load() reads a single Value. With duplicates present those pick an arbitrary row, so a write could land on one row while reads returned another -- the tag order would appear to stop updating. Replace the UserId-only index with UNIQUE (UserId, Name) and make Name NOT NULL. The unique index still serves UserId-only lookups and the UserId foreign key as a leftmost prefix, so the old index is redundant and is dropped. Name has to be NOT NULL for the constraint to mean anything, since a unique index permits any number of NULLs; a NULL-named row is unreachable regardless, as a preference is only ever looked up by name. zm_update-1.39.19.sql discards NULL-named rows, makes the column NOT NULL, collapses existing duplicates keeping the highest Id (the most recently inserted), adds the unique index, then drops the old one. The index is added before the old one is dropped so the foreign key is never left without a usable index. Each schema change is guarded against INFORMATION_SCHEMA so re-running is a no-op. db/User_Preferences.sql gets the same shape for fresh installs. With the constraint in place, TagOrder::recordUsage() replaces its SELECT-then-INSERT-or-UPDATE with a single INSERT ... ON DUPLICATE KEY UPDATE. That drops a query and closes the race where two concurrent tag additions by the same user both see no row and both insert. Verified on MariaDB 11.8.8 against a seeded table: duplicate rows collapse to the expected survivors, NULL-named rows are removed, a second run is a clean no-op, NULL/duplicate/bad-UserId inserts are rejected (1048/1062/1452, so the foreign key survives losing its index), and mysqldump of a fresh install matches a migrated database exactly. Bump version.txt and the redhat spec Version to 1.39.19.
19 lines
613 B
SQL
19 lines
613 B
SQL
--
|
|
-- Table structure for table `Users_Preferences`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS `User_Preferences`;
|
|
CREATE TABLE `User_Preferences` (
|
|
`Id` int(10) unsigned NOT NULL auto_increment,
|
|
`UserId` int(10) unsigned NOT NULL,
|
|
FOREIGN KEY (UserId) REFERENCES Users(Id),
|
|
`Name` varchar(64) NOT NULL,
|
|
`Value` TEXT,
|
|
PRIMARY KEY (Id)
|
|
);
|
|
|
|
-- A user has at most one value per preference name. This also serves
|
|
-- UserId-only lookups (and the UserId foreign key) as a leftmost prefix, so
|
|
-- no separate UserId index is needed.
|
|
CREATE UNIQUE INDEX User_Preferences_UserId_Name_idx on User_Preferences (`UserId`, `Name`);
|