Bug 29204

Summary: Error 500 when execute Circulation report with date period
Product: Koha Reporter: José Anjos <joseanjos>
Component: ReportsAssignee: Jonathan Druart <jonathan.druart+koha>
Status: Pushed to oldoldstable --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: andriiverem, fridolin.somers, jonathan.druart+koha, joonas.kylmala, kyle, martin.renvoize, nugged, victor, wainuiwitikapark
Version: unspecified   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Text to go in the release notes:
Version(s) released in:
21.11.00,21.05.05,20.11.12,20.05.18
Bug Depends on:    
Bug Blocks: 23996    
Attachments: Bug 29204: Fix reports/issues_stats.pl for MySQL 8
Bug 29204: Fix reports/issues_stats.pl for MySQL 8
Bug 29204: (QA follow-up) Fix variable name
Bug 29204: Fix reports/issues_stats.pl for MySQL 8
Bug 29204: (QA follow-up) Fix variable name

Description José Anjos 2021-10-11 12:46:14 UTC
This error doesn't affect version 20.11.03
To reproduce:
Go to Reports -> Circulation
On Period select "Row" or "Column" and choose a date
Submit

You will have a Error 500.
Logs, intranet-error.log:
AH01215: main::calculate(): DBI Exception: DBD::mysql::st execute failed: Incorrect DATETIME value: '' [for Statement "SELECT distinctrow date_format(`datetime`,"%Y-%m-%d") FROM statistics LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber)  WHERE datetime IS NOT NULL AND datetime != ''  AND datetime BETWEEN ? AND ?  AND                type ='issue'  group by date_format(`datetime`,"%Y-%m-%d") order by date_format(`datetime`,"%Y-%m-%d") " with ParamValues: 0='2021-10-01', 1='2021-10-11 23:59:59'] at /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl line 104: /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl,
Comment 1 Andrii Veremeienko 2021-10-16 08:30:06 UTC Comment hidden (obsolete)
Comment 2 Andrii Veremeienko 2021-10-16 08:31:38 UTC
Hello José Anjos,
I tried to reproduce this error on the latest vesion and didn't receive an error with 500 code.
Can you check if it's still reproducible and if yes - please provide more details.
Thank you!
Comment 3 José Anjos 2021-10-18 09:52:03 UTC
Hi Andrii Veremeienko,

I confirm the problem.
I have 2 VM's with Koha and I have the problem on both now.
The production VM (with the problem) have version 21.05.04 Ubuntu 20.04.03, Perl 5.03, mysql 8.0.26

The test VM (without problem) had version 20.11.03 Ubuntu 20.04.03, Perl 5.03, mysql 8.0.26
Then, to test if it was a koha version problem, I've upgraded koha to version 21.05.04. Now I have the error.

To reproduce, just go to Reports -> Circulation
On Period select "Row" or "Column"
Choose a date or leave it blank
Submit, and I get Error 500
I've tested with another language and I steal have the error.
Cheers
Comment 4 Jonathan Druart 2021-10-19 08:57:04 UTC
Do you have 'strict_sql_modes' turned on in your koha configuration file?
Comment 5 Jonathan Druart 2021-10-19 09:00:56 UTC
(In reply to Jonathan Druart from comment #4)
> Do you have 'strict_sql_modes' turned on in your koha configuration file?

No, it's not related. It's because of MySQL 8.
Comment 6 Jonathan Druart 2021-10-19 09:09:17 UTC
Created attachment 126475 [details] [review]
Bug 29204: Fix reports/issues_stats.pl for MySQL 8

Error was: Incorrect DATETIME value: ''

Test plan
Go to Reports -> Circulation
On Period select "Row" or "Column" and choose a date
Submit
Comment 7 José Anjos 2021-10-19 12:17:30 UTC
Tested in my Test server and it worked fine :)
Thank you
Comment 8 Joonas Kylmälä 2021-11-06 14:12:01 UTC
Created attachment 127409 [details] [review]
Bug 29204: Fix reports/issues_stats.pl for MySQL 8

Error was: Incorrect DATETIME value: ''

Test plan
Go to Reports -> Circulation
On Period select "Row" or "Column" and choose a date
Submit

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Comment 9 Joonas Kylmälä 2021-11-06 14:13:17 UTC
To be able to test this please remove the strict mode from koha-conf.xml:

  <strict_sql_modes>0</strict_sql_modes>

Spent a while figuring out why the column/row radio button selection doesn't work.
Comment 10 Joonas Kylmälä 2021-11-06 14:19:41 UTC
Created attachment 127410 [details] [review]
Bug 29204: (QA follow-up) Fix variable name

The patch "Bug 29204: Fix reports/issues_stats.pl for MySQL 8"
accidentally changed the variable name from $strsth2 to $strsth

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Comment 11 Martin Renvoize 2021-11-08 12:02:05 UTC
Created attachment 127442 [details] [review]
Bug 29204: Fix reports/issues_stats.pl for MySQL 8

Error was: Incorrect DATETIME value: ''

Test plan
Go to Reports -> Circulation
On Period select "Row" or "Column" and choose a date
Submit

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 12 Martin Renvoize 2021-11-08 12:02:08 UTC
Created attachment 127443 [details] [review]
Bug 29204: (QA follow-up) Fix variable name

The patch "Bug 29204: Fix reports/issues_stats.pl for MySQL 8"
accidentally changed the variable name from $strsth2 to $strsth

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 13 Martin Renvoize 2021-11-08 12:02:35 UTC
Nice simple fix.. all works as expected. PQA
Comment 14 Jonathan Druart 2021-11-15 11:39:46 UTC
Pushed to master for 21.11, thanks to everybody involved!
Comment 15 Kyle M Hall 2021-11-19 15:50:06 UTC
Pushed to 21.05.x for 21.05.05
Comment 16 Fridolin Somers 2021-11-19 21:07:22 UTC
Pushed to 20.11.x for 20.11.12
Comment 17 Victor Grousset/tuxayo 2021-11-21 05:13:05 UTC
Backported: Pushed to 20.05.x branch for 20.05.18
Comment 18 wainuiwitikapark 2021-11-23 00:23:37 UTC
Not backporting to 19.11.x - LTS version will just backport security patches