Bug 29204 - Error 500 when execute Circulation report with date period
Summary: Error 500 when execute Circulation report with date period
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 23996
  Show dependency treegraph
 
Reported: 2021-10-11 12:46 UTC by José Anjos
Modified: 2022-06-06 20:31 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
21.11.00,21.05.05,20.11.12,20.05.18


Attachments
Bug 29204: Fix reports/issues_stats.pl for MySQL 8 (1.55 KB, patch)
2021-10-19 09:09 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 29204: Fix reports/issues_stats.pl for MySQL 8 (1.70 KB, patch)
2021-11-06 14:12 UTC, Joonas Kylmälä
Details | Diff | Splinter Review
Bug 29204: (QA follow-up) Fix variable name (1.27 KB, patch)
2021-11-06 14:19 UTC, Joonas Kylmälä
Details | Diff | Splinter Review
Bug 29204: Fix reports/issues_stats.pl for MySQL 8 (1.76 KB, patch)
2021-11-08 12:02 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 29204: (QA follow-up) Fix variable name (1.33 KB, patch)
2021-11-08 12:02 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 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