[Bug 38602] New: Columns bookings.creation_date and bookings.modification_date not added if multiple databases are in use
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Bug ID: 38602 Summary: Columns bookings.creation_date and bookings.modification_date not added if multiple databases are in use Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: emmi.takkinen@koha-suomi.fi QA Contact: testopia@bugs.koha-community.org 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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |blocker --- Comment #1 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Emmi, thanks for the report and detective work. Are you working on a patch by chance? I have updated the severity. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com, | |martin.renvoize@ptfs-europe | |.com, | |paul.derscheid@lmscloud.de -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Assignee|koha-bugs@lists.koha-commun |emmi.takkinen@koha-suomi.fi |ity.org | --- Comment #2 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- Created attachment 175071 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=175071&action=edit 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #3 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- Hopefully that test plan makes any sense :D -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #175071|0 |1 is obsolete| | --- Comment #4 from David Nind <david@davidnind.com> --- Created attachment 175176 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=175176&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #5 from David Nind <david@davidnind.com> --- Testing notes (using KTD): 1. I only have one database, so only tested that. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #6 from Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #175176|0 |1 is obsolete| | --- Comment #7 from Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> --- Created attachment 175237 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=175237&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |martin.renvoize@ptfs-europe |y.org |.com Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Nice logical patch, thanks Emmi! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |additional_work_needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |25.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #10 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 25.05! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #11 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Thanks for cleaning that up :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.05.00 |25.05.00,24.11.01 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #12 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Nice work everyone! Pushed to 24.11.x for 24.11.01 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This fixes the database release notes| |change for Bug 37592 - Add | |a record of creation and | |modification to bookings, | |added in Koha 24.11.00. It | |covers the case where | |multiple databases are in | |use - the database update | |was only updating the first | |database. --- Comment #13 from David Nind <david@davidnind.com> --- 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? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 --- Comment #14 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- (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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the|This fixes the database |This fixes the database release notes|change for Bug 37592 - Add |update for Bug 37592 - Add |a record of creation and |a record of creation and |modification to bookings, |modification to bookings, |added in Koha 24.11.00. It |added in Koha 24.11.00. It |covers the case where |covers the case where |multiple databases are in |multiple Koha instances are |use - the database update |being updated on the same |was only updating the first |server - the database |database. |update was only updating | |the first database. --- Comment #15 from David Nind <david@davidnind.com> --- (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! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wainuiwitikapark@catalyst.n | |et.nz --- Comment #16 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 24.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|additional_work_needed | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #17 from Fridolin Somers <fridolin.somers@biblibre.com> --- May we close ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Needs documenting --- Comment #18 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11.x (In reply to Fridolin Somers from comment #17)
May we close ?
Yup! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38602 Aude Charillon <aude.charillon@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED CC| |aude.charillon@openfifth.co | |.uk Status|Needs documenting |RESOLVED --- Comment #19 from Aude Charillon <aude.charillon@openfifth.co.uk> --- No need for an update to the Koha Manual. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org