Bug 38602

Summary:	Columns bookings.creation_date and bookings.modification_date not added if multiple databases are in use
Product:	Koha	Reporter:	Emmi Takkinen <emmi.takkinen>
Component:	Database	Assignee:	Emmi Takkinen <emmi.takkinen>
Status:	Pushed to stable ---	QA Contact:	Martin Renvoize (ashimema) <martin.renvoize>
Severity:	blocker
Priority:	P5 - low	CC:	david, jonathan.druart, martin.renvoize, paul.derscheid, wainuiwitikapark
Version:	Main	Keywords:	additional_work_needed
Hardware:	All
OS:	All
Change sponsored?:	---	Patch complexity:	---
Documentation contact:		Documentation submission:
Text to go in the release notes:	This fixes the database update for Bug 37592 - Add a record of creation and modification to bookings, added in Koha 24.11.00. It covers the case where multiple Koha instances are being updated on the same server - the database update was only updating the first database.	Version(s) released in:	25.05.00,24.11.01
Circulation function:
Attachments:	Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema

Description Emmi Takkinen 2024-12-03 10:59:01 UTC

I have multiple databases in use for different versions of Koha and two of them are for Kohas current main version. One smaller one (database A) for running tests and other larger one (database B) to test e.g. loading times with large datasets. I updated both of them when version 24.11 was released. Today I tried to test some patches with database B and while checking out items, following error occured:

>DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'me.creation_date' in 'field list' at ../Koha/Koha/Objects.pm line 321

I searched for the column creation_date from my databases and only database A has it in bookings table. I then tried to revert my database B's version back to 24.05 and run updatedatabase.pl to it again. Update printed following:

>Upgrade to 24.06.00.029  [12:07:54]: Bug 37592 - Add creation_date, modification_date fields to bookings table
>Columns 'creation_date' and 'modification_date' already exist in 'bookings' table. Skipping...

In db_rev file 240600029.pl we determine if these columns are already in database by searching them from information_schema.COLUMNS. In environments with multiple databases (we have over 30 of them) in use this means that if one of the databases already has both these columns, adding them to other databases is skipped.

We should instead check if both of those columns exist with method column_exists, not from information_schema.

Comment 1 Katrin Fischer 2024-12-03 12:27:48 UTC

Emmi, thanks for the report and detective work. Are you working on a patch by chance? I have updated the severity.

Comment 2 Emmi Takkinen 2024-12-03 13:00:36 UTC

Created attachment 175071 [details] [review]
Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema

If one has multiple databases in use adding columns
bookings.creation_date and bookings.modification_date is added
just to first updated database and skipped in other updates.
This happens because we check if these columns already exist
in database from information_schema. We should instead make
this check with method column_exists.

To test:
1. Apply this patch.
3. Drop columns bookings.creation_date and bookings.modification_date:
ALTER TABLE bookings DROP COLUMN creation_date;
ALTER TABLE bookings DROP COLUMN modification_date;
2. Revert your database back to version 24.0600028:
UPDATE systempreferences SET value = "24.0600014" WHERE variable = "version";
3. Run updatedatabase.pl.
=> Check that columns were added.

If you happen to have two databases:
1. Check if you have columns in your bookings table:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN
('creation_date', 'modification_date');
=> Note that columns exist only in one of the databases.
2. Make sure you're using database missing columns
from booking table.
3. Apply this patch.
4. Revert back to version 24.0600028.
5. Run updatedatabase.pl
=> Check that columns were now added to database.

Sponsored-by: Koha-Suomi Oy

Comment 3 Emmi Takkinen 2024-12-03 13:01:20 UTC

Hopefully that test plan makes any sense :D

Comment 4 David Nind 2024-12-04 13:22:08 UTC

Created attachment 175176 [details] [review]
Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema

If one has multiple databases in use adding columns
bookings.creation_date and bookings.modification_date is added
just to first updated database and skipped in other updates.
This happens because we check if these columns already exist
in database from information_schema. We should instead make
this check with method column_exists.

To test:
1. Apply this patch.
3. Drop columns bookings.creation_date and bookings.modification_date:
ALTER TABLE bookings DROP COLUMN creation_date;
ALTER TABLE bookings DROP COLUMN modification_date;
2. Revert your database back to version 24.0600028:
UPDATE systempreferences SET value = "24.0600014" WHERE variable = "version";
3. Run updatedatabase.pl.
=> Check that columns were added.

