From e0448fd77c36609bf40e56d97931d72a1317ba0b Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 9 Jun 2022 13:42:41 +0200 Subject: [PATCH] Bug 30912: Fix db rev 21.12.00.016 Looks like MySQL v8 does not support the IF EXISTS syntax to delete primary key (to confirmed) 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 /usr/share/koha/lib/C4/Installer.pm line 739 Test plan: update systempreferences set value="21.1200015" where variable="version"; restart_all updatedatabase ALTER TABLE user_permissions DROP PRIMARY KEY; update systempreferences set value="21.1200015" where variable="version"; restart_all updatedatabase Signed-off-by: Harald Signed-off-by: Nick Clemens --- installer/data/mysql/db_revs/211200016.pl | 12 ++++++++---- 1 file changed, 8 insertions(+), 4 deletions(-) diff --git a/installer/data/mysql/db_revs/211200016.pl b/installer/data/mysql/db_revs/211200016.pl index 7f2c173d05..176720f2fc 100755 --- a/installer/data/mysql/db_revs/211200016.pl +++ b/installer/data/mysql/db_revs/211200016.pl @@ -11,10 +11,14 @@ 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"; + eval { + local $dbh->{PrintError} = 0; + local $dbh->{RaiseError} = 0; + $dbh->do(q{ + ALTER TABLE user_permissions DROP PRIMARY KEY + }); + 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.30.2