From 9ac9e128936bdb2a6c1d04e1189e8c576839aa8f Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Thu, 9 Jun 2022 12:55:21 +0000 Subject: [PATCH] Bug 30912: [alternate] Drop primary key only if exists To test: 1 - Have koha running with MySQL 8 2 - sudo koha-mysql kohadev 3 - To confirm current indexes: SHOW INDEXES FROM user_permissions WHERE Key_name = 'PRIMARY'; 4 - Drop them to ensure the update will run ALTER TABLE user_permissions DROP INDEX `PRIMARY`; 5 - Run the DB update (using attached script) and ensure it works when there is no key 6 - Drop the key again ALTER TABLE user_permissions DROP INDEX `PRIMARY`; 7 - Create new index that will still trigger update ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit); 8 - Run update again and ensure it works when there is a key --- installer/data/mysql/db_revs/211200016.pl | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/db_revs/211200016.pl b/installer/data/mysql/db_revs/211200016.pl index 7f2c173d05..ad766aee18 100755 --- a/installer/data/mysql/db_revs/211200016.pl +++ b/installer/data/mysql/db_revs/211200016.pl @@ -11,9 +11,13 @@ return { primary_key_exists( 'user_permissions', 'module_bit' ) && primary_key_exists( 'user_permissions', 'code') ){ - $dbh->do(q{ - ALTER TABLE user_permissions DROP INDEX IF EXISTS `PRIMARY` + my $exists = $dbh->do(q{ + SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() + AND TABLE_NAME = 'user_permissions' AND CONSTRAINT_NAME = 'PRIMARY'; }); + $dbh->do(q{ + ALTER TABLE user_permissions DROP INDEX `PRIMARY` + }) if $exists > 0; say $out "Dropped any previously created primary key"; $dbh->do(q{ALTER TABLE user_permissions ADD COLUMN temp SERIAL PRIMARY KEY}); -- 2.30.2