Bug 36424

Do we have an idea what's going wrong here?

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

@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.

(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.

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

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?)

> 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).

In the broken state `SHOW CREATE TABLE item_groups;` also..

ERROR 1146 (42S02): Table 'koha_myinstance.item_groups' doesn't exist

I set to critical, as Jonathan Druart did to Bug 34520.

Pablo: can you share your DB structure? There's a mysqldump option for that. Otherwise the exchange is difficult to follow.

Thanks in advance!

(In reply to Tomás Cohen Arazi from comment #10)
> Pablo: can you share your DB structure? There's a mysqldump option for that.
> Otherwise the exchange is difficult to follow.
> 
> Thanks in advance!

I mean the one before the attempt to upgrade.

Did you overcome this and come to the conclusion it was a local data issue?

Created attachment 167670 [details]
Schema of the DB, with mysqldump --no-data

> Did you overcome this and come to the conclusion it was a local data issue?

Nop, I couldn't jump to this conclusion. Was a pretty standard installation started from around 19.05, AFAIK was never touched directly.

Pablo, can you try the upgrade again, and copy everything in the output starting from
Upgrading database schema for myinstance 
until the error appears?

Created attachment 167773 [details]
apt upgrade output, 23.11.05-1 all [upgradable from: 22.05.21-1], schema change section

Thanks for sharing your apt upgrade output, Pablo. It seems like your system didn't roll back all the way after one of the previous upgrade attempts - your database upgrade started at 22.06.00.041, but it should have started at 22.06.00.000. That would explain why your system doesn't have the item_groups table, since it skipped that part of the upgrade.

You might need to try backing up your data and importing into a fresh install, but there might be other options - I'm not an expert on upgrading. I'd suggest asking around on the Koha community chat (https://chat.koha-community.org/), or on the mailing lists.

I asked on chat, https://chat.koha-community.org/koha-community/pl/drr98y7z13dqxj8mrpjbj8f6je giving more details. The issue happen on two instances on two different hosts, one of them a production server which I never tried to upgrade out of 22.05.x. I have no clue why I did get to have a .06/development and a 040. The other host/instance (test/sandbox) have the same strange version. 

Not sure also how to solve it either. Just an UPDATE to version, changing the `22.0600040` to `22.0500000`? All the schema upgrades are idempotent?

It seems that it's just me, so maybe this bug should be closed.

(In reply to Pablo AB from comment #18)
> I asked on chat,
> https://chat.koha-community.org/koha-community/pl/drr98y7z13dqxj8mrpjbj8f6je
> giving more details. The issue happen on two instances on two different
> hosts, one of them a production server which I never tried to upgrade out of
> 22.05.x. I have no clue why I did get to have a .06/development and a 040.
> The other host/instance (test/sandbox) have the same strange version. 
> 
> Not sure also how to solve it either. Just an UPDATE to version, changing
> the `22.0600040` to `22.0500000`? All the schema upgrades are idempotent?
> 
> It seems that it's just me, so maybe this bug should be closed.

You could take a backup and try that. The database updates should be idempotent.

Important bit is to really pay attention on the output of the update script to see where it gets stuck and resolve any issues found before continuing.

This time I hit a further step: It goes through Bug 24860 (Upgrade to 22.06.00.078) but crash later, on Bug 29002 - Add bookings table (Upgrade to 23.06.00.061).

1. apt update, upgrade, on 22.05 (22.0600040 on db)
2. koha-mysql $INSTANCE -e  'UPDATE systempreferences SET VALUE = '22.0500000' WHERE variable = "version"'
3. Set 23.11 on `koha.list`
4. apt update, upgrade; koha-common (23.11.06-2) over (22.05.22-1), stdout/err to txt (attached).

Created attachment 168403 [details]
apt upgrade output after changing version to 22.0500000

Can you please paste the output/error here?

Created attachment 168446 [details] [review]
Bug 36424: Remove trailing comma

(In reply to Pablo AB from comment #20)
> This time I hit a further step: It goes through Bug 24860 (Upgrade to
> 22.06.00.078) but crash later, on Bug 29002 - Add bookings table (Upgrade to
> 23.06.00.061).
> 
> 1. apt update, upgrade, on 22.05 (22.0600040 on db)
> 2. koha-mysql $INSTANCE -e  'UPDATE systempreferences SET VALUE =
> '22.0500000' WHERE variable = "version"'
> 3. Set 23.11 on `koha.list`
> 4. apt update, upgrade; koha-common (23.11.06-2) over (22.05.22-1),
> stdout/err to txt (attached).

Are you able to test my latest patch for this use case? I believe there is an extra trailing comma in the db rev.
Also, can you confirm if the Koha system this is happening on has the following plugin installed:
https://github.com/bywatersolutions/koha-plugin-room-reservations

> Are you able to test my latest patch for this use case? I believe there is an extra trailing comma in the db rev.

I also saw that trailing comma, since it seems to break on that query, and all variables are defined and with its contents. Removing the comma it runs the query, but breaks on the next query, `ALTER TABLE $rooms_table RENAME INDEX..`.

> Also, can you confirm if the Koha system this is happening on has the following plugin installed:
https://github.com/bywatersolutions/koha-plugin-room-reservations

Yes! It is installed:

- Name: Room Reservations Plugin ENABLED
- Description: This plugin provides a room reservation solution on both intranet and OPAC interfaces.
- Author: Lee Jamison
- Plugin version: 3.2.1
- Minimum Koha version: 3.22
- Last updated: 22/08/2022

Should be disabled/removed before the upgrade?

(In reply to Katrin Fischer from comment #22)
> Can you please paste the output/error here?

It's attached, also here:

Upgrading database schema for myinstance
Upgrade to 22.06.00.000  [17:13:49]: Increase DBRev for 22.06
Upgrade to 22.06.00.001  [17:13:49]: Bug 23659 - Allow hold pickup location to default to item home branch for item-level holds
[...]
Upgrade to 23.06.00.061  [17:14:59]: Bug 29002 - Add bookings table
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5 at /usr/share/koha/lib/C4/Installer.pm line 741
dpkg: error processing package koha-common (--configure):

Breaks on RENAME TABLE query on 230600061.pl, but removing the comma (line 5 on the query), then on the first ALTER TABLE. Maybe should be an IF EXIST or DROP TABLEs?

(In reply to Pablo AB from comment #26)
> Breaks on RENAME TABLE query on 230600061.pl, but removing the comma (line 5
> on the query), then on the first ALTER TABLE. Maybe should be an IF EXIST or
> DROP TABLEs?

How do we know it's then breaking on the first ALTER TABLE? What's the error?

 (In reply to Pablo AB from comment #25)
> Should be disabled/removed before the upgrade?

That plugin creates and utilizes a database table called 'bookings'.
The DB REV we're discussing here is renaming that table if it already exists, because the table added in bug 29002 is also named 'bookings'.

In theory, if you uninstall that plugin:
https://github.com/bywatersolutions/koha-plugin-room-reservations/blob/master/Koha/Plugin/Com/MarywoodUniversity/RoomReservations.pm#L248
It should remove all plugin related tables and then the DB rev will no longer consider renaming anything because 'bookings' will not exist.

I checked out branch 23.05.x (previous to bookings bug 29002)
I installed bywater's bookings plugin version 3.2.1 (when it was still using table named 'bookings'.
I ran the db rev, it errored, I applied my patch, it no longer errors and finishes successfully:

kohadev-koha@kohadevbox:koha(23.05.x)$ updatedatabase
Migrated room reservations plugin to it's own namespace
You MUST upgrade to the latest room reservation plugin to continue using it
Plugin disabled, please re-enable once you have upgraded it
DEV atomic update /kohadevbox/koha/installer/data/mysql/atomicupdate/test_29002.pl  [09:36:21]: Bug 29002 - Add bookings table
        Added new table 'bookings'
        Added column 'items.bookable'
        Added column 'deleteditems.bookable'
        Added new permission 'manage_bookings'

@Katrin the patch "Bug 36424; Remove trailing comma" is still required.

(In reply to Pedro Amorim from comment #28)
> I checked out branch 23.05.x (previous to bookings bug 29002)
> I installed bywater's bookings plugin version 3.2.1 (when it was still using
> table named 'bookings'.
> I ran the db rev, it errored, I applied my patch, it no longer errors and
> finishes successfully:
> 
> kohadev-koha@kohadevbox:koha(23.05.x)$ updatedatabase
> Migrated room reservations plugin to it's own namespace
> You MUST upgrade to the latest room reservation plugin to continue using it
> Plugin disabled, please re-enable once you have upgraded it
> DEV atomic update
> /kohadevbox/koha/installer/data/mysql/atomicupdate/test_29002.pl 
> [09:36:21]: Bug 29002 - Add bookings table
>         Added new table 'bookings'
>         Added column 'items.bookable'
>         Added column 'deleteditems.bookable'
>         Added new permission 'manage_bookings'
> 
> @Katrin the patch "Bug 36424; Remove trailing comma" is still required.

Thanks Pedro - easiest way to get my attention is to set back to PQA - it shows up in my list that way.

Pushed "Remove trailing comma" as a follow-up to main.

... not a real follow-up. Sorry, got a little confused here.

I want to update this bug's title to mention that fix specifically, but wanting on Pablo's reply to my last comments to ensure nothing else is required here and we've isolated the issue successfully.

I will wait with the actual push for confirmation from Pablo.

*** Bug 37259 has been marked as a duplicate of this bug. ***

I think as this has just been reported another time we should go ahead and fix the obvious syntax error. If there are other mistakes, we can put them on a new bug later.

Pushed for 24.11!

Well done everyone, thank you!

@Katrin I'm changing the bug title to 23.06.00.061 as that is the DB rev with the issue and the one being fixed here.

(In reply to Pedro Amorim from comment #37)
> @Katrin I'm changing the bug title to 23.06.00.061 as that is the DB rev
> with the issue and the one being fixed here.

Perfect, thank you.

(In reply to Pedro Amorim from comment #27)
> (In reply to Pablo AB from comment #26)
>> Breaks on RENAME TABLE query on 230600061.pl, but removing the comma (line 5
>> on the query), then on the first ALTER TABLE. Maybe should be an IF EXIST or
>> DROP TABLEs?
>
> How do we know it's then breaking on the first ALTER TABLE? What's the error?

I added some dirty print "after ALTER"
 
>  (In reply to Pablo AB from comment #25)
> That plugin creates and utilizes a database table called 'bookings'.
> ...
> It should remove all plugin related tables and then the DB rev will no
> longer consider renaming anything because 'bookings' will not exist.

I've uninstalled the plugin and now `koha-mysql $INSTANCE -e  'SHOW TABLES;' | grep book` only returns `aqbooksellers`.

---

I disabled and uninstalled the plugin and then followed the upgrade steps (as in comment 20) and finished successfully!! :)

Backported to 24.05.x for upcoming 24.05.02

Pushed to 23.11.x for 23.11.07

not backporting to 23.05.x unless requested