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.
Created attachment 55706 [details] [review] Bug 17320: Make '0000-00-00' into 0 See comment 0.
Does it work as expected with older MySQL versions?
(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.
Test plan?
(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. :)
(In reply to Barton Chittenden from comment #4) > Test plan? P.P.S. Comment #0
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.
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
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.
The test plan is wrong, we need to test the patch with older versions of MySQL as well.
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.
After bug 7806 we will assume that we reached a stable state. Please reopen bug reports if still happened when pushed.