Bug 38617

Summary: Fix warning about auto increment and biblioitems
Product: Koha Reporter: Magnus Enger <magnus>
Component: AboutAssignee: Magnus Enger <magnus>
Status: Pushed to stable --- QA Contact: Katrin Fischer <katrin.fischer>
Severity: normal    
Priority: P5 - low CC: david, lisette
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35033
GIT URL: Change sponsored?: ---
Patch complexity: Trivial patch Documentation contact:
Documentation submission: Text to go in the release notes:
This fixes the table name in the warning about auto increment and biblioitems on the About Koha > System information page. If the system identifies auto increment issues, the message is now "The following IDs exist in both tables biblioitems and deletedbiblioitems", instead of "...tables biblio and deletedbiblioitems".
Version(s) released in:
25.05.00,24.11.03
Circulation function:
Attachments: Bug 38617: Fix warning about auto increment and biblioitems
Bug 38617: Fix warning about auto increment and biblioitems

Description Magnus Enger 2024-12-04 10:14:38 UTC
Bug 35033 added a check for auto increment problems related to biblioitems. The code looks like this: 

     my $biblioitems = $dbh->selectall_arrayref(
        q|select bi.biblioitemnumber from biblioitems bi join deletedbiblioitems dbi on bi.biblionumber=dbi.biblionumber|,
        { Slice => {} }
    );

It compares biblioitems and deletedbiblioitems, which makes sense. But the template that displays the results look like this: 

    [% IF ai_biblioitems %]
        <h4>Bibliographic records</h4>
        <p>The following IDs exist in both tables [% "biblio" | $HtmlTags tag="strong" %] and [% "deletedbiblioitems" | $HtmlTags tag="strong" %]:</p>
        <p>
            [% FOR b IN ai_biblioitems %]
                [% b.biblioitemnumber | html %]
                [% UNLESS loop.last %], [% END %]
            [% END %]
        </p>
    [% END %]

The problem is this: 

  The following IDs exist in both tables [% "biblio" | $HtmlTags tag="strong" %] and [% "deletedbiblioitems" | $HtmlTags tag="strong" %]

It says "biblio and deletedbiblioitems", when it should say "biblioitems and deletedbiblioitems".
Comment 1 Magnus Enger 2024-12-04 10:30:45 UTC Comment hidden (obsolete)
Comment 2 David Nind 2024-12-26 19:18:04 UTC
Created attachment 175949 [details] [review]
Bug 38617: Fix warning about auto increment and biblioitems

To reproduce:
- Delete a bibliographic, noting the biblionumber (let's call it X)
- Run this in the database:
    UPDATE deletedbiblioitems SET biblionumber = X;
- Go to About Koha > System information
- Notice the warning compares two tables that are not equivalent:
  "The following IDs exist in both tables biblio and deletedbiblioitems"

To test:
- Apply the patch
- Reload the System information
- The warning should now read:
  "The following IDs exist in both tables biblioitems and deletedbiblioitems"

Signed-off-by: David Nind <david@davidnind.com>
Comment 3 David Nind 2024-12-26 19:22:28 UTC
Hi Magnus.

The steps to reproduce the error didn't work for me.

Using KTD, I deleted the items and then record for Programming Perl (biblionumber = 262).

The SQL didn't change anything for me - before the SQL command, the biblionumber was already 262.

The table details before running the SQL command:

select * from deletedbiblioitems;
+------------------+--------------+--------+--------+----------+---------------+------+------+-----------------+---------------+------------+------------+-----------------+----------------+------------------+------------------+-----------------------+---------------------+--------+----------------+-------+--------+--------------------------+--------------+------+-----------+----------+---------+-----------+---------+----------------+-------------+
| biblioitemnumber | biblionumber | volume | number | itemtype | isbn          | issn | ean  | publicationyear | publishercode | volumedate | volumedesc | collectiontitle | collectionissn | collectionvolume | editionstatement | editionresponsibility | timestamp           | illus  | pages          | notes | size   | place                    | lccn         | url  | cn_source | cn_class | cn_item | cn_suffix | cn_sort | agerestriction | totalissues |
+------------------+--------------+--------+--------+----------+---------------+------+------+-----------------+---------------+------------+------------+-----------------+----------------+------------------+------------------+-----------------------+---------------------+--------+----------------+-------+--------+--------------------------+--------------+------+-----------+----------+---------+-----------+---------+----------------+-------------+
|              262 |          262 | NULL   | NULL   | BK       | 9780596004927 | NULL | NULL | NULL            | O'Reilly,     | NULL       | NULL       | NULL            | NULL           | NULL             | 4th ed.          | NULL                  | 2024-12-26 19:02:47 | ill. ; | xli, 1130 p. : | NULL  | 24 cm. | Beijing ; | Sebastopol : |   2012392268 | NULL | ddc       | NULL     | NULL    | NULL      |         | NULL           |        NULL |
+------------------+--------------+--------+--------+----------+---------------+------+------+-----------------+---------------+------------+------------+-----------------+----------------+------------------+------------------+-----------------------+---------------------+--------+----------------+-------+--------+--------------------------+--------------+------+-----------+----------+---------+-----------+---------+----------------+-------------+
1 row in set (0.000 sec)

I could recreate the issue by using UPDATE deletedbiblioitems SET biblionumber = 100;

David
Comment 4 Magnus Enger 2024-12-27 07:19:16 UTC
Hm, the point was to get to a situation where the same id is in both biblioitems and deletedbiblioitems. So I guess it should be 

UPDATE deletedbiblioitems SET biblionumber = Y;

where Y is the biblionumber of a record that has *not* been deleted yet.
Comment 5 David Nind 2024-12-27 07:33:40 UTC
Thanks Magnus!
Comment 6 Katrin Fischer 2024-12-27 16:00:24 UTC
QA by RM
Comment 7 Katrin Fischer 2024-12-27 16:36:36 UTC
Pushed for 25.05!

Well done everyone, thank you!
Comment 8 Paul Derscheid 2025-03-19 23:01:21 UTC
Nice work everyone!

Pushed to 24.11.x for 24.11.03