Bug 36424 - Database update 22.06.00.078 breaks update process (II)
Summary: Database update 22.06.00.078 breaks update process (II)
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: Main
Hardware: All All
: P2 critical (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords: RM_priority
Depends on: 34520
Blocks:
  Show dependency treegraph
 
Reported: 2024-03-26 00:40 UTC by Pablo AB
Modified: 2024-04-15 23:16 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Pablo AB 2024-03-26 00:40:04 UTC
Following Joubu advice on bug 34520#c12:

After that bug has being fixed, I tried (today 2024-03-25) once again:

Unpacking koha-common (23.11.03-1) over (22.05.19-1) ...
Setting up koha-common (23.11.03-1) ...   
[...]
Upgrading database schema for myinstance 
[...]
Upgrade to 22.06.00.078  [21:13:26]: Bug 24860 - Add ability to place item group level holds                                                                                                                                                  
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't create table `koha_myinstance`.`reserves` (errno: 150 "Foreign key constraint is incorrectly formed") at /usr/share/koha/lib/C4/Installer.pm line 741                     
                                       
dpkg: error processing package koha-common (--configure):    
```
```

DB host has a 10.3.34-MariaDB-0+deb10u1-log.
Comment 1 Katrin Fischer 2024-04-01 16:21:45 UTC
Do we have an idea what's going wrong here?
Comment 2 Pedro Amorim 2024-04-02 10:16:44 UTC
Unable to reproduce when upgrading k-t-d from 22.05.x to 23.11.x, that particular db rev runs without issue.
@Pablo are you able to run the following scripts and check their output (before upgrade):
misc/maintenance/search_for_data_inconsistencies.pl
misc/maintenance/audit_database.pl
Comment 3 Pablo AB 2024-04-02 23:50:43 UTC
@Pedro, after ~8', the output of search_for_data_inconsistencies.pl was:

```
== Items do not have itype defined ==
        * Item with itemnumber=39024 does not have a itype value, additionally no item type defined for biblionumber=22362
        [7 similar lines]
=> The system preference item-level_itypes expects item types to be defined at item level
```

Are you sure this version should have `audit_database.pl`? Is not there.

BTW, `perldoc search_for_data_inconsistencies.pl` return a malformed output.
Comment 4 Pedro Amorim 2024-04-03 08:33:18 UTC
(In reply to Pablo AB from comment #3)
> @Pedro, after ~8', the output of search_for_data_inconsistencies.pl was:
> 
> ```
> == Items do not have itype defined ==
>         * Item with itemnumber=39024 does not have a itype value,
> additionally no item type defined for biblionumber=22362
>         [7 similar lines]
> => The system preference item-level_itypes expects item types to be defined
> at item level
> ```
> 
> Are you sure this version should have `audit_database.pl`? Is not there.
> 
> BTW, `perldoc search_for_data_inconsistencies.pl` return a malformed output.

You're right, apologies. audit_database.pl was added by bug 34064 in version 23.11, although you likely would be able to backport it for this specific use case.

Looking at 220600078.pl, what you could do, before upgrading, is manually check the output of
SHOW CREATE TABLE reserves;
SHOW CREATE TABLE old_reserves;
SHOW CREATE TABLE item_groups;

and compare that with what's in kohastructure.sql. This may provide some clues.
Comment 5 Pablo AB 2024-04-03 19:17:41 UTC
COMMENTS apart, they are identical except for these differences:

reserves: These lines are swapped. Like this on show create:

​  KEY `itemtype` (`itemtype`),
  KEY `desk_id` (`desk_id`),

old_reserves: Only on show create:

  KEY `old_desk_id` (`desk_id`),

About `SHOW CREATE TABLE item_groups;`:

ERROR 1146 (42S02): Table 'koha_myinstance.item_groups' doesn't exist
Comment 6 Pedro Amorim 2024-04-04 08:32:11 UTC
Bug 24857 is adding the table item_groups. This is DBrev 220600014.
Bug 24860, the one you report failing for you, it's DBrev 220600078.
Both bugs were added for 22.11.

It appears, for some reason, your schema ended up without the item_groups table, and that should exist by the time DBrev 220600078 runs.
What's the full output of updatedatabase when you perform the update? Does bug 24857 DBrev 220600014 run? Does the message 'Added new table 'item_groups'' show? If not, any error (related to 220600014?)
Comment 7 Pablo AB 2024-04-09 23:42:00 UTC
> It appears, for some reason, your schema ended up without the item_groups table, and that should exist by the time DBrev 220600078 runs.

Not sure if there is a misunderstanding. Those tests were run on the current rolled back instance, not the broken middle-ground upgrade.

> What's the full output of updatedatabase when you perform the update? Does bug 24857 DBrev 220600014 run? Does the message 'Added new table 'item_groups'' show? If not, any error (related to 220600014?)

I tried again the upgrade. No mention of Bug 24857 or `item_groups` or 220600078 or 220600014.

In this broken state, cron:

/etc/cron.hourly/koha-common:
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'me.renewals_count' in 'field list' at /usr/share/koha/lib/Koha/Objects.pm line 317
myinstance: 11 status returned by "/usr/share/koha/bin/cronjobs/automatic_checkin.pl"
run-parts: /etc/cron.hourly/koha-common exited with return code 1

I'll leave it in this state, for any further analysis I could test before the rollback.

Also, if helps, I could attach the output of the `apt upgrade` (after the update, after moving 22.05 → 23.11 on koha.list).
Comment 8 Pablo AB 2024-04-10 18:02:58 UTC
In the broken state `SHOW CREATE TABLE item_groups;` also..

ERROR 1146 (42S02): Table 'koha_myinstance.item_groups' doesn't exist
Comment 9 Pablo AB 2024-04-15 23:16:04 UTC
I set to critical, as Jonathan Druart did to Bug 34520.