If you happen to have two databases:
1. Check if you have columns in your bookings table:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN
('creation_date', 'modification_date');
=> Note that columns exist only in one of the databases.
2. Make sure you're using database missing columns
from booking table.
3. Apply this patch.
4. Revert back to version 24.0600028.
5. Run updatedatabase.pl
=> Check that columns were now added to database.

Sponsored-by: Koha-Suomi Oy
Signed-off-by: David Nind <david@davidnind.com>

Comment 5 David Nind 2024-12-04 13:22:50 UTC

Testing notes (using KTD):

1. I only have one database, so only tested that.

Comment 6 Martin Renvoize (ashimema) 2024-12-04 16:56:07 UTC

Damn.. how on earth did I miss this during QA :'(.

Thanks for the quick fix Emmi.. we really do need more people don't the QA team to share the load.

Comment 7 Martin Renvoize (ashimema) 2024-12-05 10:38:37 UTC

Created attachment 175237 [details] [review]
Bug 38602: Do not check if columns bookings.creation_date and bookings.modification_date from information_schema

If one has multiple databases in use adding columns
bookings.creation_date and bookings.modification_date is added
just to first updated database and skipped in other updates.
This happens because we check if these columns already exist
in database from information_schema. We should instead make
this check with method column_exists.

To test:
1. Apply this patch.
3. Drop columns bookings.creation_date and bookings.modification_date:
ALTER TABLE bookings DROP COLUMN creation_date;
ALTER TABLE bookings DROP COLUMN modification_date;
2. Revert your database back to version 24.0600028:
UPDATE systempreferences SET value = "24.0600014" WHERE variable = "version";
3. Run updatedatabase.pl.
=> Check that columns were added.

If you happen to have two databases:
1. Check if you have columns in your bookings table:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN
('creation_date', 'modification_date');
=> Note that columns exist only in one of the databases.
2. Make sure you're using database missing columns
from booking table.
3. Apply this patch.
4. Revert back to version 24.0600028.
5. Run updatedatabase.pl
=> Check that columns were now added to database.

Sponsored-by: Koha-Suomi Oy
Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Comment 8 Katrin Fischer 2024-12-05 12:47:40 UTC

Nice logical patch, thanks Emmi!

Comment 9 Katrin Fischer 2024-12-05 12:48:41 UTC

I want to push this right away. Getting the question out there: should we add another database update to fix this for libraries that might have already installed 24.11.00?

Comment 10 Katrin Fischer 2024-12-05 14:44:45 UTC

Pushed for 25.05!

Well done everyone, thank you!

Comment 11 Paul Derscheid 2024-12-10 16:07:05 UTC

Thanks for cleaning that up :)

Comment 12 Paul Derscheid 2024-12-12 13:31:34 UTC

Nice work everyone!

Pushed to 24.11.x for 24.11.01

Comment 13 David Nind 2024-12-12 18:02:19 UTC

I've had a go at adding a release note.

However, I don't really understand how you would have "multiple databases are in use", which I would like to explain so that librarians could understand.

Is this the case if you are updating multiple instances of Koha? Or something else?

Comment 14 Emmi Takkinen 2024-12-13 06:15:41 UTC

(In reply to David Nind from comment #13)
> I've had a go at adding a release note.
> 
> However, I don't really understand how you would have "multiple databases
> are in use", which I would like to explain so that librarians could
> understand.
> 
> Is this the case if you are updating multiple instances of Koha? Or
> something else?

Yeah, basically it's a case of updating multiple instances which have databases installed in same mysql server. I should have probably explained it like this in my comments :D

Comment 15 David Nind 2024-12-13 09:48:24 UTC

(In reply to Emmi Takkinen from comment #14)
> (In reply to David Nind from comment #13)
> > I've had a go at adding a release note.
> > 
> > However, I don't really understand how you would have "multiple databases
> > are in use", which I would like to explain so that librarians could
> > understand.
> > 
> > Is this the case if you are updating multiple instances of Koha? Or
> > something else?
> 
> Yeah, basically it's a case of updating multiple instances which have
> databases installed in same mysql server. I should have probably explained
> it like this in my comments :D

Thanks Emmi!

Comment 16 Wainui Witika-Park 2024-12-17 22:02:05 UTC

Not backporting to 24.05.x