Bug 36365 - compare_es_to_db.pl should offer a way to reconcile differences
Summary: compare_es_to_db.pl should offer a way to reconcile differences
Status: Pushed to stable
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P3 enhancement
Assignee: Tomás Cohen Arazi (tcohen)
QA Contact: Thomas Klausner
URL: https://bywatersolutions.com/crowdsou...
Keywords: rel_24_05_candidate, rel_24_11_candidate
Depends on: 22831
Blocks:
  Show dependency treegraph
 
Reported: 2024-03-19 21:05 UTC by Liz Rea
Modified: 2025-04-22 20:26 UTC (History)
15 users (show)

See Also:
Change sponsored?: Seeking sponsor
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
25.05.00,24.11.04
Circulation function:


Attachments
Bug 36365: Add a way for compare_es_to_db.pl to fix problems (3.88 KB, patch)
2025-02-14 22:47 UTC, Tomás Cohen Arazi (tcohen)
Details | Diff | Splinter Review
Bug 36365: Add a way for compare_es_to_db.pl to fix problems (3.94 KB, patch)
2025-02-18 13:33 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 36365: Add a way for compare_es_to_db.pl to fix problems (4.00 KB, patch)
2025-02-28 20:54 UTC, Lisette Scheer
Details | Diff | Splinter Review
Bug 36365: (follow-up) Indexing should be done async and in chunks (1.51 KB, patch)
2025-03-21 15:08 UTC, Tomás Cohen Arazi (tcohen)
Details | Diff | Splinter Review
Bug 36365: Add a way for compare_es_to_db.pl to fix problems (4.04 KB, patch)
2025-04-08 14:21 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 36365: (follow-up) Indexing should be done async and in chunks (1.56 KB, patch)
2025-04-08 14:21 UTC, Thomas Klausner
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Liz Rea 2024-03-19 21:05:02 UTC
Koha can currently tell how many biblios are in the database vs how many are in the elasticsearch indexes, but it cannot reconcile those differences without either bespoke work deleting individual ES documents or a full reindex. A full reindex might not solve the problem anyway, due to invalid data in MARC records.

It would be really neat if the existing tool to compare-es-to-db.pl had a mode that would at least try to reconcile any discrepancies. For example if it was discovered that there were more indexes than biblios, remove the erroneous ES documents, or if there were more biblios than es indexes, identify them and attempt to reindex them.

We should also provide an easy to understand error report if reconciliation was not possible, with suggested actions to take for the sysadmins or users.

This would obviously be an Elasticsearch only feature.
Comment 1 Valerie 2024-07-09 15:41:01 UTC
This would be valuable for libraries--not only be able to see what the problem is but also have a first-line strategy for resolving it.
Comment 2 David Cook 2024-07-09 23:21:22 UTC
I like this idea.

When we were first getting started with Elasticsearch with Koha, we were having lots of issues, so we had to write to write a script to help fix up the discrepancies.

But it's difficult to do well. 

