Bug 18058

Summary: 'borrowers-force-messaging-defaults --doit --truncate ' gives DBI error
Product: Koha Reporter: Mason James <mtj>
Component: Command-line UtilitiesAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: normal    
Priority: P5 - low CC: jonathan.druart, julian.maurice, katrin.fischer, kyle, m.de.rooy, robin
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Bug 18058: Allow borrower_message_preferences to be truncated
[SIGNED-OFF] Bug 18058: Allow borrower_message_preferences to be truncated
Bug 18058: Allow borrower_message_preferences to be truncated

Description Mason James 2017-02-06 06:06:48 UTC
here's the error on a 16.05.09 pkg install

#  koha-shell -c ' perl /usr/share/koha/bin/maintenance/borrowers-force-messaging-defaults --doit --truncate ' demo
DBD::mysql::st execute failed: Cannot truncate a table referenced in a foreign key constraint (`koha_demo`.`borrower_message_transport_preferences`, CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `koha_demo`.`borrower) [for Statement "TRUNCATE borrower_message_preferences"] at /usr/share/koha/bin/maintenance/borrowers-force-messaging-defaults line 52.


note: i haven't confirmed this on newer versions, yet
Comment 1 Jonathan Druart 2017-02-14 14:43:00 UTC
Created attachment 60202 [details] [review]
Bug 18058: Allow borrower_message_preferences to be truncated

borrower_message_preferences cannot be truncated because of the foreign.
DBMS fails with
  "Cannot truncate a table referenced in a foreign key constraint"

To avoid that we should remove the FK check and truncate the other table
as well.

I am wondering if we really need a truncate here
  DELETE FROM borrower_message_preferences;
should do the job, but leave it as it because of the param name.

Test plan
  perl misc/maintenance/borrowers-force-messaging-defaults --doit --truncate
Should no longer raise the error message
Comment 2 Mark Tompsett 2017-02-20 18:45:23 UTC
Created attachment 60479 [details] [review]
[SIGNED-OFF] Bug 18058: Allow borrower_message_preferences to be truncated

borrower_message_preferences cannot be truncated because of the foreign.
DBMS fails with
  "Cannot truncate a table referenced in a foreign key constraint"

To avoid that we should remove the FK check and truncate the other table
as well.

I am wondering if we really need a truncate here
  DELETE FROM borrower_message_preferences;
should do the job, but leave it as it because of the param name.

Test plan
  perl misc/maintenance/borrowers-force-messaging-defaults --doit --truncate
Should no longer raise the error message

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Comment 3 Marcel de Rooy 2017-03-10 10:39:04 UTC
Created attachment 60985 [details] [review]
Bug 18058: Allow borrower_message_preferences to be truncated

borrower_message_preferences cannot be truncated because of the foreign.
DBMS fails with
  "Cannot truncate a table referenced in a foreign key constraint"

To avoid that we should remove the FK check and truncate the other table
as well.

I am wondering if we really need a truncate here
  DELETE FROM borrower_message_preferences;
should do the job, but leave it as it because of the param name.

Test plan
  perl misc/maintenance/borrowers-force-messaging-defaults --doit --truncate
Should no longer raise the error message

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 4 Marcel de Rooy 2017-03-10 10:40:08 UTC
Delete instead of truncate indeed seems to be a better idea here..
Comment 5 Kyle M Hall 2017-03-31 10:47:42 UTC
Pushed to master for 17.05, thanks Jonathan!
Comment 6 Katrin Fischer 2017-04-02 16:13:31 UTC
This patch has been pushed to 16.11.x and will be in 16.11.07.

Could not reproduce the error before the patch, but it seems better with it.
Comment 7 Julian Maurice 2017-04-21 09:27:39 UTC
Pushed to 3.22.x for 3.22.20
Comment 8 Mason James 2017-05-03 03:52:17 UTC
Pushed to 16.05.x, for 16.05.12 release