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>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

diff --git a/about.pl b/about.pl
index ef91b24..fed0092 100755 (executable)
--- 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 (file)
--- 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>