Bug 24002 - Test suite is failing on MySQL 8
Summary: Test suite is failing on MySQL 8
Status: Pushed to master
Alias: None
Product: Koha
Classification: Unclassified
Component: Test Suite (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 24029 24030
Blocks: 23996
  Show dependency treegraph
 
Reported: 2019-11-08 11:58 UTC by Jonathan Druart
Modified: 2019-11-13 10:07 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:
19.11.00


Attachments
Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm (1.59 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm (1.58 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm (1.74 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm (3.47 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm (3.78 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm (3.25 KB, patch)
2019-11-08 13:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: One more occurrence of datecancellationprinted (896 bytes, patch)
2019-11-08 14:45 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm (1.65 KB, patch)
2019-11-08 14:45 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm (1.65 KB, patch)
2019-11-08 14:45 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm (1.80 KB, patch)
2019-11-08 14:45 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm (3.54 KB, patch)
2019-11-08 14:46 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm (3.84 KB, patch)
2019-11-08 14:46 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm (3.31 KB, patch)
2019-11-08 14:46 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 Jonathan Druart 2019-11-08 11:58:38 UTC

    
Comment 1 Jonathan Druart 2019-11-08 13:53:15 UTC
Created attachment 95215 [details] [review]
Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm

% prove  xt/author/valid-templates.t

DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement "
     SELECT opac_news.*,timestamp AS newdate,
     borrowers.title AS author_title,
     borrowers.firstname AS author_firstname,
     borrowers.surname AS author_surname
     FROM   opac_news
     LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber
     WHERE   (
        expirationdate >= CURRENT_DATE()
        OR    expirationdate IS NULL
        OR    expirationdate = '00-00-0000'
     )
     AND   DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
     AND   (opac_news.lang = '' OR opac_news.lang = ?)
     AND   (opac_news.branchcode IS NULL OR opac_news.branchcode = ?)
     ORDER BY number
    " with ParamValues: 0='OpacNavRight_', 1=''] at /kohadevbox/koha/C4/NewsChannels.pm line 216.
Comment 2 Jonathan Druart 2019-11-08 13:53:19 UTC
Created attachment 95216 [details] [review]
Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm

t/db_dependent/Bookseller.t .. 3/86 DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement "
        SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
        FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
        LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
        WHERE
            ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
            )
            AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
            AND aqbasket.closedate IS NOT NULL
    "] at /kohadevbox/koha/C4/Bookseller.pm line 100.
Comment 3 Jonathan Druart 2019-11-08 13:53:23 UTC
Created attachment 95217 [details] [review]
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm

DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT   serialid,serialseq, status, planneddate, publisheddate,
        publisheddatetext, notes, routingnotes
       FROM     serial
       WHERE    subscriptionid = ?
       AND      status IN ( 2,4,41,42,43,44,5 )
       ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
      " with ParamValues: 0=8] at /kohadevbox/koha/C4/Serials.pm line 688.
Comment 4 Jonathan Druart 2019-11-08 13:53:27 UTC
Created attachment 95218 [details] [review]
Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm

t/db_dependent/Biblio.t .. 2/12 DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement "
  SELECT    serial.serialid,
            serial.serialseq,
            serial.planneddate,
            serial.publisheddate,
            serial.publisheddatetext,
            serial.status,
            serial.notes as notes,
            year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
            biblio.title as bibliotitle,
            subscription.branchcode AS branchcode,
            subscription.subscriptionid AS subscriptionid
  FROM      serial
  LEFT JOIN subscription ON
          (serial.subscriptionid=subscription.subscriptionid)
  LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
  LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
  WHERE     subscription.biblionumber = ?
  ORDER BY year DESC,
          IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
          serial.subscriptionid
          " with ParamValues: 0='446'] at /kohadevbox/koha/C4/Serials.pm line 482.
Comment 5 Jonathan Druart 2019-11-08 13:53:31 UTC
Created attachment 95219 [details] [review]
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm

t/db_dependent/Acquisition/close_reopen_basket.t .. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "
        SELECT COUNT( DISTINCT( biblionumber ) )
        FROM   aqorders
        WHERE  basketno = ?
            AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
        " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012.
DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "
        SELECT COUNT( DISTINCT( biblionumber ) )
        FROM   aqorders
        WHERE  basketno = ?
            AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
        " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012.

And anticipating other failures
Comment 6 Jonathan Druart 2019-11-08 13:53:35 UTC
Created attachment 95220 [details] [review]
Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm

DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement "
    SELECT aqbasket.basketno,
        aqorders.ordernumber,
        DATE(aqbasket.closedate)  AS orderdate,
        aqbasket.basketname       AS basketname,
        aqbasket.basketgroupid    AS basketgroupid,
        aqbasketgroups.name       AS basketgroupname,
        aqorders.rrp              AS unitpricesupplier,
        aqorders.ecost            AS unitpricelib,
        aqorders.claims_count     AS claims_count,
        aqorders.claimed_date     AS claimed_date,
        aqbudgets.budget_name     AS budget,
        borrowers.branchcode      AS branch,
        aqbooksellers.name        AS supplier,
        aqbooksellers.id          AS supplierid,
        biblio.author, biblio.title,
        biblioitems.publishercode AS publisher,
        biblioitems.publicationyear,
        ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,

        aqorders.quantity - COALESCE(aqorders.quantityreceived,0)                 AS quantity,
        (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
        DATEDIFF(CAST(now() AS date),closedate) AS latesince

    FROM
        aqorders LEFT JOIN biblio     ON biblio.biblionumber         = aqorders.biblionumber
        LEFT JOIN biblioitems         ON biblioitems.biblionumber    = biblio.biblionumber
        LEFT JOIN aqbudgets           ON aqorders.budget_id          = aqbudgets.budget_id,
        aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       = borrowers.borrowernumber
        LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       = aqbooksellers.id
        LEFT JOIN aqbasketgroups      ON aqbasket.basketgroupid      = aqbasketgroups.id
        WHERE aqorders.basketno = aqbasket.basketno
        AND ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
        )
        AND aqbasket.closedate IS NOT NULL
        AND aqorders.datecancellationprinted IS NULL
     AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY))  AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND orderstatus <> 'cancelled'
ORDER BY latesince, basketno, borrowers.branchcode, supplier" with ParamValues: 0=0] at /kohadevbox/koha/C4/Acquisition.pm line 2248.
Comment 7 Jonathan Druart 2019-11-08 14:45:04 UTC
Created attachment 95221 [details] [review]
Bug 24002: One more occurrence of datecancellationprinted
Comment 8 Martin Renvoize 2019-11-08 14:45:51 UTC
Created attachment 95222 [details] [review]
Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm

% prove  xt/author/valid-templates.t

DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement "
     SELECT opac_news.*,timestamp AS newdate,
     borrowers.title AS author_title,
     borrowers.firstname AS author_firstname,
     borrowers.surname AS author_surname
     FROM   opac_news
     LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber
     WHERE   (
        expirationdate >= CURRENT_DATE()
        OR    expirationdate IS NULL
        OR    expirationdate = '00-00-0000'
     )
     AND   DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
     AND   (opac_news.lang = '' OR opac_news.lang = ?)
     AND   (opac_news.branchcode IS NULL OR opac_news.branchcode = ?)
     ORDER BY number
    " with ParamValues: 0='OpacNavRight_', 1=''] at /kohadevbox/koha/C4/NewsChannels.pm line 216.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 9 Martin Renvoize 2019-11-08 14:45:54 UTC
Created attachment 95223 [details] [review]
Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm

t/db_dependent/Bookseller.t .. 3/86 DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement "
        SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
        FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
        LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
        WHERE
            ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
            )
            AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
            AND aqbasket.closedate IS NOT NULL
    "] at /kohadevbox/koha/C4/Bookseller.pm line 100.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 10 Martin Renvoize 2019-11-08 14:45:58 UTC
Created attachment 95224 [details] [review]
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm

DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT   serialid,serialseq, status, planneddate, publisheddate,
        publisheddatetext, notes, routingnotes
       FROM     serial
       WHERE    subscriptionid = ?
       AND      status IN ( 2,4,41,42,43,44,5 )
       ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
      " with ParamValues: 0=8] at /kohadevbox/koha/C4/Serials.pm line 688.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 11 Martin Renvoize 2019-11-08 14:46:01 UTC
Created attachment 95225 [details] [review]
Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm

t/db_dependent/Biblio.t .. 2/12 DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement "
  SELECT    serial.serialid,
            serial.serialseq,
            serial.planneddate,
            serial.publisheddate,
            serial.publisheddatetext,
            serial.status,
            serial.notes as notes,
            year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
            biblio.title as bibliotitle,
            subscription.branchcode AS branchcode,
            subscription.subscriptionid AS subscriptionid
  FROM      serial
  LEFT JOIN subscription ON
          (serial.subscriptionid=subscription.subscriptionid)
  LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
  LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
  WHERE     subscription.biblionumber = ?
  ORDER BY year DESC,
          IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
          serial.subscriptionid
          " with ParamValues: 0='446'] at /kohadevbox/koha/C4/Serials.pm line 482.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 12 Martin Renvoize 2019-11-08 14:46:05 UTC
Created attachment 95226 [details] [review]
Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm

t/db_dependent/Acquisition/close_reopen_basket.t .. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "
        SELECT COUNT( DISTINCT( biblionumber ) )
        FROM   aqorders
        WHERE  basketno = ?
            AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
        " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012.
DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "
        SELECT COUNT( DISTINCT( biblionumber ) )
        FROM   aqorders
        WHERE  basketno = ?
            AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
        " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012.

And anticipating other failures

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 13 Martin Renvoize 2019-11-08 14:46:09 UTC
Created attachment 95227 [details] [review]
Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm

DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement "
    SELECT aqbasket.basketno,
        aqorders.ordernumber,
        DATE(aqbasket.closedate)  AS orderdate,
        aqbasket.basketname       AS basketname,
        aqbasket.basketgroupid    AS basketgroupid,
        aqbasketgroups.name       AS basketgroupname,
        aqorders.rrp              AS unitpricesupplier,
        aqorders.ecost            AS unitpricelib,
        aqorders.claims_count     AS claims_count,
        aqorders.claimed_date     AS claimed_date,
        aqbudgets.budget_name     AS budget,
        borrowers.branchcode      AS branch,
        aqbooksellers.name        AS supplier,
        aqbooksellers.id          AS supplierid,
        biblio.author, biblio.title,
        biblioitems.publishercode AS publisher,
        biblioitems.publicationyear,
        ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,

        aqorders.quantity - COALESCE(aqorders.quantityreceived,0)                 AS quantity,
        (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
        DATEDIFF(CAST(now() AS date),closedate) AS latesince

    FROM
        aqorders LEFT JOIN biblio     ON biblio.biblionumber         = aqorders.biblionumber
        LEFT JOIN biblioitems         ON biblioitems.biblionumber    = biblio.biblionumber
        LEFT JOIN aqbudgets           ON aqorders.budget_id          = aqbudgets.budget_id,
        aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       = borrowers.borrowernumber
        LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       = aqbooksellers.id
        LEFT JOIN aqbasketgroups      ON aqbasket.basketgroupid      = aqbasketgroups.id
        WHERE aqorders.basketno = aqbasket.basketno
        AND ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
        )
        AND aqbasket.closedate IS NOT NULL
        AND aqorders.datecancellationprinted IS NULL
     AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY))  AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND orderstatus <> 'cancelled'
ORDER BY latesince, basketno, borrowers.branchcode, supplier" with ParamValues: 0=0] at /kohadevbox/koha/C4/Acquisition.pm line 2248.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 14 Martin Renvoize 2019-11-08 14:47:35 UTC
Ran tests again M8, saw failures.. applied patches and re-ran tests, full Pass.

Looks good, signing off.

Minor concern is whether we might be missing some DB updates to ensure all tables are filled with expected data to start with..
Comment 15 Jonathan Druart 2019-11-11 08:32:05 UTC
(In reply to Martin Renvoize from comment #14)
> Minor concern is whether we might be missing some DB updates to ensure all
> tables are filled with expected data to start with..

Hum actually it's too late. We cannot longer write such query:

  update opac_news set expirationdate=NULL WHERE expirationdate="0000-00-00";

As it will fail:
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'expirationdate' at row 1
Comment 16 Martin Renvoize 2019-11-12 13:45:24 UTC
So really we shouldn't have any left around already. :)
Comment 17 Martin Renvoize 2019-11-12 13:50:48 UTC
Trivial patches, Passing QA
Comment 18 Martin Renvoize 2019-11-12 13:51:54 UTC
Nice work!

Pushed to master for 19.11.00