[Bug 39558] New: Timestamps on biblio biblioitems and biblio_metadata are not in sync
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Bug ID: 39558 Summary: Timestamps on biblio biblioitems and biblio_metadata are not in sync Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: MARC Bibliographic data support Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org On bug 39397 it was found we were returning the wrong timestamp when querying biblios, additionally we query on the biblio.timestamp which may not be updated if a biblio edit does not affect the data in the biblios table. As the three tables are so closely tied, I believe we should maintain a singular timestamp between - so any edit shows that the biblio was updated. -- 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=39558 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch Status|NEW |Needs Signoff -- 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=39558 --- Comment #1 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 180616 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=180616&action=edit Bug 39558: Update biblio and biblioitems timestamps explicitly This patch simply adds 'timestamp = current_timestamp()' to the SQL statements run when editing a biblio. This ensures the tables timestamps are kept in sync To test: 1 - Choose a biblio in the database and make an update to a 650 field 2 - Check the timestamps in MySQL: SELECT biblio.timestamp, biblioitems.timestamp, biblio_metadata.timestamp FROM biblio JOIN biblioitems USING (biblionumber) JOIN biblio_metadata USING (biblionumber) WHERE biblionumber=9; 3 - Note the timestamps 4 - Edit the same 650 again 5 - Check the timestamps and note that the biblio_metadata is updated, but the others are not 6 - Apply patch, restart all 7 - Update the 650 again 8 - Check the timestamps, all are updated! 9 - Sign off -- 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=39558 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | CC| |katrin.fischer@bsz-bw.de, | |tomascohen@gmail.com -- 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=39558 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #180616|0 |1 is obsolete| | --- Comment #2 from David Nind <david@davidnind.com> --- Created attachment 180624 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=180624&action=edit Bug 39558: Update biblio and biblioitems timestamps explicitly This patch simply adds 'timestamp = current_timestamp()' to the SQL statements run when editing a biblio. This ensures the tables timestamps are kept in sync To test: 1 - Choose a biblio in the database and make an update to a 650 field 2 - Check the timestamps in MySQL: SELECT biblio.timestamp, biblioitems.timestamp, biblio_metadata.timestamp FROM biblio JOIN biblioitems USING (biblionumber) JOIN biblio_metadata USING (biblionumber) WHERE biblionumber=9; 3 - Note the timestamps 4 - Edit the same 650 again 5 - Check the timestamps and note that the biblio_metadata is updated, but the others are not 6 - Apply patch, restart all 7 - Update the 650 again 8 - Check the timestamps, all are updated! 9 - Sign off Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |In Discussion --- Comment #3 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- We are discussing this on bug 39397 too. I think that we should normally leave the timestamp update to sql. If the specific table changed, change the timestamp. If not, dont touch it. Moving this to In discussion too. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=39397 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #4 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- (In reply to Marcel de Rooy from comment #3)
We are discussing this on bug 39397 too. I think that we should normally leave the timestamp update to sql. If the specific table changed, change the timestamp. If not, dont touch it.
Moving this to In discussion too.
The current situation is weird though, because it's not just 'did them marc change' - but 'did a specific field we have mapped changed' Right now we have to check three tables to decide if a "record" has had changes. The need for this comes from syncing with outside systems - 'biblios' is the endpoint for this - joining to the 'biblio_metadata' table makes any queries more expensive - with the timestamps in sync we can do a simple query to fetch the recently updated records. Is there a use case to know that the record was updated, but not the title or author fields? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #5 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Nick Clemens (kidclamp) from comment #4)
(In reply to Marcel de Rooy from comment #3)
We are discussing this on bug 39397 too. I think that we should normally leave the timestamp update to sql. If the specific table changed, change the timestamp. If not, dont touch it.
Moving this to In discussion too.
The current situation is weird though, because it's not just 'did them marc change' - but 'did a specific field we have mapped changed'
Right now we have to check three tables to decide if a "record" has had changes.
The need for this comes from syncing with outside systems - 'biblios' is the endpoint for this - joining to the 'biblio_metadata' table makes any queries more expensive - with the timestamps in sync we can do a simple query to fetch the recently updated records.
Is there a use case to know that the record was updated, but not the title or author fields?
Not sure about the last sentence, you can update a record and not touch the title or author, right? In most cases it seems to me that you just need to check biblio_metadata or items. There is an index on the primary key for biblio_metadata. Why look at biblio or biblioitems? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Megan McShane <megan.mcshane@churchofjesuschrist.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |megan.mcshane@churchofjesus | |christ.org --- Comment #6 from Megan McShane <megan.mcshane@churchofjesuschrist.org> --- This would be really helpful for us to get in the next release. Its causing a lot of problems in updating records in our discovery layer and circulation putaway system. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #7 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- (In reply to Marcel de Rooy from comment #5)
Not sure about the last sentence, you can update a record and not touch the title or author, right? In most cases it seems to me that you just need to check biblio_metadata or items. There is an index on the primary key for biblio_metadata. Why look at biblio or biblioitems?
Because the API route is centered on the biblios table - so we need reliable filtering of all three tables via this endpoint -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@openfifth.c | |o.uk --- Comment #8 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- I've debated this.. I think the pragmatist in me says we should take this patch as is.. really, long term I'd just want to see biblioitems and biblios merged properly.. then metadata needs distinct handling down the line if we really do support 1-n biblio-metadata relations -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Martin Renvoize (ashimema) from comment #8)
I've debated this.. I think the pragmatist in me says we should take this patch as is.. really, long term I'd just want to see biblioitems and biblios merged properly.. then metadata needs distinct handling down the line if we really do support 1-n biblio-metadata relations
I remember us splitting out biblio_metadata because it sped up querying on bilbio/biblioitems without the MARC blob column being there. I just noticed the difference again in queries today. So even without 1:n it might always make sense to have special handling there. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #180624|0 |1 is obsolete| | --- Comment #10 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 182319 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182319&action=edit Bug 39558: Update biblio and biblioitems timestamps explicitly This patch simply adds 'timestamp = current_timestamp()' to the SQL statements run when editing a biblio. This ensures the tables timestamps are kept in sync To test: 1 - Choose a biblio in the database and make an update to a 650 field 2 - Check the timestamps in MySQL: SELECT biblio.timestamp, biblioitems.timestamp, biblio_metadata.timestamp FROM biblio JOIN biblioitems USING (biblionumber) JOIN biblio_metadata USING (biblionumber) WHERE biblionumber=9; 3 - Note the timestamps 4 - Edit the same 650 again 5 - Check the timestamps and note that the biblio_metadata is updated, but the others are not 6 - Apply patch, restart all 7 - Update the 650 again 8 - Check the timestamps, all are updated! 9 - Sign off Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|m.de.rooy@rijksmuseum.nl |martin.renvoize@openfifth.c | |o.uk Status|Signed Off |Passed QA --- Comment #11 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Th pragmatist in me thinks we should go with this, so I'm passing qa. If we do need timestamp data for each table in the future, then I think we should have that as a distinct field. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #12 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This still feels a bit controversial to me. I would love some more voices/votes here to get a better feeling for the change. Also a unit test would be nice! I am leaving this to the end of the week, please advertise in Mattermost at least. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_11_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |25.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 25.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00 |25.11.00,25.05.01 released in| | CC| |fridolin.somers@biblibre.co | |m Status|Pushed to main |Pushed to stable --- Comment #14 from Fridolin Somers <fridolin.somers@biblibre.com> --- I see in 25.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |release-notes-needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 --- Comment #15 from Fridolin Somers <fridolin.somers@biblibre.com> --- I'm thinking not backport to 24.11.x for stability (since it is LTS). Some scripts are based on timestamp, maybe some SQL reports. I bet this change will have impact (for good).$ Also please add release notes. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_25_11_candidate, | |release-notes-needed | CC| |david@davidnind.com Text to go in the| |This fixes the timestamp release notes| |recorded in timestamp | |fields when in a | |bibliographic record is | |updated. The timestamp | |fields in the biblio, | |biblioitems, and | |biblio_metadata tables are | |now all updated and kept in | |sync when a record is | |updated. The timestamps | |being out of sync could | |affect reporting and | |updating records in other | |systems, such as discovery | |layers. --- Comment #16 from David Nind <david@davidnind.com> --- I've attempted a release note. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Needs documenting -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39558 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED Status|Needs documenting |RESOLVED --- Comment #17 from David Nind <david@davidnind.com> --- Bug fix, no updates to the manual required. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org