From 1136ecb229b4c3f883dd2f3a708017c8c7d96121 Mon Sep 17 00:00:00 2001 From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> Date: Wed, 12 Jul 2017 15:52:19 -0300 Subject: [PATCH] Bug 18931: Add a "data corrupted" section on the about page - MySQL AI See the following wiki page for more information https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Test plan: Create (at least) a patron, a checkout, a biblio and a hold Then fill the old_* or deleted* tables with: INSERT INTO deletedborrowers SELECT * from borrowers WHERE borrowernumber=XXX; INSERT INTO deletedbiblio SELECT * from biblio WHERE biblionumber=XXX; INSERT INTO old_issues SELECT * from issues WHERE issue_id=XXX; INSERT INTO old_reserves SELECT * from reserves WHERE reserve_id=XXX; Go to the about page, 'System information' tab. You should see a new "Data problems" section with the list of the ids that are wrong Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> --- about.pl | 30 +++++++++++++++++++-- koha-tmpl/intranet-tmpl/prog/en/modules/about.tt | 34 +++++++++++++++++++++++- 2 files changed, 61 insertions(+), 3 deletions(-) diff --git a/about.pl b/about.pl index ef91b24..fed0092 100755 --- a/about.pl +++ b/about.pl @@ -284,10 +284,36 @@ if ( C4::Context->preference('WebBasedSelfCheck') AutoSelfCheckPatronDoesNotHaveSelfCheckPerm => not ( $has_self_checkout_perm ), AutoSelfCheckPatronHasTooManyPerm => $has_other_permissions, ); - - } +{ + my $dbh = C4::Context->dbh; + my $patrons = $dbh->selectall_arrayref( + q|select b.borrowernumber from borrowers b join deletedborrowers db on b.borrowernumber=db.borrowernumber|, + { Slice => {} } + ); + my $biblios = $dbh->selectall_arrayref( + q|select b.biblionumber from biblio b join deletedbiblio db on b.biblionumber=db.biblionumber|, + { Slice => {} } + ); + my $checkouts = $dbh->selectall_arrayref( + q|select i.issue_id from issues i join old_issues oi on i.issue_id=oi.issue_id|, + { Slice => {} } + ); + my $holds = $dbh->selectall_arrayref( + q|select r.reserve_id from reserves r join old_reserves o on r.reserve_id=o.reserve_id|, + { Slice => {} } + ); + if ( @$patrons or @$biblios or @$checkouts or @$holds ) { + $template->param( + has_ai_issues => 1, + ai_patrons => $patrons, + ai_biblios => $biblios, + ai_checkouts => $checkouts, + ai_holds => $holds, + ); + } +} my %versions = C4::Context::get_versions(); $template->param( diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt index eaabe03..6d10c48 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt @@ -130,13 +130,45 @@ </div> <div id="sysinfo"> - [% IF warnPrefBiblioAddsAuthorities || warnPrefEasyAnalyticalRecords || warnPrefAnonymousPatron || warnPrefAnonymousPatron_PatronDoesNotExist || warnNoActiveCurrency || QueryParserError || warnIsRootUser || xml_config_warnings.size || AutoSelfCheckPatronDoesNotHaveSelfCheckPerm || AutoSelfCheckPatronHasTooManyPerm || warnStatisticsFieldsError || warnNoTemplateCaching %] + [% IF warnPrefBiblioAddsAuthorities || warnPrefEasyAnalyticalRecords || warnPrefAnonymousPatron || warnPrefAnonymousPatron_PatronDoesNotExist || warnNoActiveCurrency || QueryParserError || warnIsRootUser || xml_config_warnings.size || AutoSelfCheckPatronDoesNotHaveSelfCheckPerm || AutoSelfCheckPatronHasTooManyPerm || warnStatisticsFieldsError || warnNoTemplateCaching || has_ai_issues %] [% IF (warnIsRootUser) %] <h2>Warning regarding current user</h2> <p>You are logged in as the database administrative user. This is not recommended because some parts of Koha will not function as expected when using this account.</p> <p>Please log in instead with a regular staff account. To create a staff account, create a library, a patron category 'Staff' and add a new patron. Then give this patron permissions from 'More' in the toolbar.</p> [% END %] + [% IF has_ai_issues %] + <h2>Data problems</h2> + <p> + The following tables have problems with their auto_increment values which may lead to data lost. You should not ignore this warning. + The problem is that innodb does not keep auto_increment across SQL server restarts (it is only set in memory). + So on server startup the auto_increment values are set to max(table.id)+1. + </p> + <p> + See the <a href="https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix">related wiki page</a> to know how to avoid this problem. + </p> + [% IF ai_patrons %] + <h3>Patrons</h3> + The following ids exist in both the borrowers and deletedborrowers table: + [% FOR p IN ai_patrons %][% p.borrowernumber %][% UNLESS loop.last %], [% END %][% END %] + [% END %] + [% IF ai_biblios %] + <h3>Biblios</h3> + The following ids exist in both the biblio and deletedbiblio table: + [% FOR b IN ai_biblios %][% b.biblionumber %][% UNLESS loop.last %], [% END %][% END %] + [% END %] + [% IF ai_checkouts %] + <h3>Checkouts</h3> + The following ids exist in both the issues and old_issues table: + [% FOR c IN ai_checkouts %][% c.issue_id %][% UNLESS loop.last %], [% END %][% END %] + [% END %] + [% IF ai_holds %] + <h3>Holds</h3> + The following ids exist in both the holds and old_reserves table: + [% FOR h IN ai_holds %][% h.issue_id %][% UNLESS loop.last %], [% END %][% END %] + [% END %] + [% END %] + [% IF warnPrefBiblioAddsAuthorities || warnPrefEasyAnalyticalRecords || warnPrefAnonymousPatron || warnPrefAnonymousPatron_PatronDoesNotExist || warnNoActiveCurrency || QueryParserError || AutoSelfCheckPatronDoesNotHaveSelfCheckPerm || AutoSelfCheckPatronHasTooManyPerm || warnStatisticsFieldsError || warnNoTemplateCaching %] <h2>Warnings regarding the system configuration</h2> <table> -- 2.7.4