Another DBI Exception for foreign key constraint in upgrade to Koha
Hi, The following message was sent and held by the moderator as being too long ten days ago. I have been unable to update to the current version because of this issue. I have removed the detail about the environment and installation that I included and that are usually asked for in a bug report. I will send the details if they are wanted Hi All, It seems like that for every other update I experience a DBI Exception in the update process. In the latest upgrade of 22.05.04-1 to 22.05.05-1 I have received the message: Upgrade to 22.05.04.002 [14:33:32]: Bug 31086 - Do not allow null values in branchcodes for reserves ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'branchcode': used in a foreign key constraint 'reserves_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 739 Indeed, there are null values for branchcodes in the reserves table as I do not have any courses or reserves in my system. Is there a fix for this error? I have included the console log below: Thanks --Dave ------------------------------------------------------------------------------- David L. Whelchel 725 SE Derby Street Pullman, WA 99163 whelchel@pullman.com
Hi David, please have a look at this bug and the comments there: *Bug 31673* <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31673> - DB update of bug 31086 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086> fails: Cannot change column 'branchcode': used in a foreign key constraint Hope this helps, Katrin On 10.10.22 22:40, David L. Whelchel wrote:
Hi,
The following message was sent and held by the moderator as being too long ten days ago. I have been unable to update to the current version because of this issue. I have removed the detail about the environment and installation that I included and that are usually asked for in a bug report. I will send the details if they are wanted
Hi All,
It seems like that for every other update I experience a DBI Exception in the update process. In the latest upgrade of 22.05.04-1 to 22.05.05-1 I have received the message:
Upgrade to 22.05.04.002 [14:33:32]: Bug 31086 - Do not allow null values in branchcodes for reserves ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'branchcode': used in a foreign key constraint 'reserves_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 739
Indeed, there are null values for branchcodes in the reserves table as I do not have any courses or reserves in my system. Is there a fix for this error? I have included the console log below:
Thanks
--Dave ------------------------------------------------------------------------------- David L. Whelchel 725 SE Derby Street Pullman, WA 99163 whelchel@pullman.com
_______________________________________________
Koha mailing listhttp://koha-community.org Koha@lists.katipo.co.nz Unsubscribe:https://lists.katipo.co.nz/mailman/listinfo/koha
Hi, on my system using mysql I had the same error. Comment 34 for Bug 31086 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086#c34 helps. It seems that KOHA with mariadb is better tested than KOHA with mysql. Is there a way to exchange the underlying database mysql by mariadb? Greetings Harald Am 10.10.22 um 23:00 schrieb Katrin Fischer:
Hi David,
please have a look at this bug and the comments there:
*Bug 31673* <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31673> - DB update of bug 31086 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086> fails: Cannot change column 'branchcode': used in a foreign key constraint
Hope this helps,
Katrin
On 10.10.22 22:40, David L. Whelchel wrote:
Hi,
The following message was sent and held by the moderator as being too long ten days ago. I have been unable to update to the current version because of this issue. I have removed the detail about the environment and installation that I included and that are usually asked for in a bug report. I will send the details if they are wanted
Hi All,
It seems like that for every other update I experience a DBI Exception in the update process. In the latest upgrade of 22.05.04-1 to 22.05.05-1 I have received the message:
Upgrade to 22.05.04.002 [14:33:32]: Bug 31086 - Do not allow null values in branchcodes for reserves ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'branchcode': used in a foreign key constraint 'reserves_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 739
Indeed, there are null values for branchcodes in the reserves table as I do not have any courses or reserves in my system. Is there a fix for this error? I have included the console log below:
Thanks
--Dave -------------------------------------------------------------------------------
David L. Whelchel 725 SE Derby Street Pullman, WA 99163 whelchel@pullman.com
_______________________________________________
Koha mailing listhttp://koha-community.org Koha@lists.katipo.co.nz Unsubscribe:https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Katrin, Both bugs are resolved, but I'm having this issue while upgrading today from 20.05 to 22.05 on a Debian 9.13: Upgrade to 22.05.06.001 [19:37:23]: Bug 30483 - Make issues.borrowernumber and itemnumber NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'borrowernumber': used in a foreign key constraint 'issues_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 739 Not sure if I'm hitting another similar bug. SELECT * FROM issues WHERE borrowernumber IS NULL OR itemnumber IS NULL; doesn't return any row. It has only one row with ids on those columns. *reserves* table has no rows. Maybe Bug 32240 should be backported? Regards, Pablo On Mon, 10 Oct 2022 at 18:01, Katrin Fischer <katrin.fischer.83@web.de> wrote:
Hi David,
please have a look at this bug and the comments there:
*Bug 31673* <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31673> - DB update of bug 31086 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086> fails: Cannot change column 'branchcode': used in a foreign key constraint
Hope this helps,
Katrin
Hi Pablo,
Both bugs are resolved, but I'm having this issue while upgrading today from 20.05 to 22.05 on a Debian 9.13:
It's my understanding that 22.05 requires Debian 10 or 11, per <https://wiki.koha-community.org/wiki/System_requirements_and_recommendations>. (Don't ask me how I learned this!) Respectfully, / Bruce / Bruce A. Metcalf Augustan Library
Hi Pablo, the database update query here should be: https://git.koha-community.org/Koha-community/Koha/src/branch/22.05.x/instal... Do you have entries in your issues table, where the borrowernumber is NULL? Hope that helps, Katrin On 03.12.22 00:10, Pablo Bianchi wrote:
Hi Katrin,
Both bugs are resolved, but I'm having this issue while upgrading today from 20.05 to 22.05 on a Debian 9.13:
Upgrade to 22.05.06.001 [19:37:23]: Bug 30483 - Make issues.borrowernumber and itemnumber NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'borrowernumber': used in a foreign key constraint 'issues_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 739
Not sure if I'm hitting another similar bug. SELECT * FROM issues WHERE borrowernumber IS NULL OR itemnumber IS NULL; doesn't return any row. It has only one row with ids on those columns. /reserves/ table has no rows. Maybe Bug 32240 should be backported?
Regards, Pablo
On Mon, 10 Oct 2022 at 18:01, Katrin Fischer <katrin.fischer.83@web.de> wrote:
Hi David,
please have a look at this bug and the comments there:
*Bug 31673* <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31673> - DB update of bug 31086 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086> fails: Cannot change column 'branchcode': used in a foreign key constraint
Hope this helps,
Katrin
Thank you all for the answers! On Sat, 3 Dec 2022 at 18:59, Katrin Fischer <katrin.fischer.83@web.de> wrote:
Do you have entries in your issues table, where the borrowernumber is NULL?
Nope. In fact, *issues* has only one row, with its integer on borrowernumber: issue_id: 2. *borrowernumber: 53805*. issuer_id: NULL. *itemnumber: 1581.* date_due: 2020-10-08 23:59:00. branchcode: BC. returndate: NULL. lastreneweddate: NULL. renewals: 0. unseen_renewals: 0. auto_renew: 0. auto_renew_error: NULL. timestamp: 2020-10-07 15:29:52. issuedate: 2020-10-07 15:29:52. onsite_checkout: 0. note: NULL. notedate: NULL. noteseen: NULL. On Sun, 4 Dec 2022 at 01:00, Tomas Cohen Arazi <tomascohen@gmail.com> wrote:
That's a data issue. Maybe an old bug let things in wrongly on the db, it incomplete data migration.
You should do something with those checkouts with no linked patron, and the upgrade script will continue where it left.
borrowernumber (and itemnumber) are not NULL in the only row of issues table. Also, the borrowernumber (53805) exists on borrowers table. Could be something else? As a last resource (I wish to avoid) I could DELETE the row. This will always cascade any action and can't break any consistency? JFTR, even of course upgrading to Debian 10 has nothing to do with this, I had some trouble with Apache and Memcached and new security features of systemd (like PrivateTmp). I had to disable them.
Dear all, I had the same error. Here is the solution: SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to', MODIFY COLUMN itemnumber int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out' SET FOREIGN_KEY_CHECKS = 1; Best Regards, Eugen Bastron Am 04.12.22, 08:48 schrieb "Koha im Auftrag von Pablo Bianchi" <koha-bounces@lists.katipo.co.nz <mailto:koha-bounces@lists.katipo.co.nz> im Auftrag von pablo.bianchi@gmail.com <mailto:pablo.bianchi@gmail.com>>: Thank you all for the answers! On Sat, 3 Dec 2022 at 18:59, Katrin Fischer <katrin.fischer.83@web.de <mailto:katrin.fischer.83@web.de>> wrote:
Do you have entries in your issues table, where the borrowernumber is NULL?
Nope. In fact, *issues* has only one row, with its integer on borrowernumber: issue_id: 2. *borrowernumber: 53805*. issuer_id: NULL. *itemnumber: 1581.* date_due: 2020-10-08 23:59:00. branchcode: BC. returndate: NULL. lastreneweddate: NULL. renewals: 0. unseen_renewals: 0. auto_renew: 0. auto_renew_error: NULL. timestamp: 2020-10-07 15:29:52. issuedate: 2020-10-07 15:29:52. onsite_checkout: 0. note: NULL. notedate: NULL. noteseen: NULL. On Sun, 4 Dec 2022 at 01:00, Tomas Cohen Arazi <tomascohen@gmail.com <mailto:tomascohen@gmail.com>> wrote:
That's a data issue. Maybe an old bug let things in wrongly on the db, it incomplete data migration.
You should do something with those checkouts with no linked patron, and the upgrade script will continue where it left.
borrowernumber (and itemnumber) are not NULL in the only row of issues table. Also, the borrowernumber (53805) exists on borrowers table. Could be something else? As a last resource (I wish to avoid) I could DELETE the row. This will always cascade any action and can't break any consistency? JFTR, even of course upgrading to Debian 10 has nothing to do with this, I had some trouble with Apache and Memcached and new security features of systemd (like PrivateTmp). I had to disable them. _______________________________________________ Koha mailing list http://koha-community.org <http://koha-community.org> Koha@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha <https://lists.katipo.co.nz/mailman/listinfo/koha>
That's a data issue. Maybe an old bug let things in wrongly on the db, it incomplete data migration. You should do something with those checkouts with no linked patron, and the upgrade script will continue where it left. Best regards El vie, 2 dic 2022 20:12, Pablo Bianchi <pablo.bianchi@gmail.com> escribió:
Hi Katrin,
Both bugs are resolved, but I'm having this issue while upgrading today from 20.05 to 22.05 on a Debian 9.13:
Upgrade to 22.05.06.001 [19:37:23]: Bug 30483 - Make issues.borrowernumber and itemnumber NOT NULL ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'borrowernumber': used in a foreign key constraint 'issues_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 739
Not sure if I'm hitting another similar bug. SELECT * FROM issues WHERE borrowernumber IS NULL OR itemnumber IS NULL; doesn't return any row. It has only one row with ids on those columns. *reserves* table has no rows. Maybe Bug 32240 should be backported?
Regards, Pablo
On Mon, 10 Oct 2022 at 18:01, Katrin Fischer <katrin.fischer.83@web.de> wrote:
Hi David,
please have a look at this bug and the comments there:
*Bug 31673* <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31673> - DB update of bug 31086 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31086> fails: Cannot change column 'branchcode': used in a foreign key constraint
Hope this helps,
Katrin
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (7)
-
Bruce A. Metcalf -
David L. Whelchel -
DHIM IT -
Harald Schaefer -
Katrin Fischer -
Pablo Bianchi -
Tomas Cohen Arazi