Bug 26365

Summary: Koha/Acquisition/Order.t is failing with MySQL 8
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Test SuiteAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: lucas, martin.renvoize, tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.11.00
Bug Depends on: 23166    
Bug Blocks: 23996    
Attachments: Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted
Bug 26365: Remove comparison with 0000-00-00 for datecancellationprinted

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