Bug 26997

Summary: Database Mysql Version 8.0.22 failed to Update During Upgrade
Product: Koha Reporter: Halim <halim4977>
Component: AcquisitionsAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: andrewfh, egpetridis, fridolin.somers, jonathan.druart, martin.renvoize, victor
Version: Main   
Hardware: All   
OS: Linux   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=25026
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
21.05.00,20.11.04,20.05.10,19.11.16
Bug Depends on:    
Bug Blocks: 7806, 23996, 27821    
Attachments: Bug 26997: Fix upgrade process under MySQL 8
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted
Bug 26997: Fix upgrade process under MySQL 8
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted
Bug 26997: CAST AS CHAR to prevent the error
Bug 26997: Fix upgrade process under MySQL 8
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted
Bug 26997: CAST AS CHAR to prevent the error
Bug 26997: (QA follow-up) Fix Typo
Bug 26997: CAST AS CHAR to prevent the error

Description Halim 2020-11-11 11:17:11 UTC
Hi,
Facing Error During Running Koha-Upgrade-Scheme from 17.11 to 20.05.
Details Error 
DBD::mysql::db do failed: Incorrect DATE value: '0000-00-00' [for Statement "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 17632.
DBD::mysql::db do failed: Incorrect DATE value: '0000-00-00' [for Statement "UPDATE suggestions SET manageddate = '1970-01-01' WHERE manageddate = '0000-00-00';"] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 17635.
DBD::mysql::db do failed: Incorrect DATE value: '0000-00-00' [for Statement "UPDATE suggestions SET accepteddate = '1970-01-01' WHERE accepteddate = '0000-00-00';"] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 17638.

Is there any way to fix this?
Please advise.

Thanks
Halim
Comment 1 Jonathan Druart 2020-11-11 13:22:47 UTC
Halim, those errors are not blocking the update process, you can ignore them.
Comment 2 Jonathan Druart 2020-11-11 13:25:48 UTC
Created attachment 113495 [details] [review]
Bug 26997: Fix upgrade process under MySQL 8

If you are coming from an old version of Koha, you may face an upgrade
error for the SQL query using 0000-00-00.
The idea was to fix it, but actually the DBMS will raise an error (not
blocking the upgrade process however).

We can hide them to not cause confusion

Errors are like:
DBD::mysql::db do failed: Incorrect date value: '0000-00-00' for column 'suggesteddate' at row 1 [for Statement "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"] at /kohadevbox/koha/installer/data/mysql/updatedatabase.pl line 23312.

Test plan:
Checkout an old version of Koha (3.20, not older)
Insert a dump, install Koha
Checkout a recent version of Koha, apply the patch (or use master)
Execute the upgrade process
You should not see the errors
Comment 3 Halim 2020-11-12 00:19:49 UTC
Hi Jonathan,
Thanks for the feedback.
After the upgrade, found errors as follows :

At Acquisition main page (/cgi-bin/koha/acqui/acqui-home.pl), when click the total under column Ordered or Spent, it will bring you to software errors. (Software error: An error occurred fetching records: Incorrect DATE value: '0000-00-00' at /usr/share/koha/intranet/cgi-bin/acqui/spent.pl line 101) and (An Error occurred fetching records: Incorrect DATE value: '0000-00-00' at /usr/share/koha/intranet/cgi-bin/acqui/ordered.pl line 88).

How do I fix this?

Thanks.
Comment 4 Jonathan Druart 2020-11-13 13:01:03 UTC
Created attachment 113600 [details] [review]
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted

This patch remove the 0000-00-00 from the WHERE condition from
ordered.pl and spent.pl to prevent an error under MySQL 8

It also fixes the wrong values in DB (if possible, ie. under other DBMS
that MySQL 8)
Comment 5 Jonathan Druart 2020-11-13 13:01:30 UTC
Halim, this patch should fix your problem.
Comment 6 Martin Renvoize 2020-12-18 14:26:36 UTC
Created attachment 114526 [details] [review]
Bug 26997: Fix upgrade process under MySQL 8

If you are coming from an old version of Koha, you may face an upgrade
error for the SQL query using 0000-00-00.
The idea was to fix it, but actually the DBMS will raise an error (not
blocking the upgrade process however).

We can hide them to not cause confusion

Errors are like:
DBD::mysql::db do failed: Incorrect date value: '0000-00-00' for column 'suggesteddate' at row 1 [for Statement "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"] at /kohadevbox/koha/installer/data/mysql/updatedatabase.pl line 23312.

Test plan:
Checkout an old version of Koha (3.20, not older)
Insert a dump, install Koha
Checkout a recent version of Koha, apply the patch (or use master)
Execute the upgrade process
You should not see the errors

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 7 Martin Renvoize 2020-12-18 14:26:38 UTC
Created attachment 114527 [details] [review]
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted

This patch remove the 0000-00-00 from the WHERE condition from
ordered.pl and spent.pl to prevent an error under MySQL 8

It also fixes the wrong values in DB (if possible, ie. under other DBMS
that MySQL 8)

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize 2020-12-18 14:27:06 UTC
I've tested this myself now and am happy with the result.. signing off.
Comment 9 Manos PETRIDIS 2021-02-03 15:00:04 UTC
I wonder if 
UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE NOT suggesteddate > '1970-01-01';
might succeed in performing the intended update. Values '0000-00-00' would be interpeted as FALSE, and NOT FALSE would allow the update to proceed.
Comment 10 Martin Renvoize 2021-02-10 16:44:16 UTC
(In reply to Manos PETRIDIS from comment #9)
> I wonder if 
> UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE NOT suggesteddate
> > '1970-01-01';
> might succeed in performing the intended update. Values '0000-00-00' would
> be interpeted as FALSE, and NOT FALSE would allow the update to proceed.

I just tried this locally and it works well.. 

I think however, it may be worth putting into a generic 'fix all dates' script..

I'll take a look at doing that.
Comment 11 Martin Renvoize 2021-02-10 17:00:40 UTC
'''
SELECT col.table_schema     AS database_name,
       col.table_name,
       col.ordinal_position AS column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
FROM   information_schema.columns col
       JOIN information_schema.tables tab
         ON tab.table_schema = col.table_schema
            AND tab.table_name = col.table_name
            AND tab.table_type = 'BASE TABLE'
WHERE  col.data_type IN ( 'date', 'time', 'datetime', 'year', 'timestamp' )
       AND col.table_schema NOT IN ( 'information_schema', 'sys',
                                     'performance_schema',
                                     'mysql' )
       AND col.table_schema = 'database_name'
ORDER  BY col.table_schema,
          col.table_name,
          col.ordinal_position;
'''
Comment 12 Jonathan Druart 2021-02-19 07:47:08 UTC
Upping priority, this needs to be pushed and backported ASAP.
Comment 13 Jonathan Druart 2021-02-19 09:53:49 UTC
Created attachment 117038 [details] [review]
Bug 26997: CAST AS CHAR to prevent the error

To prevent the DBMS to fail on 0000-00-00 we can cast it as a char and
compare.
This patch also moves to a subroutine, in case we need to adjust (or
reuse) it later.
Comment 14 Martin Renvoize 2021-02-19 12:33:48 UTC
This final addition works well.. and I agree we should retrospectively apply the approach to older updates.. 

However, what if some of those fields where 'NOT NULL' at the time.. then the update will fail again.. we could either add handling inside the sub to alter the table to allow null for the failed field.. or use '1000-01-01' an arbitrary early date (it's the earliest date I'm told MySQL supports)?
Comment 15 Martin Renvoize 2021-02-19 12:37:45 UTC
I think we're safe with my above comment.. now we die on such failures we can, and in my opinion should, leave those possible early cases of weirdness for the administrator to fix manually.
Comment 16 Martin Renvoize 2021-02-23 09:07:24 UTC
Created attachment 117178 [details] [review]
Bug 26997: Fix upgrade process under MySQL 8

If you are coming from an old version of Koha, you may face an upgrade
error for the SQL query using 0000-00-00.
The idea was to fix it, but actually the DBMS will raise an error (not
blocking the upgrade process however).

We can hide them to not cause confusion

Errors are like:
DBD::mysql::db do failed: Incorrect date value: '0000-00-00' for column 'suggesteddate' at row 1 [for Statement "UPDATE suggestions SET suggesteddate = '1970-01-01' WHERE suggesteddate = '0000-00-00';"] at /kohadevbox/koha/installer/data/mysql/updatedatabase.pl line 23312.

Test plan:
Checkout an old version of Koha (3.20, not older)
Insert a dump, install Koha
Checkout a recent version of Koha, apply the patch (or use master)
Execute the upgrade process
You should not see the errors

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 17 Martin Renvoize 2021-02-23 09:07:28 UTC
Created attachment 117179 [details] [review]
Bug 26997: Remove 0000-00-00 from aqorders.datecancellationprinted

This patch remove the 0000-00-00 from the WHERE condition from
ordered.pl and spent.pl to prevent an error under MySQL 8

It also fixes the wrong values in DB (if possible, ie. under other DBMS
that MySQL 8)

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 18 Martin Renvoize 2021-02-23 09:07:31 UTC
Created attachment 117180 [details] [review]
Bug 26997: CAST AS CHAR to prevent the error

To prevent the DBMS to fail on 0000-00-00 we can cast it as a char and
compare.
This patch also moves to a subroutine, in case we need to adjust (or
reuse) it later.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 19 Martin Renvoize 2021-02-23 09:07:34 UTC
Created attachment 117181 [details] [review]
Bug 26997: (QA follow-up) Fix Typo

Feel free to squash this ;)

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 20 Martin Renvoize 2021-02-23 09:08:50 UTC
Tested against a couple of versions of MariaDB and Mysql 8.. with a database that exhibited the problem prior to the fix.. 

Works well and resolves a major... I think we should go with it.

Passing QA
Comment 21 Jonathan Druart 2021-02-23 12:21:30 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 22 Fridolin Somers 2021-02-25 09:24:08 UTC
Pushed to 20.11.x for 20.11.04
Comment 23 Jonathan Druart 2021-03-01 09:33:32 UTC Comment hidden (obsolete)
Comment 24 Andrew Fuerste-Henry 2021-03-04 20:55:47 UTC
Pushed to 20.05.x for 20.05.10
Comment 25 Victor Grousset/tuxayo 2021-03-18 20:09:53 UTC
Backported: Pushed to 19.11.x branch for 19.11.16