Bug 30472

Summary:	Field borrower_relationships.guarantor_id should be marked as NOT NULL
Product:	Koha	Reporter:	Marcel de Rooy <m.de.rooy>
Component:	Database	Assignee:	Marcel de Rooy <m.de.rooy>
Status:	CLOSED FIXED	QA Contact:	Testopia <testopia>
Severity:	trivial
Priority:	P5 - low	CC:	fridolin.somers, m+koha, pablo.bianchi
Version:	Main
Hardware:	All
OS:	All
Change sponsored?:	---	Patch complexity:	Trivial patch
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:	22.11.00
Circulation function:
Bug Depends on:	14570
Bug Blocks:	30486, 33671
Attachments:	Bug 30472: Make borrower_relationships.guarantor_id not null Bug 30472: Make borrower_relationships.guarantor_id not null

Description Marcel de Rooy 2022-04-06 14:05:59 UTC

See also $DBversion = '19.06.00.022'; CREATE TABLE `borrower_relationships` [...]                   guarantor_id INT(11) NOT NULL,                   guarantee_id INT(11) NOT NULL,

Many production db's will have both fields already NOT NULL.
We need to adjust kohastructure and DBIx

Note that non-patron guarantors are NOT saved in this table but in the borrowers table itself IIUC.

Comment 1 Marcel de Rooy 2022-08-24 11:40:50 UTC

Created attachment 139711 [details] [review]
Bug 30472: Make borrower_relationships.guarantor_id not null

In harmony with dbrev 19.06.00.022.

Test plan:
Run dbrev.
Run t/db_dependent/Patron/Relationships.t
Run t/db_dependent/Koha/Patrons.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>

Comment 2 Marcel de Rooy 2022-08-24 11:41:37 UTC

Trivial, architectural (db admin). Self signoff.

Comment 3 Marcel de Rooy 2022-08-24 11:42:44 UTC

RM: Take care of DBIx please.

Comment 4 Fridolin Somers 2022-08-30 06:19:22 UTC

Created attachment 139972 [details] [review]
Bug 30472: Make borrower_relationships.guarantor_id not null

In harmony with dbrev 19.06.00.022.

Test plan:
Run dbrev.
Run t/db_dependent/Patron/Relationships.t
Run t/db_dependent/Koha/Patrons.t

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>

Comment 5 Fridolin Somers 2022-08-30 06:21:08 UTC

Makes perfect sens.
Generated DBIx looks OK.

Comment 6 Marcel de Rooy 2022-08-30 06:36:40 UTC

(In reply to Fridolin Somers from comment #4)
> Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>

Thanks.

Comment 7 Tomás Cohen Arazi (tcohen) 2022-09-16 20:01:12 UTC

Pushed to master for 22.11.

Nice work everyone, thanks!

Comment 8 Pablo AB 2023-02-08 21:47:53 UTC

Upgrading to 22.11.02-1 from an updated 22.05 on a Debian 11:

Upgrade to 22.06.00.048  [18:33:27]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 739

Maybe the same issue as on bug 32399?

Comment 9 Mona Panchaud 2023-05-03 18:31:17 UTC

Hello.

While upgrading from 22.05 to 22.11 we are running into the following problem that is related to this change:

"Upgrade to 22.06.00.048 [20:19:53]: Bug 30472 - borrower_relationships.guarantor_id NOT NULL
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'guarantor_id': used in a foreign key constraint 'r_guarantor' at /usr/share/koha/lib/C4/Installer.pm line 743"

Is there any workaround?
What can we do to get the upgrade working?

Comment 10 Katrin Fischer 2023-05-03 21:51:11 UTC

Hi Mona, hi Pablo,

I've moved the problem into a new bug: bug 33671.

What you can do: could you check if you have NULL values in borrower_relationships.guarantor_id?

Also, which DBMS are you using and what is the version? (MariaDB or MySQL, check the about page in Koha)

Please add info to bug 33671. Thx!