[Bug 26997] New: Database Mysql Version 8.0.22 failed to Update During Upgrade
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Bug ID: 26997 Summary: Database Mysql Version 8.0.22 failed to Update During Upgrade Change sponsored?: --- Product: Koha Version: 20.05 Hardware: All OS: Linux Status: NEW Severity: critical Priority: P5 - low Component: Acquisitions Assignee: koha-bugs@lists.koha-community.org Reporter: halim4977@gmail.com QA Contact: testopia@bugs.koha-community.org 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|critical |normal Assignee|koha-bugs@lists.koha-commun |jonathan.druart@bugs.koha-c |ity.org |ommunity.org CC| |jonathan.druart@bugs.koha-c | |ommunity.org Version|20.05 |master Status|NEW |ASSIGNED --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Halim, those errors are not blocking the update process, you can ignore them. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #2 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 113495 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=113495&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #3 from Halim <halim4977@gmail.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #4 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 113600 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=113600&action=edit 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) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Halim, this patch should fix your problem. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |23996 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23996 [Bug 23996] MySQL 8 support [Omnibus] -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |RM_priority -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |7806 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 [Bug 7806] Don't use 0000-00-00 to signal a non-existing date -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #113495|0 |1 is obsolete| | --- Comment #6 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 114526 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=114526&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #113600|0 |1 is obsolete| | --- Comment #7 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 114527 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=114527&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off CC| |martin.renvoize@ptfs-europe | |.com --- Comment #8 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I've tested this myself now and am happy with the result.. signing off. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |25026 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=25026 [Bug 25026] RaiseError must be set for the UI -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Manos PETRIDIS <egpetridis@yahoo.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |egpetridis@yahoo.com --- Comment #9 from Manos PETRIDIS <egpetridis@yahoo.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks|25026 | See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=25026 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=25026 [Bug 25026] RaiseError must be set for the UI -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- (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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- ''' 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; ''' -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |major --- Comment #12 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Upping priority, this needs to be pushed and backported ASAP. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #13 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 117038 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117038&action=edit 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #14 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- 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)? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #15 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #114526|0 |1 is obsolete| | --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 117178 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117178&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #114527|0 |1 is obsolete| | --- Comment #17 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 117179 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117179&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #117038|0 |1 is obsolete| | --- Comment #18 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 117180 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117180&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #19 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 117181 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117181&action=edit Bug 26997: (QA follow-up) Fix Typo Feel free to squash this ;) Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #20 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |21.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 --- Comment #21 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Pushed to master for 21.05, thanks to everybody involved! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable CC| |fridolin.somers@biblibre.co | |m Version(s)|21.05.00 |21.05.00,20.11.04 released in| | --- Comment #22 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 20.11.x for 20.11.04 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #117180|0 |1 is obsolete| | Attachment #117181|0 |1 is obsolete| | --- Comment #23 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 117428 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117428&action=edit 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> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> Bug 26997: (QA follow-up) Fix Typo Feel free to squash this ;) Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> JD Amended patch - remove changes prior to 3.20 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |27821 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27821 [Bug 27821] sanitize_zero_date does not handle datetime -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|RM_priority | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com Version(s)|21.05.00,20.11.04 |21.05.00,20.11.04,20.05.10 released in| | Status|Pushed to stable |Pushed to oldstable --- Comment #24 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- Pushed to 20.05.x for 20.05.10 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26997 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |victor@tuxayo.net Version(s)|21.05.00,20.11.04,20.05.10 |21.05.00,20.11.04,20.05.10, released in| |19.11.16 Status|Pushed to oldstable |Pushed to oldoldstable --- Comment #25 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Backported: Pushed to 19.11.x branch for 19.11.16 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org