- Koha-bugs - lists.koha-community.org

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 --- Comment #22 from Martin Renvoize <martin.renvoize(a)ptfs-europe.com> --- (In reply to Marcel de Rooy from comment #19) > Although this looks quite good to me, it is really hard to be convinced that > all appearances of 0000-00-00 in the data now are caught correctly by the > codebase. > > I found at least one issue (no blocker): > select count(*) from items where dateaccessioned= '0000-00-00'; > +----------+ > | count(*) | > +----------+ > | 335 | > +----------+ > 1 row in set (0.12 sec) > This comes from a production database. > So I tested with an item. Set dateaccessioned to 0*. Open item editor. You > see the 0* on the form. Tab thru the field so that the date gets FOCUS. This > will update the 0* in the old situation, but not in the new one (obviously). > Note that the CLICK event uses the force parameter and will set the date. > > Could you add this one to the atomicupdate too? > And please provide me some evidence that we found the last case ;) For > instance by checking date columns in a larger production database being used > for a longer time.. > > PQA for now. Thanks for QAing this Marcel, your right, I should have detailed my testing :(.. I basically looked at where the code changed and tried to trigger those actions via the UI to check things weren't broken. As for this comment, I'm pretty sure we'll have left behind some bad data, but it should get picked up by upgrades now that we throw errors in the updatedatabase script. We could I suppose write a one off atomicupdate to check and fix all possible datefields at this point in time.. I used the following to get a list of such fields.... we have 210! ''' 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.

1 0

[Bug 6473] Test bug for Git-bz
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=6473 Katrin Fischer <katrin.fischer(a)bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Passed QA -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 6473] Test bug for Git-bz
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=6473 Katrin Fischer <katrin.fischer(a)bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 6473] Test bug for Git-bz
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=6473 Katrin Fischer <katrin.fischer(a)bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact| |m.de.rooy(a)rijksmuseum.nl -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #117267|0 |1 is obsolete| | --- Comment #21 from Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> --- Created attachment 117362 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117362&action=e… Bug 7806: (follow-up) Update to use sanitize_zero_date Signed-off-by: Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Signed-off-by: Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #117266|0 |1 is obsolete| | --- Comment #20 from Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> --- Created attachment 117361 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=117361&action=e… Bug 7806: Fix remaining occurrences of 0000-00-00 We should remove all SQL queries that contain 0000-00-00 and finally assume we do not longer have such value in our DB (for date type) We already dealt with such values in previous update DB entries. The 2 added by this one haven't been replaced already. The code will now assume that either a valid date exist, or NULL/undef. Test plan: QA review is needed and test of the different places where code is modified. Not sure about the change from reports/issues_avg_stats.pl Signed-off-by: Martin Renvoize <martin.renvoize(a)ptfs-europe.com> Signed-off-by: Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #19 from Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> --- Although this looks quite good to me, it is really hard to be convinced that all appearances of 0000-00-00 in the data now are caught correctly by the codebase. I found at least one issue (no blocker): select count(*) from items where dateaccessioned= '0000-00-00'; +----------+ | count(*) | +----------+ | 335 | +----------+ 1 row in set (0.12 sec) This comes from a production database. So I tested with an item. Set dateaccessioned to 0*. Open item editor. You see the 0* on the form. Tab thru the field so that the date gets FOCUS. This will update the 0* in the old situation, but not in the new one (obviously). Note that the CLICK event uses the force parameter and will set the date. Could you add this one to the atomicupdate too? And please provide me some evidence that we found the last case ;) For instance by checking date columns in a larger production database being used for a longer time.. PQA for now. -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 7806] Don't use 0000-00-00 to signal a non-existing date
by bugzilla-daemon＠bugs.koha-community.org 26 Feb '21

26 Feb '21

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806 --- Comment #18 from Marcel de Rooy <m.de.rooy(a)rijksmuseum.nl> --- (In reply to Jonathan Druart from comment #9) > Not sure about the change from reports/issues_avg_stats.pl Since returndate comes from oldissues and you sanitize the field in the atomic update, we should be fine imo. -- You are receiving this mail because: You are watching all bug changes.

1 0