[Bug 17260] New: updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Bug ID: 17260 Summary: updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Installation and upgrade (command-line installer) Assignee: gmcharlt@gmail.com Reporter: philippe.blouin@inlibro.com QA Contact: testopia@bugs.koha-community.org See also Bug 17234 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Blou <philippe.blouin@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch CC| |eric.begin@inLibro.com, | |francharb@gmail.com Assignee|gmcharlt@gmail.com |philippe.blouin@inlibro.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Blou <philippe.blouin@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=17234 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #1 from Blou <philippe.blouin@inlibro.com> --- Created attachment 55263 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=55263&action=edit 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. 0) Find a database on 3.22 or earlier, save it. 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. 6) succeed with updatedatabase.pl -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Blou <philippe.blouin@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #2 from M. Tompsett <mtompset@hotmail.com> --- Comment on attachment 55263 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=55263 Bug 17260 - updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns Review of attachment 55263: --> (https://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=17260&attachment=55263) ----------------------------------------------------------------- ::: installer/data/mysql/updatedatabase.pl @@ +12280,4 @@
`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',
I'm not sure how to test for this. @@ +12337,4 @@
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} || 0, suggestible => $mapping->{suggestible} || 0, sort => $mapping->{sort} } );
I can voucher for this change easily. I reproduced it, and this fixes it. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Francois Charbonnier <francois.charbonnier@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC|francharb@gmail.com |francois.charbonnier@inlibr | |o.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=17324 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=17320 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org Blocks| |17258 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17258 [Bug 17258] [OMIBUS] MySQL 5.7 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also|https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=17320 | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #3 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- (In reply to M. Tompsett from comment #2)
Comment on attachment 55263 [details] [review] Bug 17260 - updatedatabase.pl fails on invalid entries in ENUM and BOOLEAN columns
Review of attachment 55263 [details] [review]: -----------------------------------------------------------------
::: installer/data/mysql/updatedatabase.pl @@ +12280,4 @@
`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',
I'm not sure how to test for this.
I do not have MySQL5.7 but: mysql> insert into search_field(name, label, type)values('name', 'label', ''); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'type' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) And given https://dev.mysql.com/doc/refman/5.7/en/constraint-enum.html: "For a column defined as ENUM('a','b','c'), values such as '', 'd', or 'ax' are invalid and are rejected." I guess MySQL5.7 will raise an error instead of a warning. ENUM should be avoided, but that would be for another bug report. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #55263|0 |1 is obsolete| | --- Comment #4 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 59621 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=59621&action=edit 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 <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=16583 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Mehdi Hamidi <mehdi.hamidi@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Mehdi Hamidi <mehdi.hamidi@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #59621|0 |1 is obsolete| | --- Comment #5 from Mehdi Hamidi <mehdi.hamidi@inlibro.com> --- Created attachment 60532 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=60532&action=edit 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 <mtompset@hotmail.com> Signed-off-by: Mehdi Hamidi <mehdi.hamidi@inlibro.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Mehdi Hamidi <mehdi.hamidi@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mehdi.hamidi@inlibro.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #6 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Who is the original author of this patch? The patch says "From: Your Full Name <your_email>" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #7 from Mehdi Hamidi <mehdi.hamidi@inlibro.com> --- (In reply to Jonathan Druart from comment #6)
Who is the original author of this patch? The patch says "From: Your Full Name <your_email>"
The original author is : Blou <philippe.blouin@inlibro.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #8 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- So I guess you can resubmit the patch with the correct author line. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Mehdi Hamidi <mehdi.hamidi@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #60532|0 |1 is obsolete| | --- Comment #9 from Mehdi Hamidi <mehdi.hamidi@inlibro.com> --- Created attachment 60561 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=60561&action=edit 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 <mtompset@hotmail.com> Signed-off-by: Mehdi Hamidi <mehdi.hamidi@inlibro.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #60561|0 |1 is obsolete| | --- Comment #10 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 60635 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=60635&action=edit 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 <mtompset@hotmail.com> Signed-off-by: Mehdi Hamidi <mehdi.hamidi@inlibro.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 --- Comment #11 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 60636 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=60636&action=edit Bug 17260: Update existing installs Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com Status|Passed QA |Pushed to Master --- Comment #12 from Kyle M Hall <kyle@bywatersolutions.com> --- Pushed to master for 17.05, thanks Phillipe, Jonathan! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #13 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- These patches have been pushed to 16.11.x and will be in 16.11.05. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #14 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- These patches have been pushed to 16.11.x and will be in 16.11.05. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtj@kohaaloha.com --- Comment #15 from Mason James <mtj@kohaaloha.com> --- Pushed to 16.05.x, for 16.05.11 release -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17260 Zeno Tajoli <z.tajoli@cineca.it> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Stable |RESOLVED Resolution|--- |FIXED CC| |z.tajoli@cineca.it -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org