From 6f512cff8b264167f27ff8d007da9b172a772192 Mon Sep 17 00:00:00 2001 From: Blou Date: Fri, 27 Jan 2017 16:51:26 +0000 Subject: [PATCH] Bug 17260: updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns The loading of file admin/searchengine/elasticsearch/mappings.yaml specifies 'type' as empty, which fails with Mysql 5.7+ which is more stringent. Also, forcing an empty value into a boolean also fails. Both issues arise when updatedatabase.pl hit 3.23.00.050. NOTE: both issues could also be resolved by actually setting values in the load file. This doesn't make this solution incorrect, though. To Test/reproduce: -1) Happens with Mysql 5.7.4. Maybe earlier, but certainly at that point. Use a Xenial kohadevbox to more easily test. 0) Find a database on 3.22 or earlier, save it. a) place sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION into the [mysqld] section of /etc/mysql/mysql.conf.d/mysqld.cnf b) restart you mysql server c) drop the db and recreate it d) checkout the 3.22.x branch e) do a web install f) remove the added sql_mode g) restart the mysql server 1) Set your code base to master 2) run updatedatabase.pl 3) See the errors on 3.23.00.050 4) Apply the patch 5) Reload the 3.22 db. a) repeat steps 0(a)-0(g) b) don't forget the caching issues 6) succeed with updatedatabase.pl 7) drop the db and recreate it 8) run the web installer 9) notice no issues either. 10) run koha qa test tools NOTE: This bug only solved the upgrade portion. I added the kohastructure.sql change as well. I confirmed that all the code changes were triggered with this test plan. Signed-off-by: Mark Tompsett Signed-off-by: Mehdi Hamidi Signed-off-by: Jonathan Druart Signed-off-by: Kyle M Hall (cherry picked from commit 77337a2ab537843eab70cc06b8bc2531ad2014dc) Signed-off-by: Katrin Fischer --- Koha/SearchEngine/Elasticsearch.pm | 2 +- installer/data/mysql/kohastructure.sql | 2 +- installer/data/mysql/updatedatabase.pl | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) diff --git a/Koha/SearchEngine/Elasticsearch.pm b/Koha/SearchEngine/Elasticsearch.pm index 35a6540dc2..f6d4d3a333 100644 --- a/Koha/SearchEngine/Elasticsearch.pm +++ b/Koha/SearchEngine/Elasticsearch.pm @@ -265,7 +265,7 @@ sub reset_elasticsearch_mappings { my $search_field = Koha::SearchFields->find_or_create({ name => $field_name, label => $field_label, type => $field_type }, { key => 'name' }); for my $mapping ( @$mappings ) { my $marc_field = Koha::SearchMarcMaps->find_or_create({ index_name => $index_name, marc_type => $mapping->{marc_type}, marc_field => $mapping->{marc_field} }); - $search_field->add_to_search_marc_maps($marc_field, { facet => $mapping->{facet}, suggestible => $mapping->{suggestible}, sort => $mapping->{sort} } ); + $search_field->add_to_search_marc_maps($marc_field, { facet => $mapping->{facet} || 0, suggestible => $mapping->{suggestible} || 0, sort => $mapping->{sort} } ); } } } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 514c13dc05..4825b61120 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1476,7 +1476,7 @@ CREATE TABLE `search_field` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', - `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', + `type` ENUM('', 'string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', PRIMARY KEY (`id`), UNIQUE KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 85be4c1b93..6ccdfa0209 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -12339,7 +12339,7 @@ if ( CheckVersion($DBversion) ) { `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', - `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', + `type` ENUM('', 'string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', PRIMARY KEY (`id`), UNIQUE KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci -- 2.39.5