From 4620b4c70eaff72e3c47c16fd4bb5664560c7029 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 15 Feb 2022 12:17:44 +0000 Subject: [PATCH] Bug 30060: (follow-up) Remove duplicate prior to adding key I had issues with this update as somehow my user_permissions table contained duplicate rows which throws an error when adding the new primary key. This follow-up patch adds SQL to remove any duplicate rows prior to adding the new primary key. Signed-off-by: Martin Renvoize --- .../mysql/atomicupdate/update_user_permissions_table.pl | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/installer/data/mysql/atomicupdate/update_user_permissions_table.pl b/installer/data/mysql/atomicupdate/update_user_permissions_table.pl index c455ab6bbc..067d6c613a 100755 --- a/installer/data/mysql/atomicupdate/update_user_permissions_table.pl +++ b/installer/data/mysql/atomicupdate/update_user_permissions_table.pl @@ -15,6 +15,12 @@ return { ALTER TABLE user_permissions DROP INDEX IF EXISTS `PRIMARY` }); say $out "Dropped any previously created primary key"; + + $dbh->do(q{ALTER TABLE user_permissions ADD COLUMN temp SERIAL PRIMARY KEY}); + $dbh->do(q{DELETE t1 FROM user_permissions t1 INNER JOIN user_permissions t2 WHERE t1.temp < t2.temp AND t1.borrowernumber = t2.borrowernumber AND t1.module_bit = t2.module_bit AND t1.code = t2.code}); + $dbh->do(q{ALTER TABLE user_permissions DROP COLUMN temp}); + say $out "Removed any duplicate rows"; + $dbh->do(q{ ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit,code) }); -- 2.20.1