From e79e9b039c7d98b4a85cf59d57685f3c9b12948e Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 24 Nov 2016 13:37:52 +0000 Subject: [PATCH] Bug 17676: Update default COLLATE of marc_subfield_structure Content-Type: text/plain; charset=utf-8 (This issue has been raised on bug 17216 comments 93-103) On bug 11944 (3.19.00.006) we updated the default COLLATE for all our tables to utf8_unicode_ci but not the marc_subfield_structure table. Indeed we want to keep tagsubfield a utf8_bin (to allow lowercase and uppercase of the same letter for subfields). We should have set the default collate to utf8_unicode_ci for further changes. This patch updates the DB entry 3.19.00.006 to set the default COLLATE to this table (for people upgrading from prior to 3.19.00.006) and set this default COLLATE on 16.06.00.033 (for people upgrading from after 3.19.00.006). The error is: DBD::mysql::db do failed: Can't create table `koha_kohadev`.`#sql-306_9f9` (errno: 150 "Foreign key constraint is incorrectly formed") [for Statement " ALTER TABLE marc_subfield_structure MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL, ADD CONSTRAINT marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value) REFERENCES authorised_value_categories (category_name) ON UPDATE CASCADE ON DELETE SET NULL; "] at installer/data/mysql/updatedatabase.pl line 13175. Upgrade to 16.06.00.033 done (Bug 17216 - Add a new table to store authorized value categories) Test plan: 1/ git checkout v3.18.00 2/ Do an install 3/ git checkout master; 4/ perl installer/data/mysql/updatedatabase.pl => Without this patch, you get the error => With this patch applied you will not get it and the default COLLATE for marc_subfield_structure will be correctly set. Make sure tagsubfield is still utf8_bin Signed-off-by: Mark Tompsett Signed-off-by: Marcel de Rooy Will put some notes on the Bugzilla report. --- installer/data/mysql/updatedatabase.pl | 8 ++++++++ 1 file changed, 8 insertions(+) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 9b77f8d..b99974c 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9736,6 +9736,7 @@ if ( CheckVersion($DBversion) ) { MODIFY COLUMN seealso varchar(1100) COLLATE utf8_unicode_ci DEFAULT NULL, MODIFY COLUMN link varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL |); + $dbh->do(qq|ALTER TABLE $name CHARACTER SET utf8 COLLATE utf8_unicode_ci|); } else { $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci|); @@ -13172,6 +13173,13 @@ if ( CheckVersion($DBversion) ) { UPDATE marc_subfield_structure SET authorised_value = NULL WHERE authorised_value = ';'; }); + # If the DB has been created before 3.19.00.006, the default collate for marc_subfield_structure if not set to utf8_unicode_ci and the new FK will not be create (MariaDB or MySQL will raise err 150) + my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE marc_subfield_structure|); + $table_sth->execute; + my @table = $table_sth->fetchrow_array; + if ( $table[1] !~ /COLLATE=utf8_unicode_ci/ and $table[1] !~ /COLLATE=utf8mb4_unicode_ci/ ) { #catches utf8mb4 collated tables + $dbh->do(qq|ALTER TABLE marc_subfield_structure CHARACTER SET utf8 COLLATE utf8_unicode_ci|); + } $dbh->do(q{ ALTER TABLE marc_subfield_structure MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL, -- 2.1.4