Bug 30539

Summary: Koha upgrade error "Column 'claimed_on' cannot be null"
Product: Koha Reporter: Saiful Amin <saiful>
Component: Installation and upgrade (command-line installer)Assignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: david, jonathan.druart, lucas, saiful
Version: Main   
Hardware: All   
OS: All   
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
Bug Depends on: 24161    
Bug Blocks:    
Attachments: Bug 30539: (bug 24161 follow-up) Fix typo in DBrev
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev
Bug 30539: (bug 24161 follow-up) Fix typo in DBrev

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 2022-06-07 12:37:38 UTC
Pushed to master for 22.11.

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