[Koha-bugs] [Bug 27276] New: borrowers-force-messaging-defaults throws Incorrect DATE value: '0000-00-00' even though sql strict mode is dissabled

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27276

            Bug ID: 27276
           Summary: borrowers-force-messaging-defaults throws Incorrect
                    DATE value: '0000-00-00' even though sql strict mode
                    is dissabled
 Change sponsored?: ---
           Product: Koha
           Version: 20.11
          Hardware: All
                OS: All
            Status: NEW
          Severity: critical
          Priority: P5 - low
         Component: Command-line Utilities
          Assignee: koha-bugs at lists.koha-community.org
          Reporter: somesecretemail at mailinator.com
        QA Contact: testopia at bugs.koha-community.org
                CC: robin at catalyst.net.nz

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?

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.