Bug 39558

Summary: Timestamps on biblio biblioitems and biblio_metadata are not in sync
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: MARC Bibliographic data supportAssignee: Nick Clemens (kidclamp) <nick>
Status: Pushed to stable --- QA Contact: Martin Renvoize (ashimema) <martin.renvoize>
Severity: normal    
Priority: P5 - low CC: david, dcook, fridolin.somers, katrin.fischer, m.de.rooy, martin.renvoize, megan.mcshane, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39397
GIT URL: Change sponsored?: ---
Patch complexity: Trivial patch Documentation contact:
Documentation submission: Text to go in the release notes:
This fixes the timestamp 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.
Version(s) released in:
25.11.00,25.05.01
Circulation function:
Attachments: Bug 39558: Update biblio and biblioitems timestamps explicitly
Bug 39558: Update biblio and biblioitems timestamps explicitly
Bug 39558: Update biblio and biblioitems timestamps explicitly

Description Nick Clemens (kidclamp) 2025-04-04 08:06:38 UTC
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.
Comment 1 Nick Clemens (kidclamp) 2025-04-04 08:22:09 UTC
Created attachment 180616 [details] [review]
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
Comment 2 David Nind 2025-04-04 10:27:49 UTC
Created attachment 180624 [details] [review]
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>
Comment 3 Marcel de Rooy 2025-04-11 07:42:27 UTC
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.
Comment 4 Nick Clemens (kidclamp) 2025-04-11 12:57:16 UTC
(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?
Comment 5 Marcel de Rooy 2025-04-22 11:49:14 UTC
(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?
Comment 6 Megan McShane 2025-04-22 20:47:11 UTC
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.
Comment 7 Nick Clemens (kidclamp) 2025-05-02 18:14:02 UTC
(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
Comment 8 Martin Renvoize (ashimema) 2025-05-06 16:29:57 UTC
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
Comment 9 Katrin Fischer 2025-05-06 16:31:42 UTC
(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.
Comment 10 Martin Renvoize (ashimema) 2025-05-13 06:13:10 UTC
Created attachment 182319 [details] [review]
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>
Comment 11 Martin Renvoize (ashimema) 2025-05-13 06:13:56 UTC
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.
Comment 12 Katrin Fischer 2025-05-13 16:31:12 UTC
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.
Comment 13 Lucas Gass (lukeg) 2025-06-04 22:08:33 UTC
Nice work everyone!

Pushed to main for 25.11
Comment 14 Fridolin Somers 2025-06-19 07:51:54 UTC
I see in 25.05.x
Comment 15 Fridolin Somers 2025-06-19 07:55:12 UTC
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.
Comment 16 David Nind 2025-06-26 22:49:50 UTC
I've attempted a release note.