[Koha-bugs] [Bug 35338] New: Error in SQL syntax when upgrading to 22.06.00.084

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35338

            Bug ID: 35338
           Summary: Error in SQL syntax when upgrading to 22.06.00.084
 Change sponsored?: ---
           Product: Koha
           Version: master
          Hardware: All
                OS: All
            Status: NEW
          Severity: major
          Priority: P5 - low
         Component: Database
          Assignee: koha-bugs at lists.koha-community.org
          Reporter: magnus at libriotech.no
        QA Contact: testopia at bugs.koha-community.org

I have done a few upgrades to 22.11.x and 23.05.x now, but I have not seen this
error before: 

Upgrade to 22.06.00.084  [21:21:43]: Bug 31162 - Add primary key to
erm_eholdings_packages_agreements
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'CONSTRAINT
erm_eholdings_packages_agreements_uniq,
                ADD PRIMARY K' at line 3 at /usr/share/koha/lib/C4/Installer.pm
line 741

I can find that constraint in two places: 

* 220600079.pl:

        unless ( TableExists('erm_eholdings_packages_agreements') ) {
            $dbh->do(q{
                CREATE TABLE `erm_eholdings_packages_agreements` (
                    `package_id` INT(11) NOT NULL COMMENT 'link to the
package',
                    `agreement_id` INT(11) NOT NULL COMMENT 'link to the
agreement',
                    UNIQUE KEY `erm_eholdings_packages_agreements_uniq`
(`package_id`, `agreement_id`),
                    CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1`
FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
                    CONSTRAINT `erm_eholdings_packages_agreements_ibfk_2`
FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON
DELETE CASCADE ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
            });
            say $out "Added new table 'erm_eholdings_packages_agreements'";
        }

* 220600084.pl:

        unless ( primary_key_exists('erm_eholdings_packages_agreements') ){
            $dbh->do(q{
                ALTER TABLE erm_eholdings_packages_agreements
                DROP FOREIGN KEY erm_eholdings_packages_agreements_ibfk_1,
                DROP FOREIGN KEY erm_eholdings_packages_agreements_ibfk_2,
                DROP CONSTRAINT erm_eholdings_packages_agreements_uniq,
                ADD PRIMARY KEY(`package_id`, `agreement_id`)
            });
            $dbh->do(q{
                ALTER TABLE erm_eholdings_packages_agreements
                ADD CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1`
FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
                ADD CONSTRAINT `erm_eholdings_packages_agreements_ibfk_2`
FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON
DELETE CASCADE ON UPDATE CASCADE
            });
        }


Database version: MariaDB 5.7.34-0ubuntu0.18.04.1-log

OS: 
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.6 LTS
Release:        20.04
Codename:       focal

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.