Bug 22831 - Elasticsearch - add a maintenance script for checking DB vs index counts
Summary: Elasticsearch - add a maintenance script for checking DB vs index counts
Status: Pushed to stable
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching - Elasticsearch (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement with 1 vote (vote)
Assignee: Nick Clemens
QA Contact: Jonathan Druart
URL:
Keywords: Manual, release-notes-needed
Depends on:
Blocks:
 
Reported: 2019-05-02 12:24 UTC by Nick Clemens
Modified: 2020-02-13 21:14 UTC (History)
12 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.03


Attachments
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.37 KB, patch)
2019-05-02 12:31 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.25 KB, patch)
2019-11-06 03:10 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.49 KB, patch)
2019-11-06 13:04 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (2.14 KB, patch)
2019-11-06 14:37 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (2.14 KB, patch)
2019-11-06 14:38 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.46 KB, patch)
2019-11-07 13:21 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (3.25 KB, patch)
2019-11-07 13:21 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.50 KB, patch)
2019-11-07 13:28 UTC, Ere Maijala (away 16Mar-10May)
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (3.30 KB, patch)
2019-11-07 13:28 UTC, Ere Maijala (away 16Mar-10May)
Details | Diff | Splinter Review
Bug 22831: (follow-up) Sort results, group by db (2.04 KB, patch)
2019-11-07 18:49 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.56 KB, patch)
2020-01-03 21:21 UTC, Bouzid Fergani
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (3.36 KB, patch)
2020-01-03 21:21 UTC, Bouzid Fergani
Details | Diff | Splinter Review
Bug 22831: (follow-up) Sort results, group by db (2.09 KB, patch)
2020-01-03 21:21 UTC, Bouzid Fergani
Details | Diff | Splinter Review
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts (4.64 KB, patch)
2020-01-14 10:26 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter (3.43 KB, patch)
2020-01-14 10:26 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 22831: (follow-up) Sort results, group by db (2.17 KB, patch)
2020-01-14 10:26 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 22831: (RM follow-up) Code golf (2.42 KB, patch)
2020-01-23 10:24 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2019-05-02 12:24:45 UTC

    
Comment 1 Nick Clemens 2019-05-02 12:31:49 UTC
Created attachment 89233 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elastic_search.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems
Comment 2 Nick Clemens 2019-11-06 03:10:26 UTC
Created attachment 95077 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elastic_search.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems
Comment 3 Ere Maijala (away 16Mar-10May) 2019-11-06 08:02:05 UTC
I think this needs to display some sort of progress message. Otherwise it looks like it just hangs with high CPU usage. And please fix "elastic search" to "Elasticsearch" in the comment while at it. :)

It would also be useful to check from which array a record is missing and tell it to the user. Since we already have the information in the arrays, let's help the user out.
Comment 4 Michal Denar 2019-11-06 08:16:33 UTC
Hi Nick,
I troed to test it with Kohadevbox, but get this error on step 3:
Can't locate Koha/Items.pm in @INC (you may need to install the Koha::Items module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.24.1 /usr/local/share/perl/5.24.1 /usr/lib/x86_64-linux-gnu/perl5/5.24 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base) at compare_es_to_db.pl line 33.
BEGIN failed--compilation aborted at compare_es_to_db.pl line 33.

I'm really not sure about this issue. It can be based at Kohadevbox instalation.
Comment 5 Ere Maijala (away 16Mar-10May) 2019-11-06 08:59:03 UTC
That's a weird one. The script worked for me in my dev environment.
Comment 6 Ere Maijala (away 16Mar-10May) 2019-11-06 09:14:46 UTC
Nick, one more thing: you can make the ES cursor a zillion times faster by not retrieving any of the stored fields:

    my $scroll = $es->scroll_helper(
        index => $searcher->get_elasticsearch_params->{index_name},
        size => 5000,
        body => {
            query => {
                match_all => {}
            },
            stored_fields => []
        },
        scroll_in_qs => 1,
    );

I'd also run the id comparison regardless of whether record counts match since there's could be a case where they do but the records still differ.
Comment 7 Nick Clemens 2019-11-06 13:04:30 UTC
Created attachment 95105 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elasticsearch.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems
Comment 8 Ere Maijala (away 16Mar-10May) 2019-11-06 13:46:29 UTC
Nick, did you consider my suggestion about more informative result display:

It would also be useful to check from which array a record is missing and tell it to the user. Since we already have the information in the arrays, let's help the user out.

