From d9f452921e87a47cf3b5dd5c829e5dbf051bdc5e Mon Sep 17 00:00:00 2001 From: Zeno Tajoli Date: Sat, 21 Nov 2015 14:10:06 +0100 Subject: [PATCH] To not show errors on update from 3.20.x to 3.22.x Two update of db, 3.21.00.027 and 3.21.00.022, are partialy done also on 3.20.x. This patch modify updatedatabase.pl to cover those partial changes in db done in 3.20.x To test: 1)Install a 3.20.4 or more 2)Dump the db 2)Update to master and see the error 3)Installe the patch 4)Reload the db 5)Update to master and see no more error 6)Check the coerence at sql structure level More check: a)Reload the db b)Change db to delete the effect of those two commit in 3.20: bug 14205: http://git.koha-community.org/gitweb/?p=koha.git;a=commit;h=c158f9e27e9e283523a5070ae4e4c718627374d2 bug 14298: http://git.koha-community.org/gitweb/?p=koha.git;a=commit;h=e0c926e0104e3fb73d80d8bea07ea8b6f90807b3 c)Redone update d)With patch no errors Signed-off-by: Bernardo Gonzalez Kriegel Added COLLATE to audio_alerts Signed-off-by: Katrin Fischer Insert check based on INDEX instead of FK http://bugs.koha-community.org/show_bug.cgi?id=15207 Signed-off-by: Brendan A Gallagher --- installer/data/mysql/updatedatabase.pl | 42 ++++++++++++++++++++++++++-------- 1 file changed, 32 insertions(+), 10 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6ba8e4d..66b32cd 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -10801,12 +10801,34 @@ if ( CheckVersion($DBversion) ) { LEFT JOIN course_items USING(ci_id) WHERE course_items.ci_id IS NULL }); - $dbh->do(q{ - ALTER IGNORE TABLE course_reserves - add CONSTRAINT course_reserves_ibfk_2 - FOREIGN KEY (ci_id) REFERENCES course_items (ci_id) - ON DELETE CASCADE ON UPDATE CASCADE - }); + my @row; + @row = $dbh->selectrow_arrayref(qq| + SHOW INDEXES FROM course_reserves WHERE key_name='course_reserves_ibfk_2' + |); + #Add fk only if not present + unless ( @row ) { + $dbh->do(q{ + ALTER IGNORE TABLE course_reserves + add CONSTRAINT course_reserves_ibfk_2 + FOREIGN KEY (ci_id) REFERENCES course_items (ci_id) + ON DELETE CASCADE ON UPDATE CASCADE + }); + }else{ + $dbh->do(q{ + ALTER IGNORE TABLE course_reserves + DROP FOREIGN KEY course_reserves_ibfk_2; + }); + $dbh->do(q{ + ALTER IGNORE TABLE course_reserves + DROP INDEX course_reserves_ibfk_2; + }); + $dbh->do(q{ + ALTER IGNORE TABLE course_reserves + add CONSTRAINT course_reserves_ibfk_2 + FOREIGN KEY (ci_id) REFERENCES course_items (ci_id) + ON DELETE CASCADE ON UPDATE CASCADE + }); + }; print "Upgrade to $DBversion done (Bug 14205: Deleting an Item/Record does not remove link to course reserve)\n"; SetVersion($DBversion); } @@ -10869,7 +10891,7 @@ if ( CheckVersion($DBversion) ) { $DBversion = "3.21.00.027"; if ( CheckVersion($DBversion) ) { $dbh->do(q| - INSERT INTO permissions (module_bit, code, description) + INSERT IGNORE INTO permissions (module_bit, code, description) VALUES (1, 'self_checkout', 'Perform self checkout at the OPAC. It should be used for the patron matching the AutoSelfCheckID') |); @@ -11264,11 +11286,11 @@ if ( CheckVersion($DBversion) ) { CREATE TABLE audio_alerts ( id int(11) NOT NULL AUTO_INCREMENT, precedence smallint(5) unsigned NOT NULL, - selector varchar(255) NOT NULL, - sound varchar(255) NOT NULL, + selector varchar(255) COLLATE utf8_unicode_ci NOT NULL, + sound varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (id), KEY precedence (precedence) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; }); $dbh->do(q{ -- 2.1.4