Bug 22831

Summary: Add a maintenance script for checking DB vs index counts
Product: Koha Reporter: Nick Clemens <nick>
Component: Searching - ElasticsearchAssignee: Nick Clemens <nick>
Status: CLOSED FIXED QA Contact: Jonathan Druart <jonathan.druart>
Severity: enhancement    
Priority: P5 - low CC: 1joynelson, alex.arnaud, black23, dcook, ere.maijala, fridolin.somers, glasklas, jonathan.druart, josef.moravec, lucas, martin.renvoize, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17382
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.03
Bug Depends on:    
Bug Blocks: 26382    
Attachments: Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: (follow-up) Sort results, group by db
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: (follow-up) Sort results, group by db
Bug 22831: Elasticsearch - add a maintenance script for checking DB vs index counts
Bug 22831: (follow-up) Determine where the record is missing and provide link, show counter
Bug 22831: (follow-up) Sort results, group by db
Bug 22831: (RM follow-up) Code golf

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 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 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 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 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 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 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 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
Comment 31 David Cook 2020-05-17 23:22:22 UTC
I wrote something like this for Zebra a long time ago, and while it is useful, it would be great to check if the contents of both the DB and the Index were the same. (For instance, the counts might be the same as you may not have added/deleted in a while, but the contents may still be different, if your updates aren't working.)

What do people think about adding a checksum in a MARC field and indexing that? (Alternatively, I suppose we could just use the 005, although I don't know how reliably that is updated.)
Comment 32 Jonathan Druart 2020-11-10 14:10:29 UTC
*** Bug 17382 has been marked as a duplicate of this bug. ***