[Bug 32399] New: Database update for bug 30483 is failing
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Bug ID: 32399 Summary: Database update for bug 30483 is failing Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Installation and upgrade (web-based installer) Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com Depends on: 30483 We have had 2 reports recently of this database update failing: 22.06.00.041 [09:45:46]: Bug 30483 - Make issues.borrowernumber and issues.itemnumber NOT NULLERROR - {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 And: 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 Both refer to the same bug/SQL statements, but backported to different versions. Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30483 [Bug 30483] Do not allow NULL in issues.borrowernumber and issues.itemnumber -- 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=32399 --- Comment #1 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- While we first thought this to be a data issue, it might not be the case, from the mailing list: 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 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? -- 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=32399 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Pablo AB <pablo.bianchi@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |pablo.bianchi@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #2 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Could you provide details on the exact SQL version used here ? -- 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=32399 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |m.de.rooy@rijksmuseum.nl |ity.org | Status|NEW |ASSIGNED -- 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=32399 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #3 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 144428 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=144428&action=edit Bug 32399: Unset foreign key checks while changing issues constraint Some users have reported that the dbrev of bug 30483 fails. This might depend on bad data, SQL server version, etc. The easiest workaround is now to unset the check temporarily. Maybe we should also warn for issues without itemnumber or borrowernumber on About? Or remove them? Test plan: Reset your version in order to pass this dbrev again. So below 22.06.00.041. * update systempreferences set value='22.0600040' where variable = 'Version'; Run updatedatabase. Bonus: Create an issues record with invalid itemnumber (set FK check off when doing so). Verify that dbrev prints warning about it. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #4 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- This needs a backport to 22.11 and 22.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #5 from Pablo AB <pablo.bianchi@gmail.com> --- (In reply to Marcel de Rooy from comment #2)
Could you provide details on the exact SQL version used here ?
innodb_version 5.6.44-86.0 protocol_version 10 version 10.1.41-MariaDB-0+deb9u1 version_comment Debian 9.9 version_compile_machine x86_64 version_compile_os debian-linux-gnu version_malloc_library system jemalloc version_ssl_library YaSSL 2.4.4 wsrep_patch_version wsrep_25.24 Debian 10 (lsb_release -a) koha-common 22.05.07-1 (upgrading from 20.05) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #6 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Pablo AB from comment #5)
innodb_version 5.6.44-86.0 protocol_version 10 version 10.1.41-MariaDB-0+deb9u1 version_comment Debian 9.9 version_compile_machine x86_64 version_compile_os debian-linux-gnu version_malloc_library system jemalloc version_ssl_library YaSSL 2.4.4 wsrep_patch_version wsrep_25.24
Debian 10 (lsb_release -a) koha-common 22.05.07-1 (upgrading from 20.05)
Ah, thanks. We seem to bump on SQL version differences. I tested with MariaDB 10.4/10.5 without the issues you reported. But 10.1 apparently has. The attached patch should address it. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com --- Comment #7 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- I don't think we should turn FOREIGN_KEY_CHECKS off. IMO we should add the warning and stop the upgrade process, wait for them to fix the data, then continue the upgrade. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #8 from Tomás Cohen Arazi <tomascohen@gmail.com> --- (In reply to Jonathan Druart from comment #7)
I don't think we should turn FOREIGN_KEY_CHECKS off. IMO we should add the warning and stop the upgrade process, wait for them to fix the data, then continue the upgrade.
I agree. We should definitely give a better error message. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #9 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Yes, but could this be sql server/version specific ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #10 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- As reported, there should not have been bad data ?? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #11 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Marcel de Rooy from comment #10)
As reported, there should not have been bad data ??
yes, we had a good report on it not being the data. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #144428|0 |1 is obsolete| | --- Comment #12 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 144448 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=144448&action=edit Bug 32399: Remove FK constraints while changing columns Some users have reported that the dbrev of bug 30483 fails. This might depend on bad data, SQL server version, etc. We now remove the constraints temporarily. Test plan: Reset your version in order to pass this dbrev again. So below 22.06.00.041. * update systempreferences set value='22.0600040' where variable = 'Version'; Run updatedatabase. Bonus: Create an issues record with invalid itemnumber (set FK check off when doing so). Verify that dbrev fails and installer stops. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #13 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Plan B activated: remove constraints temporarily. Bail out when we have bad data. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #14 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Observation from testing: The step3.tt template sticks the error to each line of the output. When you want to print 4 lines, this is kind of silly. Not solving this here. If you like, open a new report :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #144448|0 |1 is obsolete| | --- Comment #15 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 144516 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=144516&action=edit Bug 32399: Remove FK constraints while changing columns Some users have reported that the dbrev of bug 30483 fails. This might depend on bad data, SQL server version, etc. We now remove the constraints temporarily. Test plan: Reset your version in order to pass this dbrev again. So below 22.06.00.041. * update systempreferences set value='22.0600040' where variable = 'Version'; Run updatedatabase. Bonus: Create an issues record with invalid itemnumber (set FK check off when doing so). Verify that dbrev fails and installer stops. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> 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=32399 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com 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=32399 --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- This is a real improvement in my opinion.. signing off. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=32399 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #144516|0 |1 is obsolete| | --- Comment #17 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 144616 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=144616&action=edit Bug 32399: Remove FK constraints while changing columns Some users have reported that the dbrev of bug 30483 fails. This might depend on bad data, SQL server version, etc. We now remove the constraints temporarily. Test plan: Reset your version in order to pass this dbrev again. So below 22.06.00.041. * update systempreferences set value='22.0600040' where variable = 'Version'; Run updatedatabase. Bonus: Create an issues record with invalid itemnumber (set FK check off when doing so). Verify that dbrev fails and installer stops. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |23.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=32399 --- Comment #18 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.05. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #19 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- RMaints: We need this in 22.11 and 22.05. Please let me know if you need assistance. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|23.05.00 |23.05.00,22.11.01 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #20 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice work everyone! Pushed to 22.11.x for the next release -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_22_05_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #21 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Why wasn't this pushed to 22.05 yet ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #22 from Lucas Gass <lucas@bywatersolutions.com> --- (In reply to Marcel de Rooy from comment #21)
Why wasn't this pushed to 22.05 yet ?
I'm confused about this patch. It won't apply to 22.05 because the update is directly in installer/data/mysql/atomicupdate/db_revs. Should it not be in 'installer/data/mysql/atomicupdate/atomicupdate' and the Rmaints move it to db_revs? Sorry for the confusion. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #23 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Lucas Gass from comment #22)
(In reply to Marcel de Rooy from comment #21)
Why wasn't this pushed to 22.05 yet ?
I'm confused about this patch. It won't apply to 22.05 because the update is directly in installer/data/mysql/atomicupdate/db_revs.
Should it not be in 'installer/data/mysql/atomicupdate/atomicupdate' and the Rmaints move it to db_revs?
Sorry for the confusion.
Sure. Good plan! Dont wait any longer. This is a fix for something that was pushed to 22.05.07. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #24 from Lucas Gass <lucas@bywatersolutions.com> --- Created attachment 149235 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149235&action=edit Bug 32399: [22.05.x] Remove FK constraints while changing columns -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #25 from Lucas Gass <lucas@bywatersolutions.com> --- Marcel, Please have a look at the patch I added for 22.05.x and conform it is correct. Thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 --- Comment #26 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Lucas Gass from comment #25)
Marcel,
Please have a look at the patch I added for 22.05.x and conform it is correct.
Thanks!
Looks fine to me. Thanks. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.05.00,22.11.01 |23.05.00,22.11.01,22.05.12 released in| | Status|Pushed to stable |Pushed to oldstable --- Comment #27 from Lucas Gass <lucas@bywatersolutions.com> --- Backported to 22.05.x for upcoming 22.05.12 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_22_05_candidate | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32399 Arthur Suzuki <arthur.suzuki@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED CC| |arthur.suzuki@biblibre.com Status|Pushed to oldstable |RESOLVED --- Comment #28 from Arthur Suzuki <arthur.suzuki@biblibre.com> --- won't backport, depends on 30483 which is not applied to 21.11.x -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org