Bug 30483 - Do not allow NULL in issues.borrowernumber and issues.itemnumber
Summary: Do not allow NULL in issues.borrowernumber and issues.itemnumber
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Marcel de Rooy
QA Contact: Joonas Kylmälä
URL:
Keywords:
Depends on:
Blocks: 30486 32399
  Show dependency treegraph
 
Reported: 2022-04-07 09:43 UTC by Marcel de Rooy
Modified: 2023-12-28 20:42 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00, 22.05.07


Attachments
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL (2.91 KB, patch)
2022-04-07 13:06 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 30483: DBIx changes for issues table (2.96 KB, patch)
2022-04-07 13:06 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 30483: (DBIx schema) Changes to Issue.pm (2.72 KB, patch)
2022-08-16 13:43 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL (2.94 KB, patch)
2022-08-23 14:48 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 30483: (DBIx schema) Changes to Issue.pm (2.75 KB, patch)
2022-08-23 14:48 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL (3.08 KB, patch)
2022-08-28 14:49 UTC, Joonas Kylmälä
Details | Diff | Splinter Review
Bug 30483: (DBIx schema) Changes to Issue.pm (2.89 KB, patch)
2022-08-28 14:49 UTC, Joonas Kylmälä
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Marcel de Rooy 2022-04-07 09:43:58 UTC
Sync problem between structure and dbrevs.
For old prod databases only (that passed the 3.05 dbrevs)
Note that I did not expect such a difference in a vital table like issues !

update     $DBversion = "3.05.00.009"; $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL");     $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL");

Current structure allows them to be NULL.
I wonder if that is correct. An issue should have a borrower and an item, right?
Comment 1 Marcel de Rooy 2022-04-07 13:06:25 UTC
Created attachment 133079 [details] [review]
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL

Note: This change does NOT apply to old_issues, where constraints
may result in nullifying these columns.

Test plan:
Run dbrev.
Try checkout, checkin.
Comment 2 Marcel de Rooy 2022-04-07 13:06:28 UTC
Created attachment 133080 [details] [review]
Bug 30483: DBIx changes for issues table

Note: You may observe that DBIx drops the LEFT join_type here.
There is no reason to be worried about that. If a FK column
allows NULL values, DBIx sets it to LEFT. We remove the NULL
here and DBIx now defaults to INNER again.

No test plan.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 3 Marcel de Rooy 2022-08-16 13:43:27 UTC
Created attachment 139195 [details] [review]
Bug 30483: (DBIx schema) Changes to Issue.pm

Compare with results of update_dbix_class_files.pl.
Comment 4 Marcel de Rooy 2022-08-16 13:44:59 UTC
Note: You may observe that DBIx drops the LEFT join_type here.
There is no reason to be worried about that. If a FK column
allows NULL values, DBIx sets it to LEFT. We remove the NULL
here and DBIx now defaults to INNER again.
Comment 5 Marcel de Rooy 2022-08-23 14:43:58 UTC
Note from a discussion on IRC:

We do not anonymize active issues. So no problem there.
And if we mark an item as lost but not returned, the issue should still have an itemnumber and a borrowernumber. So no problem too.
Comment 6 Martin Renvoize 2022-08-23 14:48:37 UTC
Created attachment 139682 [details] [review]
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL

Note: This change does NOT apply to old_issues, where constraints
may result in nullifying these columns.

Test plan:
Run dbrev.
Try checkout, checkin.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 7 Martin Renvoize 2022-08-23 14:48:40 UTC
Created attachment 139683 [details] [review]
Bug 30483: (DBIx schema) Changes to Issue.pm

Compare with results of update_dbix_class_files.pl.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize 2022-08-23 14:49:04 UTC
Makes sense to me.. Signing off
Comment 9 Joonas Kylmälä 2022-08-28 14:49:42 UTC
Created attachment 139924 [details] [review]
Bug 30483: Make issues.borrowernumber and itemnumber NOT NULL

Note: This change does NOT apply to old_issues, where constraints
may result in nullifying these columns.

Test plan:
Run dbrev.
Try checkout, checkin.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Comment 10 Joonas Kylmälä 2022-08-28 14:49:46 UTC
Created attachment 139925 [details] [review]
Bug 30483: (DBIx schema) Changes to Issue.pm

Compare with results of update_dbix_class_files.pl.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Joonas Kylmälä <joonas.kylmala@iki.fi>
Comment 11 Tomás Cohen Arazi 2022-09-12 11:45:09 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 12 Lucas Gass 2022-10-31 20:35:13 UTC
Backported to 22.05.x for upcoming 22.05.07 release
Comment 13 Victor Grousset/tuxayo 2022-11-20 00:28:57 UTC
Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed.

Nothing to document it seems, marking resolved.
Comment 14 Marcel de Rooy 2022-12-05 09:58:43 UTC
Follow-up on bug 32399 to prevent failures.
Comment 15 Pablo López Liotti 2023-03-20 16:37:05 UTC
Hi, I'm upgrading a Koha 21.05 in Debian 9.5 to Koha 22.05.

After I do command line upgrade of koha-common, the intranet shows web install.
Here, in tables structure update, the process stop with this message:

Updating database structure
Update errors :

Upgrade to 22.05.06.001 [13:31:19]: 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

Now, what can I do?
Thks
Comment 16 Marcel de Rooy 2023-03-21 12:16:11 UTC
(In reply to Pablo López Liotti from comment #15)
> Hi, I'm upgrading a Koha 21.05 in Debian 9.5 to Koha 22.05.
> 
> After I do command line upgrade of koha-common, the intranet shows web
> install.
> Here, in tables structure update, the process stop with this message:
> 
> Updating database structure
> Update errors :
> 
> Upgrade to 22.05.06.001 [13:31:19]: 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
> 
> Now, what can I do?
> Thks

See bug 32399. There is a fix.
And it should have been pushed already to 22.05. But somehow it wasnt.