Bug 17320 - MySQL 5.7 doesn't work well with SQL using '0000-00-00'
Summary: MySQL 5.7 doesn't work well with SQL using '0000-00-00'
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Mark Tompsett
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 17258
  Show dependency treegraph
 
Reported: 2016-09-20 00:58 UTC by Mark Tompsett
Modified: 2021-12-13 21:09 UTC (History)
4 users (show)

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


Attachments
Bug 17320: Make '0000-00-00' into 0 (4.83 KB, patch)
2016-09-20 00:59 UTC, Mark Tompsett
Details | Diff | Splinter Review
Bug 17320 [DO NOT PUSH] Make 3.14.x "install" (2.26 KB, patch)
2016-10-24 23:53 UTC, Mark Tompsett
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Mark Tompsett 2016-09-20 00:58:34 UTC
Several SQL statements in updatedatabase.pl have '0000-00-00'. A simple substitution to 0 will prevent a default MySQL 5.7 on Ubuntu from barfing.

This patch will touch as far back as 3.0.6, but given the magnitude of other errors while upgrading, finding no 0000-00-00 barfs after the patch up to 3.9 and then finding no 0000-00-00 barfs from 3.14.x upwards seems sufficient to check.
Comment 1 Mark Tompsett 2016-09-20 00:59:44 UTC
Created attachment 55706 [details] [review]
Bug 17320: Make '0000-00-00' into 0

See comment 0.
Comment 2 Jonathan Druart 2016-09-27 08:05:55 UTC
Does it work as expected with older MySQL versions?
Comment 3 Mark Tompsett 2016-09-27 08:15:21 UTC
(In reply to Jonathan Druart from comment #2)
> Does it work as expected with older MySQL versions?

I ran it on Debian 8, and it worked too.
Comment 4 Barton Chittenden 2016-09-27 13:22:42 UTC
Test plan?
Comment 5 Mark Tompsett 2016-09-27 13:24:03 UTC
(In reply to Barton Chittenden from comment #4)
> Test plan?

Run an upgrade, make sure there are no '0000-00-00' related red errors in the web install. :)
Comment 6 Mark Tompsett 2016-09-27 13:24:41 UTC
(In reply to Barton Chittenden from comment #4)
> Test plan?

P.P.S. Comment #0
Comment 7 Mark Tompsett 2016-10-24 19:29:07 UTC
While preparing to test for bug 17234, I went back to 3.14.x, installed an empty install with all the sample data. I had to patch it with 2 changes in the kohastructure and the ignore error from C4/Installer.pm to get it to install with just 3 visible errors. I then changed to master, and run updatedatabase.pl from the command line.

Here is the output (shortened):
mtompset@ubuntu:~/kohaclone$ ./installer/data/mysql/updatedatabase.pl
[SNIP]
Upgrade to 3.21.00.008 done (Bug 7981: Transfer message on return. HomeOrHoldingBranchReturn syspref removed in favour of circulation rules.)
DBD::mysql::db do failed: Incorrect date value: '0000-00-00' for column 'datecancellationprinted' at row 1 [for Statement "
        UPDATE aqorders SET orderstatus='cancelled'
        WHERE (datecancellationprinted IS NOT NULL OR
               datecancellationprinted<>'0000-00-00');
    "] at ./installer/data/mysql/updatedatabase.pl line 10607.
Upgrade to 3.21.00.009 done (Bug 13993: Correct orderstatus for transferred orders)
[SNIP]

Koha clean installs 3.22.x and higher currently, but the upgrade process is still flaky from anything before that.
Comment 8 Mark Tompsett 2016-10-24 23:53:15 UTC
Created attachment 56819 [details] [review]
Bug 17320 [DO NOT PUSH] Make 3.14.x "install"

Tweak fields, so primary key is workable.
Ignore command line mysql error security warning
Comment 9 Mark Tompsett 2016-10-25 00:07:44 UTC
TEST PLAN
---------
On an Ubuntu 16.04 LTS git install:
git checkout -b bug_17320 origin/3.20.x
git bz apply 17320
i
-- apply the DO NOT PUSH
-- in a MySQL client drop and recreate your koha_library DB
-- run a web install and select all data (just 3 issues for Marc21, but not really the point of this patch)
git reset --hard origin/master
./installer/data/mysql/updatedatabase.pl
-- there will be 0000-00-00 messages in the upgrade output.
git reset --hard origin/3.20.x
git bz apply 17320
i
-- apply the DO NOT PUSH
-- in a MySQL client drop and recreate your koha_library DB
-- run a web install and select all data (just 3 issues for Marc21, but not really the point of this patch)
git reset --hard origin/master
git bz apply 17320
i
apply the 0000-00-00 fix patch
./installer/data/mysql/updatedatabase.pl
-- there will be NO 0000-00-00 messages in the upgrade output.
Comment 10 Jonathan Druart 2017-05-09 19:08:03 UTC
The test plan is wrong, we need to test the patch with older versions of MySQL as well.
Comment 11 Dominic Pichette 2017-09-08 20:11:44 UTC
It appeared to need to be signed-off. but after reading the last comment of Jonathan Druart, I think it is better to change the status to Failed QA.
Comment 12 Jonathan Druart 2020-12-18 14:58:14 UTC
After bug 7806 we will assume that we reached a stable state. Please reopen bug reports if still happened when pushed.