Bug 30539 - Koha upgrade error "Column 'claimed_on' cannot be null"
Summary: Koha upgrade error "Column 'claimed_on' cannot be null"
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: Main
Hardware: All All
: P5 - low major
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 24161
Blocks:
  Show dependency tree / graph
 
Reported: 2022-04-14 07:04 UTC by Saiful Amin
Modified: 2023-12-28 20:43 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This fixes an upgrade error that could result in data loss when upgrading from earlier releases to 20.05 (and later releases). It results in the claim_dates for orders being replaced with the date the upgrade was run. (This was caused by an error in the database update for bug 24161 - Add ability to track the claim dates of later orders.)
Version(s) released in:
22.11.00, 22.05.03
Circulation function:

Attachments
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev (938 bytes, patch)
2022-05-31 05:59 UTC, Jonathan Druart 		Details | Diff | Splinter Review
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev (984 bytes, patch)
2022-05-31 18:48 UTC, David Nind 		Details | Diff | Splinter Review
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev (1.02 KB, patch)
2022-06-06 20:41 UTC, Katrin Fischer 		Details | Diff | Splinter Review
Show Obsolete (2) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Saiful Amin 2022-04-14 07:04:16 UTC 
Running koha-upgrade-schema for migration (from 17.x to 21.x) encountered this error which breaks the upgrade during '19.12.00.085':

{UNKNOWN}: DBI Exception: DBD::mysql::st execute failed: Column 'claimed_on' cannot be null  at /usr/share/perl5/DBIx/C8.
 ...[snip]...
  DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::st execute failed: Column 'claimed_on' cannot be "..., DBI::stundef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 22065
Comment 1 Saiful Amin 2022-04-14 07:18:33 UTC 
There is a possible bug in:
/usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl
(OR [master]/installer/data/mysql/updatedatabase.pl)

While checking the code I observed that the orders object has three values and 'claimed_on' is not one of them:

22053   my $orders = $dbh->selectall_arrayref(q|
22054       SELECT ordernumber, claims_count, claimed_date
22055       FROM aqorders


So, the possible patch is making following change in the line 22065 of the updatedatabase.pl:

22063   for my $order ( @$orders ) {
22064       for my $claim (1..$order->{claims_count}) {
-22065           $insert_claim_sth->execute($order->{ordernumber}, $order->{claimed_on});
+22065           $insert_claim_sth->execute($order->{ordernumber}, $order->{claimed_date});
22066       }
22067   }

The above change fixed the upgrade process for us.
Comment 2 Jonathan Druart 2022-05-31 05:59:48 UTC 
Created attachment 135464 [details] [review]
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev
Comment 3 Jonathan Druart 2022-05-31 06:00:56 UTC 
As the upgrade process crashes we should be safe and not need to update the existing data. However I am wondering why this hasn't been reported earlier.
Comment 4 David Nind 2022-05-31 18:48:10 UTC 
Created attachment 135516 [details] [review]
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev

Signed-off-by: David Nind <david@davidnind.com>
Comment 5 David Nind 2022-05-31 18:51:37 UTC 
Testing notes (using koha-testing-docker):

- I haven't tested an upgrade from 17.x to 21.x.

- I have reviewed the patch and comments - the change resolved the upgrade issue experienced, comments on the original bug. 

Feel free to change status back to needs sign off, or provide some feedback on how to test.
Comment 6 Katrin Fischer 2022-06-06 20:41:21 UTC 
Created attachment 135735 [details] [review]
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 7 Katrin Fischer 2022-06-06 20:42:31 UTC 
This is a data loss bug too. We either didn't notice or didn't have the error, but it results in the claim_dates being replaced with today's date on migration to the new table.

Please backport to all necessary releases.
Comment 8 Tomás Cohen Arazi (tcohen) 2022-06-07 12:37:38 UTC 
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 9 Lucas Gass (lukeg) 2022-07-12 16:23:11 UTC 
Backported to 22.05.x for 22.05.03