Summary: | We should attempt to fix/identify all cases where '0000-00-00' may still remain in the database | ||
---|---|---|---|
Product: | Koha | Reporter: | Martin Renvoize <martin.renvoize> |
Component: | Database | Assignee: | wainuiwitikapark |
Status: | Failed QA --- | QA Contact: | Testopia <testopia> |
Severity: | enhancement | ||
Priority: | P5 - low | CC: | david, dcook, jonathan.druart, Laura.escamilla, lucas, wainuiwitikapark |
Version: | Main | ||
Hardware: | All | ||
OS: | All | ||
Change sponsored?: | --- | Patch complexity: | --- |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: |
This new script identifies patron fields that have '0000-00-00' for date fields (for example: dateofbirth) and updates them to 'NULL'. Date fields with a value of '000-00-00' can cause upgrade and API issues, stop the autocomplete patron search working, and generate a 500 internal server error issue with normal patron searching.
|
Version(s) released in: | |
Bug Depends on: | 18016, 28440 | ||
Bug Blocks: | |||
Attachments: |
Bug 31143: Update patrons with dates of 0000-00-00 to NULL
Bug 31143: Update patrons with dates of 0000-00-00 to NULL Bug 31143: Update patrons with dates of 0000-00-00 to NULL |
Description
Martin Renvoize
2022-07-12 16:05:40 UTC
Created attachment 154299 [details] [review] Bug 31143: Update patrons with dates of 0000-00-00 to NULL To test: 1) Change a patron's value of one or more of the following to 0000-00-00: dateofbirth dateenrolled dateexpiry date_renewed 2) Run the fix_patron_dates.pl (-c -v) 3) Notice the value has changed from 0000-00-00 to NULL Sponsored by: Toi Ohomai Institute of Technology, New Zealand Hi! Thank you for working on this patch. I ran into this error: Missing right curly or square bracket at misc/cronjobs/fix_patron_dates.pl line 53, at end of line syntax error at misc/cronjobs/fix_patron_dates.pl line 53, at EOF After adding the curly this works as described! I'm marking this as Failed QA for now, but once the patch is updated I'll be happy to sign off! Created attachment 154399 [details] [review] Bug 31143: Update patrons with dates of 0000-00-00 to NULL To test: 1) Change a patron's value of one or more of the following to 0000-00-00: dateofbirth dateenrolled dateexpiry date_renewed 2) Run the fix_patron_dates.pl (-c -v) 3) Notice the value has changed from 0000-00-00 to NULL Sponsored by: Toi Ohomai Institute of Technology, New Zealand (In reply to Laura Escamilla from comment #2) > Hi! Thank you for working on this patch. I ran into this error: > > Missing right curly or square bracket at misc/cronjobs/fix_patron_dates.pl > line 53, at end of line > syntax error at misc/cronjobs/fix_patron_dates.pl line 53, at EOF > > After adding the curly this works as described! I'm marking this as Failed > QA for now, but once the patch is updated I'll be happy to sign off! Thanks Laura for pointing that out. I have added the curly bracket to the end of the file and reuploaded the patch :) Created attachment 155964 [details] [review] Bug 31143: Update patrons with dates of 0000-00-00 to NULL To test: 1) Change a patron's value of one or more of the following to 0000-00-00: dateofbirth dateenrolled dateexpiry date_renewed 2) Run the fix_patron_dates.pl (-c -v) 3) Notice the value has changed from 0000-00-00 to NULL Sponsored by: Toi Ohomai Institute of Technology, New Zealand Signed-off-by: David Nind <david@davidnind.com> Testing notes (using KTD): 1. Before applying the patch, I updated a couple of patron records using this SQL (Mary (49) and Henry (19)): . koha-mysql kohadev . update borrowers set dateofbirth ="0000-00-00" where borrowernumber = "49"; . update borrowers set date_renewed ="0000-00-00" where borrowernumber = "19"; 2. Note that the values updated now have a value of 0000-00-00 (SQL: select * from borrowers where borrowernumber = 19;). 3. In the staff interface, go to patrons. Note that the autocomplete for the search in the header doesn't work, and that you get a 500 error if you use the search on the left-hand side: Something went wrong when loading the table. 500: Internal Server Error. Month out of range. 4. Apply the patch (and restart_all). 5. Run the new script: misc/cronjobs/fix_patron_dates.pl -c -v 6. Output is: 2 wrong dates found Some other notes: 1. If you run when there are no wrong dates, you get this (with the -c and -v options): Use of uninitialized value $count in concatenation (.) or string at misc/cronjobs/fix_patron_dates.pl line 53. wrong dates found 2. I'm not sure of the protocol, but for many scripts: - you can add the -h or --help and get quick help with a description, what the different options do, and some examples. - normally the help would have the options, like -v, etc (in the script currently it just has c or v) - sometimes with verbose options, it may list the things changed 3. These could probably be added as new bugs, if determined as warranted. This has been done already. Are you sure it's not coming from migration/import instead? I would add a check in misc/maintenance/search_for_data_inconsistencies.pl instead. IMO it does not make sense to have such specific cronjobs. (In reply to Jonathan Druart from comment #7) > This has been done already. Are you sure it's not coming from > migration/import instead? Just curious where this has been dealt with already. (In reply to Lucas Gass from comment #8) > (In reply to Jonathan Druart from comment #7) > > This has been done already. Are you sure it's not coming from > > migration/import instead? > > Just curious where this has been dealt with already. Search for "sanitize_zero_date" in updatedatabase.pl |