I think we need to start storing a timestamp in the Elasticsearch document, so that it can be compared against the biblio_metadata and items tables. If the timestamp for the bib or the item is greater than that of the ES document, then you could schedule a re-index (or maybe do a synchronous index since it's a CLI tool) for that particular record. 

Fortunately, we've managed to iron out the difficulties we were having, so this has been less of a pressing issue for us, but I still think it's a good idea for sure. The ES indexing can go wrong so easily, and it's currently so hard to repair without a full re-index :/.
Comment 3 George Williams (NEKLS) 2024-08-14 16:39:33 UTC
Good idea
Comment 4 Tomás Cohen Arazi (tcohen) 2025-02-14 22:46:36 UTC
Hi, I'm adding my two cents here. My first idea was to reuse the sub in about.pl for building a button in the about page, to trigger the fixes as a background job.
The about page doesn't do the same thing as the `compare_es_to_db.pl` script, so it would take more time to write/add tests, etc.

I focused on the CLI script this time, adding a convenient `--fix` option switch.

Hope it helps.
Comment 5 Tomás Cohen Arazi (tcohen) 2025-02-14 22:47:09 UTC
Created attachment 178124 [details] [review]
Bug 36365: Add a way for compare_es_to_db.pl to fix problems

This patch adds a new option switch `--fix` (or `-f`) to make the script
try to fix the problems it found.

Bonus: `--help` option switch added.

To test:
1. Apply this patch
2. Run:
   $ ktd --shell
  k$ perl -MKoha::SearchEngine -MKoha::SearchEngine::Indexer -e 'my $i = Koha::SearchEngine::Indexer->new({index => $Koha::SearchEngine::BIBLIOS_INDEX}); $i->delete_index([1]);'
=> SUCCESS: Bibliographic record #deleted from ES index
3. Run:
  k$ koha-mysql kohadev
   > DELETE FROM biblio WHERE biblionumber=2 OR biblionumber=3;
=> SUCCESS: Bibliographic records 2 and 3 deleted directly from the
database, no reindex triggered by Koha.
4. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl
=> SUCCESS: The results from (2) and (3) are explained. [1]
5. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl --fix
=> SUCCESS: It says it is gonna fix things
6. Repeat 4
=> SUCCESS: The problems we generated are no longer reported! They've
been fixed!
7. Sign off :-D

[1] Note KTD ships some broken records on purpose so devs need to deal
with a not-perfect DB everyday, and catch misses in the code.
Comment 6 Andrew Fuerste-Henry 2025-02-18 13:33:38 UTC
Created attachment 178178 [details] [review]
Bug 36365: Add a way for compare_es_to_db.pl to fix problems

This patch adds a new option switch `--fix` (or `-f`) to make the script
try to fix the problems it found.

Bonus: `--help` option switch added.

To test:
1. Apply this patch
2. Run:
   $ ktd --shell
  k$ perl -MKoha::SearchEngine -MKoha::SearchEngine::Indexer -e 'my $i = Koha::SearchEngine::Indexer->new({index => $Koha::SearchEngine::BIBLIOS_INDEX}); $i->delete_index([1]);'
=> SUCCESS: Bibliographic record #deleted from ES index
3. Run:
  k$ koha-mysql kohadev
   > DELETE FROM biblio WHERE biblionumber=2 OR biblionumber=3;
=> SUCCESS: Bibliographic records 2 and 3 deleted directly from the
database, no reindex triggered by Koha.
4. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl
=> SUCCESS: The results from (2) and (3) are explained. [1]
5. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl --fix
=> SUCCESS: It says it is gonna fix things
6. Repeat 4
=> SUCCESS: The problems we generated are no longer reported! They've
been fixed!
7. Sign off :-D

[1] Note KTD ships some broken records on purpose so devs need to deal
with a not-perfect DB everyday, and catch misses in the code.

Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Comment 7 Lisette Scheer 2025-02-28 20:54:53 UTC
Created attachment 178874 [details] [review]
Bug 36365: Add a way for compare_es_to_db.pl to fix problems

Worked great and the --help is nice. 

This patch adds a new option switch `--fix` (or `-f`) to make the script
try to fix the problems it found.

Bonus: `--help` option switch added.

To test:
1. Apply this patch
2. Run:
   $ ktd --shell
  k$ perl -MKoha::SearchEngine -MKoha::SearchEngine::Indexer -e 'my $i = Koha::SearchEngine::Indexer->new({index => $Koha::SearchEngine::BIBLIOS_INDEX}); $i->delete_index([1]);'
=> SUCCESS: Bibliographic record #deleted from ES index
3. Run:
  k$ koha-mysql kohadev
   > DELETE FROM biblio WHERE biblionumber=2 OR biblionumber=3;
=> SUCCESS: Bibliographic records 2 and 3 deleted directly from the
database, no reindex triggered by Koha.
4. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl
=> SUCCESS: The results from (2) and (3) are explained. [1]
5. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl --fix
=> SUCCESS: It says it is gonna fix things
6. Repeat 4
=> SUCCESS: The problems we generated are no longer reported! They've
been fixed!
7. Sign off :-D

[1] Note KTD ships some broken records on purpose so devs need to deal
with a not-perfect DB everyday, and catch misses in the code.

Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 8 Katrin Fischer 2025-03-07 16:39:20 UTC
All for the feature, but can we please get another set of QA eyes on this one?

Thanks!
Comment 9 Nick Clemens (kidclamp) 2025-03-07 19:41:20 UTC
This is indexing each record individually - we should batch these up and use a method that implements background jobs - I fear this might overwhelm a server
Comment 10 Tomás Cohen Arazi (tcohen) 2025-03-07 19:42:26 UTC
(In reply to Nick Clemens (kidclamp) from comment #9)
> This is indexing each record individually - we should batch these up and use
> a method that implements background jobs - I fear this might overwhelm a
> server

Thanks, I will revisit this next week. My ultimate goal is to have something we can reuse from the UI later.
Comment 11 Tomás Cohen Arazi (tcohen) 2025-03-21 15:08:54 UTC
Created attachment 179623 [details] [review]
Bug 36365: (follow-up) Indexing should be done async and in chunks

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 12 Martin Renvoize (ashimema) 2025-04-02 14:22:51 UTC
I'm happy to give this a second look, but to me it's looking great with the follow-up, do you agree Nick?
Comment 13 Thomas Klausner 2025-04-07 17:20:04 UTC
I think the patch works, but the ES records are not updated immediately. They end up in the `background_jobs` table. We had quite some problems with the `background_jobs`, and on ktd the missing record (id 1) is not added to ES. This might well be because I'm doing something wrong.

In the DB the jobs have status 'finished', but ES is still missing:

curl es:9200/koha_kohadev_biblios/data/1?pretty=true
{
  "_index" : "koha_kohadev_biblios",
  "_type" : "data",
  "_id" : "1",
  "found" : false
}

Not sure what to do next...
Comment 14 Thomas Klausner 2025-04-08 14:20:05 UTC
I just did a manual reindex of the missing biblio (as I somehow don't get the background_job to actually index it, though according to the DB it did).

So the patches work as advertised!
Comment 15 Thomas Klausner 2025-04-08 14:21:10 UTC
Created attachment 180724 [details] [review]
Bug 36365: Add a way for compare_es_to_db.pl to fix problems

This patch adds a new option switch `--fix` (or `-f`) to make the script
try to fix the problems it found.

Bonus: `--help` option switch added.

To test:
1. Apply this patch
2. Run:
   $ ktd --shell
  k$ perl -MKoha::SearchEngine -MKoha::SearchEngine::Indexer -e 'my $i = Koha::SearchEngine::Indexer->new({index => $Koha::SearchEngine::BIBLIOS_INDEX}); $i->delete_index([1]);'
=> SUCCESS: Bibliographic record #deleted from ES index
3. Run:
  k$ koha-mysql kohadev
   > DELETE FROM biblio WHERE biblionumber=2 OR biblionumber=3;
=> SUCCESS: Bibliographic records 2 and 3 deleted directly from the
database, no reindex triggered by Koha.
4. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl
=> SUCCESS: The results from (2) and (3) are explained. [1]
5. Run:
  k$ perl misc/maintenance/compare_es_to_db.pl --fix
=> SUCCESS: It says it is gonna fix things
6. Repeat 4
=> SUCCESS: The problems we generated are no longer reported! They've
been fixed!
7. Sign off :-D

[1] Note KTD ships some broken records on purpose so devs need to deal
with a not-perfect DB everyday, and catch misses in the code.

Signed-off-by: Andrew Fuerste Henry <andrew@bywatersolutions.com>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Signed-off-by: Thomas Klausner <domm@plix.at>
Comment 16 Thomas Klausner 2025-04-08 14:21:13 UTC
Created attachment 180725 [details] [review]
Bug 36365: (follow-up) Indexing should be done async and in chunks

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Thomas Klausner <domm@plix.at>
Comment 17 Katrin Fischer 2025-04-14 06:52:23 UTC
Pushed for 25.05!

Well done everyone, thank you!
Comment 18 Paul Derscheid 2025-04-22 20:26:43 UTC
Nice work everyone!

Pushed to 24.11.x for 24.11.04