Bug 34428

Summary: Update database creates a wrong table transport_cost
Product: Koha Reporter: Didier Gautheron <didier.gautheron>
Component: DatabaseAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: indradg, jonathan.druart
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 23995    
Bug Blocks:    

Description Didier Gautheron 2023-07-27 06:56:57 UTC 
Hi,

it's using the initial definition from the first patch with :
CONSTRAINT `CONSTRAINT_1` CHECK (`frombranch` <> `tobranch`)

which never works and doesn't import in Maria db 10.6 


Regards
Didier,
Comment 1 Jonathan Druart 2023-09-01 14:55:31 UTC 
Bug 23995 removed it from kohastructure.sql but still exists in updatedatabase.pl
Comment 2 Katrin Fischer 2023-10-29 23:31:11 UTC 
(In reply to Jonathan Druart from comment #1)
> Bug 23995 removed it from kohastructure.sql but still exists in
> updatedatabase.pl

That's not great... right?

It's this update here, still in updatedatabase:

$DBversion = "3.09.00.037";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseTransportCostMatrix',0,'Use Transport Cost Matrix when filling holds','','YesNo')");

 $dbh->do("CREATE TABLE `transport_cost` (
              `frombranch` varchar(10) NOT NULL,
              `tobranch` varchar(10) NOT NULL,
              `cost` decimal(6,2) NOT NULL,
              `disable_transfer` tinyint(1) NOT NULL DEFAULT 0,
              CHECK ( `frombranch` <> `tobranch` ), -- a dud check, mysql does not support that
              PRIMARY KEY (`frombranch`, `tobranch`),
              CONSTRAINT `transport_cost_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `transport_cost_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8");

    print "Upgrade to $DBversion done (creating `transport_cost` table; adding UseTransportCostMatrix systempref, in circulation)\n";
    SetVersion($DBversion);
}

Should we fix and try to drop the CHECK in a database update?
Comment 3 Jonathan Druart 2023-10-31 10:33:14 UTC 
(In reply to Katrin Fischer from comment #2)
> That's not great... right?

Right.

> Should we fix and try to drop the CHECK in a database update?

Yes.