Description
Petter Goksøyr Åsen
2015-08-03 11:46:10 UTC
I encounter this problem for a client that moved his Koha database on MySQL 5.6. A temporary fix I did was to force the sql_mode to NO_ENGINE_SUBSTITUTION (removing the STRICT_TRANS_TABLES) on the connection time. I added the following line in Context.pm $dbo->do("set SESSION sql_mode='NO_ENGINE_SUBSTITUTION'"); You can do the same without modifying the source code, just comment the sql_mode=STRICT_TRANS_TABLES line in your MySQL/MariaDB config file. Created attachment 55232 [details] [review] Bug 14637: Fix add patron category under MySQL 5.7 - tests Created attachment 55233 [details] [review] Bug 14637: Fix add patron category under MySQL 5.7 If no dateofbirthrequired or upperagelimit is set on the interface, the ->store method will receive an empty string defined for these values. For INT field, we must explicitely set these empty value to undef instead to avoid MySQL 5.7 (and strict mode) to raise: DBD::mysql::st execute failed: Incorrect integer value: ' for column 'dateofbirthrequired'' Test plan: Using MySQL 5.7 (and/or sql_mode=STRICT_TRANS_TABLES) Create a patron category without specifying upperagelimit or dateofbirthrequired I am still not sure if this is the way to go. Actually update_or_insert should set the default value defined at the DB level if the field is not passed to the constructor. Created attachment 56674 [details] [review] [SIGNED-OFF] Bug 14637: Fix add patron category under MySQL 5.7 - tests Signed-off-by: Josef Moravec <josef.moravec@gmail.com> Created attachment 56675 [details] [review] [SIGNED-OFF] Bug 14637: Fix add patron category under MySQL 5.7 - tests Signed-off-by: Josef Moravec <josef.moravec@gmail.com> Created attachment 56676 [details] [review] [SIGNED-OFF] Bug 14637: Fix add patron category under MySQL 5.7 If no dateofbirthrequired or upperagelimit is set on the interface, the ->store method will receive an empty string defined for these values. For INT field, we must explicitely set these empty value to undef instead to avoid MySQL 5.7 (and strict mode) to raise: DBD::mysql::st execute failed: Incorrect integer value: ' for column 'dateofbirthrequired'' Test plan: Using MySQL 5.7 (and/or sql_mode=STRICT_TRANS_TABLES) Create a patron category without specifying upperagelimit or dateofbirthrequired Signed-off-by: Josef Moravec <josef.moravec@gmail.com> Created attachment 56677 [details] [review] Bug 14637: Followup - Fix number of tests due to rebase So it looks like this kind of issue could be in more places, is it true? I agree with you, that it would be better to fix it generally, but I have no idea, how to do it... But if we'd go with this solution, I am adding sign-off as it works as described. (In reply to Josef Moravec from comment #10) > So it looks like this kind of issue could be in more places, is it true? > > I agree with you, that it would be better to fix it generally, but I have no > idea, how to do it... > > But if we'd go with this solution, I am adding sign-off as it works as > described. Could you prove the tests from this bug without the changes to Koha::Patron::Category->store and apply bug 17080 and see if they pass with MySQL5.7? (In reply to Jonathan Druart from comment #11) > (In reply to Josef Moravec from comment #10) > > So it looks like this kind of issue could be in more places, is it true? > > > > I agree with you, that it would be better to fix it generally, but I have no > > idea, how to do it... > > > > But if we'd go with this solution, I am adding sign-off as it works as > > described. > > Could you prove the tests from this bug without the changes to > Koha::Patron::Category->store and apply bug 17080 and see if they pass with > MySQL5.7? I tried, but unfortunately, the test failed with this error message: t/db_dependent/Koha/Patron/Categories.t .. 1/8 DBD::mysql::st execute failed: Incorrect integer value: '' for column 'dateofbirthrequired' at row 1 [for Statement "INSERT INTO `categories` ( `category_type`, `categorycode`, `dateofbirthrequired`, `description`, `upperagelimit`) VALUES ( ?, ?, ?, ?, ? )" with ParamValues: 0='A', 1='mycatcodeW', 2='', 3='mycatdescW', 4=''] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832. DBIx::Class::Storage::DBI::_dbh_execute(): Incorrect integer value: '' for column 'dateofbirthrequired' at row 1 at /home/vagrant/kohaclone/Koha/Object.pm line 119 # Looks like your test exited with 255 just after 8. t/db_dependent/Koha/Patron/Categories.t .. Dubious, test returned 255 (wstat 65280, 0xff00) All 8 subtests passed Test Summary Report ------------------- t/db_dependent/Koha/Patron/Categories.t (Wstat: 65280 Tests: 8 Failed: 0) Non-zero exit status: 255 Files=1, Tests=8, 1 wallclock secs ( 0.01 usr 0.00 sys + 0.51 cusr 0.27 csys = 0.79 CPU) Result: FAIL Maybe I unterstand it wrong but 17080 handles NOT NULL columns and upperagelimit and dateofbirthrequired are nullable... (In reply to Josef Moravec from comment #12) > Maybe I unterstand it wrong but 17080 handles NOT NULL columns and > upperagelimit and dateofbirthrequired are nullable... Yes sorry, not enough tea this morning. This patch makes sense but we could reuse the same logic for date and int fields (if equal '', then set to undef). Created attachment 58416 [details] [review] Bug 14637: Fix add patron category under MySQL 5.7 - tests Signed-off-by: Josef Moravec <josef.moravec@gmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Created attachment 58417 [details] [review] Bug 14637: Fix add patron category under MySQL 5.7 If no dateofbirthrequired or upperagelimit is set on the interface, the ->store method will receive an empty string defined for these values. For INT field, we must explicitely set these empty value to undef instead to avoid MySQL 5.7 (and strict mode) to raise: DBD::mysql::st execute failed: Incorrect integer value: ' for column 'dateofbirthrequired'' Test plan: Using MySQL 5.7 (and/or sql_mode=STRICT_TRANS_TABLES) Create a patron category without specifying upperagelimit or dateofbirthrequired Signed-off-by: Josef Moravec <josef.moravec@gmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Created attachment 58418 [details] [review] Bug 14637: Followup - Fix number of tests due to rebase Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Pushed to master for 17.05, thanks Jonathan! These patches have been pushed to 16.11.x, will be in 16.11.02 . Pushed to 16.05.x, for 16.05.08 release (In reply to Mason James from comment #19) > Pushed to 16.05.x, for 16.05.08 release No! No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 35. No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 35. No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 40. No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 40. No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 55. No method checkprevcheckout! at t/db_dependent/Koha/Patron/Categories.t line 55. [13:33:25] t/db_dependent/Koha/Patron/Categories.t ...................... ok 977 ms You should run the tests (at least) when you backport patches. |