Summary: | Upgrade sometimes fails at "Upgrade to 21.11.05.004" | ||
---|---|---|---|
Product: | Koha | Reporter: | Magnus Enger <magnus> |
Component: | Circulation | Assignee: | Marcel de Rooy <m.de.rooy> |
Status: | CLOSED FIXED | QA Contact: | Fridolin Somers <fridolin.somers> |
Severity: | critical | ||
Priority: | P2 | CC: | arthur.suzuki, federicoantoniopaiz, fridolin.somers, gmcharlt, jonathan.druart, kyle.m.hall, lucas, m.de.rooy, syamsivanmail, tomascohen, vbovictor |
Version: | 24.11 | ||
Hardware: | All | ||
OS: | Linux | ||
See Also: | https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31108 | ||
Change sponsored?: | --- | Patch complexity: | Small patch |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: |
This database revision fixes the one from bug 30449 for table borrower_attribute_types.
|
Version(s) released in: |
22.11.00,22.05.01
|
Circulation function: | |||
Bug Depends on: | 30449 | ||
Bug Blocks: | |||
Attachments: |
Bug 30899: [MASTER 22.12] Correct dbrev 211200041
Bug 30899: [22.05.x] Follow-up for dbrev 211200041 from bug 30449 Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug 30449 Bug 30899: [MASTER 22.12] Correct dbrev 211200041 |
Description
Magnus Enger
2022-06-03 09:20:35 UTC
Looks like the problem is on line 11 of /usr/share/koha/intranet/cgi-bin/installer/data/mysql/db_revs/211105004.pl: 10 if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) { 11 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| ); 12 if( index_exists('borrower_attribute_types', 'category_code_fk') ) { 13 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| ); 14 } 15 } This is from a database where the update succeeded: MariaDB [koha_ok]> show create table borrower_attribute_types\G *************************** 1. row *************************** Table: borrower_attribute_types Create Table: CREATE TABLE `borrower_attribute_types` ( `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `repeatable` tinyint(1) NOT NULL DEFAULT '0', `unique_id` tinyint(1) NOT NULL DEFAULT '0', `opac_display` tinyint(1) NOT NULL DEFAULT '0', `opac_editable` tinyint(1) NOT NULL DEFAULT '0', `staff_searchable` tinyint(1) NOT NULL DEFAULT '0', `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `display_checkout` tinyint(1) NOT NULL DEFAULT '0', `category_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT '0', `mandatory` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`), KEY `category_code` (`category_code`), CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) And this is from a database where it failed: MariaDB [koha_fail]> show create table borrower_attribute_types\G *************************** 1. row *************************** Table: borrower_attribute_types Create Table: CREATE TABLE `borrower_attribute_types` ( `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `repeatable` tinyint(1) NOT NULL DEFAULT '0', `unique_id` tinyint(1) NOT NULL DEFAULT '0', `opac_display` tinyint(1) NOT NULL DEFAULT '0', `opac_editable` tinyint(1) NOT NULL DEFAULT '0', `staff_searchable` tinyint(1) NOT NULL DEFAULT '0', `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `display_checkout` tinyint(1) NOT NULL DEFAULT '0', `category_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT '0', `mandatory` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`), KEY `category_code_fk` (`category_code`), CONSTRAINT `category_code_fk` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) This is from the site that succeeded, before the upgrade (taken from last night's backup): CREATE TABLE `borrower_attribute_types` ( `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `repeatable` tinyint(1) NOT NULL DEFAULT '0', `unique_id` tinyint(1) NOT NULL DEFAULT '0', `opac_display` tinyint(1) NOT NULL DEFAULT '0', `opac_editable` tinyint(1) NOT NULL DEFAULT '0', `staff_searchable` tinyint(1) NOT NULL DEFAULT '0', `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `display_checkout` tinyint(1) NOT NULL DEFAULT '0', `category_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT '0', `mandatory` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`), KEY `category_code_fk` (`category_code`), CONSTRAINT `category_code_fk` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; And this is the same for the site that failed: CREATE TABLE `borrower_attribute_types` ( `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `repeatable` tinyint(1) NOT NULL DEFAULT '0', `unique_id` tinyint(1) NOT NULL DEFAULT '0', `opac_display` tinyint(1) NOT NULL DEFAULT '0', `opac_editable` tinyint(1) NOT NULL DEFAULT '0', `staff_searchable` tinyint(1) NOT NULL DEFAULT '0', `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `display_checkout` tinyint(1) NOT NULL DEFAULT '0', `category_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT '0', `mandatory` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; It's because you have a FK with the same name "category_code_fk" I see upgrade 3.07.00.034 adds : ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode) This is from Bug 7154 that did not add the constraint in kohastructure.sql The relevant code in the database update is: if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) { $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| ); ... } The database that succeeds had this before the upgrade: PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`), KEY `category_code_fk` (`category_code`), CONSTRAINT `category_code_fk` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) And the one that fails had: PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`) C4::Installer::foreign_key_exists looks like this 648 sub foreign_key_exists { 649 my ( $table_name, $constraint_name ) = @_; 650 my $dbh = C4::Context->dbh; 651 my (undef, $infos) = $dbh->selectrow_array(qq|SHOW CREATE TABLE $table_name|); 652 return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY|; 653 } Looks to me like the "if" above should prevent the "ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk" from being run against the database at all? (In reply to Magnus Enger from comment #1) > Looks like the problem is on line 11 of > /usr/share/koha/intranet/cgi-bin/installer/data/mysql/db_revs/211105004.pl: > > 10 if( foreign_key_exists('borrower_attribute_types', > 'category_code_fk') ) { > 11 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP > CONSTRAINT category_code_fk| ); > 12 if( index_exists('borrower_attribute_types', > 'category_code_fk') ) { > 13 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP > INDEX category_code_fk| ); > 14 } > 15 } Ahhh, the syntax is not : DROP CONSTRAINT category_code_fk it is : DROP FOREIGN KEY category_code_fk Both exists : https://mariadb.com/kb/en/alter-table/#drop-constraint https://mariadb.com/kb/en/alter-table/#drop-foreign-key But in all other existing DBRevs it is "DROP FOREIGN KEY". For example : https://git.koha-community.org/Koha-community/Koha/src/branch/master/installer/data/mysql/db_revs/210600013.pl#L12 If I change this line in the database update: $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| ); to this: $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| ); the update completes: $ sudo koha-upgrade-schema koha Upgrading database schema for koha Upgrade to 21.11.05.004 [12:19:49]: Bug 30449 - Check borrower_attribute_types FK constraint Upgrade to 21.11.05.005 [12:19:49]: Bug 30572 - Adjust search_marc_to_field.sort Upgrade to 21.11.06.000 [12:19:49]: Koha 21.11.06 release And after this the table looks like this: MariaDB [koha_esme]> SHOW CREATE TABLE borrower_attribute_types\G *************************** 1. row *************************** Table: borrower_attribute_types Create Table: CREATE TABLE `borrower_attribute_types` ( `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `repeatable` tinyint(1) NOT NULL DEFAULT '0', `unique_id` tinyint(1) NOT NULL DEFAULT '0', `opac_display` tinyint(1) NOT NULL DEFAULT '0', `opac_editable` tinyint(1) NOT NULL DEFAULT '0', `staff_searchable` tinyint(1) NOT NULL DEFAULT '0', `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `display_checkout` tinyint(1) NOT NULL DEFAULT '0', `category_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT '0', `mandatory` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`), KEY `category_code` (`category_code`), CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) As far as I can see, this is the same as for the database where the upgrade succeeded in the first place. Created attachment 135779 [details] [review] Bug 30899: [MASTER 22.12] Correct dbrev 211200041 Replace DROP CONSTRAINT by DROP FOREIGN KEY. Note: It seems that we do not need a new dbrev for master. A developer could follow test plan below to correct if really needed. Test plan: NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1. [1] cp installer/data/mysql/db_revs/211200041.pl installer/data/mysql/atomicupdate/ [2] run updatedatabase; check show create table [3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table [4] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table [5] rm installer/data/atomicupdate/211200041.pl Still coming patches for 22.05 and 21.11 Created attachment 135780 [details] [review] Bug 30899: [22.05.x] Follow-up for dbrev 211200041 from bug 30449 Replace DROP CONSTRAINT by DROP FOREIGN KEY in old dbrev, and submit a new dbrev to fix cases where dbrev went wrong. Test plan: NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1. [1] run updatedatabase; check show create table [2] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table [3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table Created attachment 135783 [details] [review] Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug 30449 Replace DROP CONSTRAINT by DROP FOREIGN KEY in old dbrev, and submit a new dbrev to fix cases where dbrev went wrong. Test plan: NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1. [1] run updatedatabase; check show create table [2] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table [3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table Jonathan: Do you want to check this one, or straight to Tomas? Magnus maybe, can you test this is working for you? (In reply to Jonathan Druart from comment #16) > Magnus maybe, can you test this is working for you? Comment10 confirms that more or less. Created attachment 135790 [details] [review] Bug 30899: [MASTER 22.12] Correct dbrev 211200041 Replace DROP CONSTRAINT by DROP FOREIGN KEY. Note: It seems that we do not need a new dbrev for master. A developer could follow test plan below to correct if really needed. Test plan: NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1. [1] cp installer/data/mysql/db_revs/211200041.pl installer/data/mysql/atomicupdate/ [2] run updatedatabase; check show create table [3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table [4] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table [5] rm installer/data/atomicupdate/211200041.pl Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com> @RM : remove [MASTER 22.12] when pushing Pushed to master for 22.11. Nice work everyone, thanks! Backported to 22.05.x for 22.05.01 Will the next updates fix this bug? Or do you need to do the correction manually? Complementing the information. I saw this error when updating to version 22.05.02.000 In ubuntu 20.05 and Koha 22.05.02.000 $ koha-upgrade-schema faes Upgrading database schema for faes DEV atomic update /usr/share/koha/intranet/cgi-bin/installer/data/mysql/atomicupdate/bug_30899.pl [22:09:37]: Bug 30899 - Check borrower_attribute_types FK constraint (30449 follow-up) (In reply to victor from comment #24) > In ubuntu 20.05 and Koha 22.05.02.000 > $ koha-upgrade-schema faes > Upgrading database schema for faes > DEV atomic update > /usr/share/koha/intranet/cgi-bin/installer/data/mysql/atomicupdate/bug_30899. > pl [22:09:37]: Bug 30899 - Check borrower_attribute_types FK constraint > (30449 follow-up) Nothing wrong here. Your DB schema is up-to-date. Lucas forgot to move a file from the atomicupdate directory to db_revs. Fixed in 22.05.03 by : e40fa1af65 Remove atomicupdate file *** Bug 31301 has been marked as a duplicate of this bug. *** Hello, we are having a problem adding a loan, it shows us this error in the logs DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'me.creation_date' in 'field list' at /usr/share/koha/lib/Koha/Objects.pm line 321: /usr/share/koha/intranet/cgi-bin/circ/circulation.pl (In reply to Federico Paiz from comment #28) > Hello, we are having a problem adding a loan, it shows us this error in the > logs > > DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st > execute failed: Unknown column 'me.creation_date' in 'field list' at > /usr/share/koha/lib/Koha/Objects.pm line 321: > /usr/share/koha/intranet/cgi-bin/circ/circulation.pl This does not seem to be related to this bug. Could you open a new bug or link to one matching your issue more closely? Thanks. Note: Please provide further details on that bug (not here) on Koha version, SQL version and other relevant info. Hola, estamos teniendo un problema para agregar un préstamo, nos muestra este error en los registros We move 20.11 to version 24.11 DBIx::Clasa::Tarjetivo::DBI::-dbh-ejecute(): DBI Excepción: DBD::mysql::st execute fall: Columna desconocida 'me.creation-date' en 'campo list' en /usr/share/koha/lib/Koha/Objectspm line 321: /usr/share/koha/intranet/cgi-bin/circulation.pl (In reply to Federico Paiz from comment #30) > Hola, estamos teniendo un problema para agregar un préstamo, nos muestra > este error en los registros > We move 20.11 to version 24.11 > > DBIx::Clasa::Tarjetivo::DBI::-dbh-ejecute(): DBI Excepción: DBD::mysql::st > execute fall: Columna desconocida 'me.creation-date' en 'campo list' en > /usr/share/koha/lib/Koha/Objectspm line 321: > /usr/share/koha/intranet/cgi-bin/circulation.pl Hi, please try to ask on the Mattermost channel (chat.koha-community.org) or on the Spanish speaking mailing list instead. This sounds like a configuration/update issue and this bug has been long closed. |