Bug 34428 - Update database creates a wrong table transport_cost
Summary: Update database creates a wrong table transport_cost
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 23995
Blocks:
  Show dependency treegraph
 
Reported: 2023-07-27 06:56 UTC by Didier Gautheron
Modified: 2024-02-18 11:58 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.