Bug 24272

Summary: Add a command line script to compare the syspref cache to the database
Product: Koha Reporter: Nick Clemens <nick>
Component: Command-line UtilitiesAssignee: Nick Clemens <nick>
Status: Pushed to oldoldstable --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: andrew, caroline.cyr-la-rose, david.roberts, david, dcook, fridolin.somers, jonathan.druart, martin.renvoize, robin, victor
Version: master   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Trivial patch
Text to go in the release notes:
This script checks the value of the system preferences in the database against those in the cache. Generally differences will only exist if changes have been made directly to the DB or the cache has become corrupted.
Version(s) released in:
21.05.00,20.11.03,20.05.09,19.11.15
Attachments: Bug 24272: add check_sysprefs_cache.pl
Bug 24272: add check_sysprefs_cache.pl
Bug 24272: add check_sysprefs_cache.pl
Bug 24272: add check_sysprefs_cache.pl
Bug 24272: (follow-up) Test defined not true
Bug 24272: Use Pod2Usage and surround values with ''
Bug 24272: Correctly encode output to prevent 'Wide character in say'

Description Nick Clemens 2019-12-19 03:20:05 UTC

    
Comment 1 Nick Clemens 2019-12-19 03:20:58 UTC
Created attachment 96467 [details] [review]
Bug 24272: add check_sysprefs_cache.pl
Comment 2 David Roberts 2020-04-19 18:06:33 UTC
If you can tell me what you needs to be tested, I'll be happy to try to sign this one off for you. :-)
Comment 3 David Nind 2020-11-05 05:06:06 UTC
I tried to test in koha-testing-docker this by:

1. Applying the patch.
2. Changed the ownership to kohadev-koha (chown kohadev-koha:kohadev-koha check_syspref_cache.pl) and permissions (chmod 755 check_syspref_cache.pl).
3. Running the script:
   perl misc/maintenance/check_syspref_cache.pl

I received this message:

Global symbol "$syspref_cache" requires explicit package name (did you forget to declare "my $syspref_cache"?) at misc/maintenance/check_syspref_cache.pl line 40.
Execution of misc/maintenance/check_syspref_cache.pl aborted due to compilation errors.

I don't know how system preferences are cached and to check for differences between those and what is in the database.
Comment 4 Nick Clemens 2020-11-05 19:12:51 UTC
Created attachment 113135 [details] [review]
Bug 24272: add check_sysprefs_cache.pl

This script ensure that no sysprefs have been changed directly in the database and/or
that the cache has not become corrupted. We have occasionally seen this happen on production sites

To test:
1 - In the staff interface go to Administration
2 - Search for system preference 'IntranetUserJS'
3 - Add content to the syspref:
    console.log('Hi!');
4 - On the command line launch mysql
    sudo koha-mysql kohadev
5 - Alter the syspref directly
    UPDATE systempreferences SET value = "console.log('Bye!');" WHERE variable = 'IntranetUserJS';
6 - run the script
    perl misc/maintenance/check_syspref_cache.pl
7 - You are warned about the altered system preference
Comment 5 David Nind 2020-11-05 20:24:30 UTC
Created attachment 113193 [details] [review]
Bug 24272: add check_sysprefs_cache.pl

This script ensure that no sysprefs have been changed directly in the database and/or
that the cache has not become corrupted. We have occasionally seen this happen on production sites

To test:
1 - In the staff interface go to Administration
2 - Search for system preference 'IntranetUserJS'
3 - Add content to the syspref:
    console.log('Hi!');
4 - On the command line launch mysql
    sudo koha-mysql kohadev
5 - Alter the syspref directly
    UPDATE systempreferences SET value = "console.log('Bye!');" WHERE variable = 'IntranetUserJS';
6 - run the script
    perl misc/maintenance/check_syspref_cache.pl
7 - You are warned about the altered system preference

Signed-off-by: David Nind <david@davidnind.com>
Comment 6 David Nind 2020-11-05 20:24:54 UTC
Thanks Nick!
Comment 7 Martin Renvoize 2021-01-11 13:49:17 UTC
Created attachment 115029 [details] [review]
Bug 24272: add check_sysprefs_cache.pl

This script ensure that no sysprefs have been changed directly in the database and/or
that the cache has not become corrupted. We have occasionally seen this happen on production sites

To test:
1 - In the staff interface go to Administration
2 - Search for system preference 'IntranetUserJS'
3 - Add content to the syspref:
    console.log('Hi!');
4 - On the command line launch mysql
    sudo koha-mysql kohadev
5 - Alter the syspref directly
    UPDATE systempreferences SET value = "console.log('Bye!');" WHERE variable = 'IntranetUserJS';
6 - run the script
    perl misc/maintenance/check_syspref_cache.pl
7 - You are warned about the altered system preference

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize 2021-01-11 13:49:58 UTC
I can see this one being pretty helpful.. not sure where we should document it to make it clear it exists.

Passing QA
Comment 9 Jonathan Druart 2021-01-12 15:17:20 UTC
Cannot push, release notes needed.
Comment 10 Jonathan Druart 2021-01-12 15:20:06 UTC
+    next unless $cached_var;

Are you sure? We need to tell if 0 or '' are different values from the DB.
Comment 11 Nick Clemens 2021-01-15 11:01:06 UTC
Created attachment 115192 [details] [review]
Bug 24272: (follow-up) Test defined not true
Comment 12 Jonathan Druart 2021-01-20 09:03:13 UTC
Created attachment 115412 [details] [review]
Bug 24272: Use Pod2Usage and surround values with ''
Comment 13 Jonathan Druart 2021-01-20 09:03:16 UTC
Created attachment 115413 [details] [review]
Bug 24272: Correctly encode output to prevent 'Wide character in say'
Comment 14 Jonathan Druart 2021-01-20 12:51:01 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 15 Fridolin Somers 2021-01-25 15:13:51 UTC
Since it is a standalone script there is no risk in backporting.

Pushed to 20.11.x for 20.11.03
Comment 16 Andrew Fuerste-Henry 2021-02-01 20:55:22 UTC
Backporting standalone script to 20.05.x for 20.05.09
Comment 17 Victor Grousset/tuxayo 2021-02-01 23:45:19 UTC
Backported: Pushed to 19.11.x branch for 19.11.15