Bug 27276 - borrowers-force-messaging-defaults throws Incorrect DATE value: '0000-00-00' even though sql strict mode is dissabled
Summary: borrowers-force-messaging-defaults throws Incorrect DATE value: '0000-00-00' ...
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low major (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 17258 7806
  Show dependency treegraph
 
Reported: 2020-12-18 06:53 UTC by Stefan Norinder
Modified: 2021-06-14 21:35 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Text to go in the release notes:
Version(s) released in:
21.05.00,20.11.01,20.05.07,19.11.14


Attachments
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash (1.79 KB, patch)
2020-12-18 13:29 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash (1.84 KB, patch)
2021-01-03 01:43 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash (1.91 KB, patch)
2021-01-04 13:20 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 27276:: Fix syntax error (990 bytes, patch)
2021-01-04 13:21 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan Norinder 2020-12-18 06:53:32 UTC
When running borrowers-force-messaging-defaults.pl the error message 

DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT DISTINCT bo.borrowernumber, bo.categorycode FROM borrowers bo
LEFT JOIN borrower_message_preferences mp USING (borrowernumber)

is displayed. 

According to documentation this relates to MySql variable sql_mode and that the following behavior can occur if sql_mode contains NO_ZERO_IN_DATE and/or NO_ZERO_DATE. 

I can confirm this by executing 

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE'; 

SELECT * FROM borrowers WHERE borrowers.dateenrolled >= '0000-00-00'

The select query will result in "Incorrect DATE value: '0000-00-00'" 

If I instead write 

set sql_mode='ALLOW_INVALID_DATES';

SELECT * FROM borrowers WHERE borrowers.dateenrolled >= '0000-00-00'

the select query executes correctly and returns a collection of borrowers BUT borrowers-force-messaging-defaults.pl still produces the error. 

Things I've checked 

* restarting MySql service 
* restarting server 
* that  <strict_sql_modes>0</strict_sql_modes> in koha-conf.xml is set to 0
* that /etc/mysql/mysql.conf.d/mysqld.cnf contains sql_mode = "ALLOW_INVALID_DATES"

Is this a bug in Koha or is there some configuration that I'm missing?
Comment 1 Jonathan Druart 2020-12-18 13:27:28 UTC
It's a bug.
Lowering severity a bit however.
Comment 2 Jonathan Druart 2020-12-18 13:29:45 UTC
Created attachment 114520 [details] [review]
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash

MySQL 5.7 does not allow queries using 0000-00-00
Comment 3 Jonathan Druart 2020-12-18 13:30:24 UTC
Stefan, can you try this patch? (I haven't tested it yet!)
Comment 4 Chris Cormack 2021-01-03 01:43:17 UTC
Created attachment 114768 [details] [review]
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash

MySQL 5.7 does not allow queries using 0000-00-00

Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Comment 5 Kyle M Hall 2021-01-04 13:20:56 UTC
Created attachment 114780 [details] [review]
Bug 27276: Prevent borrowers-force-messaging-defaults.pl to crash

MySQL 5.7 does not allow queries using 0000-00-00

Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 6 Kyle M Hall 2021-01-04 13:21:08 UTC
Created attachment 114781 [details] [review]
Bug 27276:: Fix syntax error

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 7 Jonathan Druart 2021-01-04 15:34:36 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 8 Fridolin Somers 2021-01-05 08:31:01 UTC
Pushed to 20.11.x for 20.11.01
Comment 9 Andrew Fuerste-Henry 2021-01-14 18:35:19 UTC
Pushed to 20.05.x for 20.05.07
Comment 10 Victor Grousset/tuxayo 2021-01-15 00:48:01 UTC
Backported: Pushed to 19.11.x branch for 19.11.14