Description
Halim
2020-11-11 11:17:11 UTC
Halim, those errors are not blocking the update process, you can ignore them. 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 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. 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) Halim, this patch should fix your problem. 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> 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> I've tested this myself now and am happy with the result.. signing off. 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. (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. ''' 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; ''' Upping priority, this needs to be pushed and backported ASAP. 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. 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)? 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. 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> 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> 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> 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> 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 Pushed to master for 21.05, thanks to everybody involved! Pushed to 20.11.x for 20.11.04 Created attachment 117428 [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> 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 Pushed to 20.05.x for 20.05.10 Backported: Pushed to 19.11.x branch for 19.11.16 |