From d3e6c3c7090ed706a152c6c465efc767fc0428c1 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Mon, 13 Jun 2022 10:17:22 -0300 Subject: [PATCH] Bug 30912: Fix db rev 21.12.00.016 This is a mix of Nick's patch and Jonathan's comment on it. I tested it with KTD using MySQL 8 and it works correctly. To test: 1. Launch KTD with bells and whistles: $ docker compose -f docker-compose.yml \ -f docker-compose.mysql8.0.yml \ up -d 2. Inside of it, do: $ koha-mysql kohadev > update systempreferences set value="21.1200015" where variable="version"; > \q $ restart_all $ updatedatabase => SUCCESS: All good :-D 3. Run: $ koha-mysql kohadev > update systempreferences set value="21.1200015" where variable="version"; > ALTER TABLE user_permissions DROP PRIMARY KEY; > \q $ updatedatabase => FAIL: You get: Upgrade to 21.12.00.016 [12:47:09]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS `PRIMARY`' at line 1 at /kohadevbox/koha/C4/Installer.pm line 739 4. Apply this patch 5. Run: $ updatedatabase => SUCCESS: Update goes well 6. Sign off :-D Signed-off-by: Tomas Cohen Arazi Signed-off-by: Nick Clemens Signed-off-by: Tomas Cohen Arazi --- installer/data/mysql/db_revs/211200016.pl | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) diff --git a/installer/data/mysql/db_revs/211200016.pl b/installer/data/mysql/db_revs/211200016.pl index 7f2c173d05..e8584bb635 100755 --- a/installer/data/mysql/db_revs/211200016.pl +++ b/installer/data/mysql/db_revs/211200016.pl @@ -11,10 +11,12 @@ 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` - }); - say $out "Dropped any previously created primary key"; + if ( primary_key_exists('user_permissions') ) { + $dbh->do(q{ + ALTER TABLE user_permissions DROP INDEX `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}); -- 2.39.5