Also, maybe a running counter when fetching the records?
Comment 9 Nick Clemens 2019-11-06 14:37:19 UTC
Created attachment 95109 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Comment 10 Nick Clemens 2019-11-06 14:38:12 UTC
Created attachment 95110 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Comment 11 Ere Maijala (away 16Mar-10May) 2019-11-07 08:44:27 UTC
The url is a nice touch! A few issues remain, though: 

1.) I'd still fetch the base urls outside the loop. Would improve readability and avoid repeated pref calls.

2.) For authorities, the OPAC URLs are now invalid. They should be something like [...]/cgi-bin/koha/opac-authoritiesdetail.pl?authid=123.

3.) The patch for PerlDependencies.pl is borked.
Comment 12 Fridolin SOMERS 2019-11-07 12:34:03 UTC
Very nice script, thanks a lot ;)
Comment 13 Nick Clemens 2019-11-07 13:21:06 UTC
Created attachment 95154 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elasticsearch.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems
Comment 14 Nick Clemens 2019-11-07 13:21:09 UTC
Created attachment 95155 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Comment 15 Ere Maijala (away 16Mar-10May) 2019-11-07 13:28:54 UTC
Created attachment 95156 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elasticsearch.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Comment 16 Ere Maijala (away 16Mar-10May) 2019-11-07 13:28:57 UTC
Created attachment 95157 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Comment 17 Jonathan Druart 2019-11-07 17:52:36 UTC
record ids should be sorted numerically.
Comment 18 Nick Clemens 2019-11-07 18:49:31 UTC
Created attachment 95176 [details] [review]
Bug 22831: (follow-up) Sort results, group by db
Comment 19 Bouzid Fergani 2020-01-03 21:21:26 UTC
Created attachment 96804 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elasticsearch.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>
Comment 20 Bouzid Fergani 2020-01-03 21:21:29 UTC
Created attachment 96805 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>
Comment 21 Bouzid Fergani 2020-01-03 21:21:32 UTC
Created attachment 96806 [details] [review]
Bug 22831: (follow-up) Sort results, group by db

Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>
Comment 22 Jonathan Druart 2020-01-14 10:26:39 UTC
Created attachment 97339 [details] [review]
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts

This script uses Array::Utils and adds a dependency

To test:
 1 - Have Koha with ES running
 2 - Delete some records from ES
     curl -X DELETE "es:9200/koha_kohadev_biblios/data/5"
     curl -X DELETE "es:9200/koha_kohadev_authorities/data/5"
 3 - perl misc/maintenance/compare_es_to_db.pl
 4 - Note you are notified of problems in both indexes
 5 - perl misc/search_tools/rebuild_elastic_search.pl  -a
 6 - perl misc/maintenance/compare_es_to_db.pl
 7 - Note you are only notified about problems in biblios (assuming you don't have other issues)
 8 - perl misc/search_tools/rebuild_elasticsearch.pl  -b
 9 - perl misc/maintenance/compare_es_to_db.pl
10 - Both counts match, no problems

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 23 Jonathan Druart 2020-01-14 10:26:44 UTC
Created attachment 97340 [details] [review]
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter

Signed-off-by: Ere Maijala <ere.maijala@helsinki.fi>
Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 24 Jonathan Druart 2020-01-14 10:26:47 UTC
Created attachment 97341 [details] [review]
Bug 22831: (follow-up) Sort results, group by db

Signed-off-by: Bouzid Fergani <bouzid.fergani@inlibro.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 25 Jonathan Druart 2020-01-14 10:27:23 UTC
I would have preferred to not add the new deps, but not blocker.

Thanks for the follow-up Nick!
Comment 26 Martin Renvoize 2020-01-20 16:24:56 UTC
Haha, I agree.. I would have preferred not to add a new dependancy... especially as it doesn't appear to be packaged for Jessie (having said that.. we are dropping supoprt for Jessie with 20.05 and this is an enhancement so that shouldn't be a problem).

However.. I am wondering a little about the code having had a little read.

You seem to do an array_diff and then two more iterations to catch where the diffs came from.. couldn't you have just done two array_minus calls (from the same module) to directly get the two different types of problem and saved yourself a loop and a few lines of code?
Comment 27 Martin Renvoize 2020-01-23 10:24:06 UTC
Created attachment 97783 [details] [review]
Bug 22831: (RM follow-up) Code golf

Rather than performing a symmetric diff and then splitting the results
in a further loop this patch changes to logic to use two asymetric diffs
to get the results more directly.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 28 Martin Renvoize 2020-01-23 10:35:46 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 29 Joy Nelson 2020-02-07 23:41:50 UTC
Backported to 19.11.03
Comment 30 Lucas Gass 2020-02-13 21:14:05 UTC
choosing not backport enhancement to 19.05.x