Bug 26365 - Koha/Acquisition/Order.t is failing with MySQL 8
Summary: Koha/Acquisition/Order.t is failing with MySQL 8
Status: Pushed to master
Alias: None
Product: Koha
Classification: Unclassified
Component: Test Suite (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 23166
Blocks: 23996
  Show dependency treegraph
 
Reported: 2020-09-03 09:56 UTC by Jonathan Druart
Modified: 2020-10-15 21:30 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Text to go in the release notes:
Version(s) released in:
20.11.00


Attachments
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted (1.88 KB, patch)
2020-09-03 09:59 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted (1.93 KB, patch)
2020-09-08 13:34 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted (2.00 KB, patch)
2020-09-10 12:34 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Druart 2020-09-03 09:56:04 UTC
t/db_dependent/Koha/Acquisition/Order.t .. 10/11 No method count found for Koha::Acquisition::Orders DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT COUNT( * ) FROM `aqorders` `me` WHERE ( ( ( `datecancellationprinted` IS NULL OR `datecancellationprinted` = ? ) AND `ordernumber` IN ( ?, ?, ? ) ) )" with ParamValues: 0='0000-00-00', 1=23, 2=24, 3=25] at /kohadevbox/koha/Koha/Objects.pm line 597

We must remove the comparison with 0000-00-00
Comment 1 Jonathan Druart 2020-09-03 09:56:22 UTC
Highlighted by bug 23166.
Comment 2 Jonathan Druart 2020-09-03 09:59:57 UTC
Created attachment 109600 [details] [review]
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted

Using MySQL 8:
t/db_dependent/Koha/Acquisition/Order.t .. 10/11 No method count found for Koha::Acquisition::Orders DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT COUNT( * ) FROM `aqorders` `me` WHERE ( ( ( `datecancellationprinted` IS NULL OR `datecancellationprinted` = ? ) AND `ordernumber` IN ( ?, ?, ? ) ) )" with ParamValues: 0='0000-00-00', 1=23, 2=24, 3=25] at /kohadevbox/koha/Koha/Objects.pm line 597

We must remove the comparison with 0000-00-00

Highlighted by bug 23166.

I wanted to provide a DB rev but we (obviously) cannot:
MySQL [koha_kohadev]> UPDATE aqorders SET datecancellationprinted=NULL WHERE datecancellationprinted="0000-00-00";
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'datecancellationprinted' at row 1
Comment 3 Tomás Cohen Arazi 2020-09-05 11:58:42 UTC
Just came across this one yesterday.
Comment 4 Tomás Cohen Arazi 2020-09-08 13:34:31 UTC
Created attachment 109749 [details] [review]
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted

Using MySQL 8:
t/db_dependent/Koha/Acquisition/Order.t .. 10/11 No method count found for Koha::Acquisition::Orders DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT COUNT( * ) FROM `aqorders` `me` WHERE ( ( ( `datecancellationprinted` IS NULL OR `datecancellationprinted` = ? ) AND `ordernumber` IN ( ?, ?, ? ) ) )" with ParamValues: 0='0000-00-00', 1=23, 2=24, 3=25] at /kohadevbox/koha/Koha/Objects.pm line 597

We must remove the comparison with 0000-00-00

Highlighted by bug 23166.

I wanted to provide a DB rev but we (obviously) cannot:
MySQL [koha_kohadev]> UPDATE aqorders SET datecancellationprinted=NULL WHERE datecancellationprinted="0000-00-00";
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'datecancellationprinted' at row 1

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 5 Martin Renvoize 2020-09-10 12:21:27 UTC
Hmm.. regarding the db update can we not use $dbh->{mysql_serverversion} to compare and only execute the update if we have an older MySQL version.. that way we're preparing for upgrades..?
Comment 6 Jonathan Druart 2020-09-10 12:28:49 UTC
In that case I would be in favour of a maintenance script: before you switch to MySQL 8 (or any other DBMS that won't allowed 0000-00-00), then run it and it will fix all the bad dates.
Comment 7 Martin Renvoize 2020-09-10 12:34:37 UTC
Created attachment 109849 [details] [review]
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted

Using MySQL 8:
t/db_dependent/Koha/Acquisition/Order.t .. 10/11 No method count found for Koha::Acquisition::Orders DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT COUNT( * ) FROM `aqorders` `me` WHERE ( ( ( `datecancellationprinted` IS NULL OR `datecancellationprinted` = ? ) AND `ordernumber` IN ( ?, ?, ? ) ) )" with ParamValues: 0='0000-00-00', 1=23, 2=24, 3=25] at /kohadevbox/koha/Koha/Objects.pm line 597

We must remove the comparison with 0000-00-00

Highlighted by bug 23166.

I wanted to provide a DB rev but we (obviously) cannot:
MySQL [koha_kohadev]> UPDATE aqorders SET datecancellationprinted=NULL WHERE datecancellationprinted="0000-00-00";
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'datecancellationprinted' at row 1

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize 2020-09-10 12:37:57 UTC
Well.. I'm more than happy with the patch so am passing QA..  DB update wise, I'm not really too worried about it. We do have a number of places more recently in the updatedatebase script which will likely fail in the same way: 19.12.00.076 for example.. I think we'de need to clean up at least some of those back to a particular release if we wanted to be consistent.

As it is.. I don't think it's worthwhile blocking this bug.
Comment 9 Jonathan Druart 2020-09-11 07:59:19 UTC
Pushed to master for 20.11, thanks to everybody involved!
Comment 10 Lucas Gass 2020-10-15 21:30:34 UTC
missing dependencies for 20.05.x, no backport