There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1'
Created attachment 160461 [details] [review] Bug 35687 - Upgrade to 23.06.00.013 may fail There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' Test Plan: 1) Upgrade to post 23.06.00.013 2) Note the failure 3) Apply this patch set 4) Run updatedatabase.pl 5) Update should succeed!
Shouldn't this be done before the problematic DB rev?
(In reply to Jonathan Druart from comment #2) > Shouldn't this be done before the problematic DB rev? If someone has already upgraded past that db rev this one will fix it. It's a good idea to fix the existing rev as well though. I'll post a followup.
Created attachment 160462 [details] [review] Bug 35687: Fix db rev for 23.06.00.013
I'd like to test, but I'm not sure of the exact steps for step 1 of the test plan (using KTD). This is what I tried, but I couldn't trigger the error: 1. Koha repository - checkout 23.05.x: git checkout 23.05.x 2. KTD: start up as normal (I didn't change the KOHA_IMAGE in .env) 3. From the KTD shell check out 23.11.x: ktd --shell git checkout 23.11.x 4. Restart everything: restart_all 5. Go to the staff interface 6. Upgrade starts - Use koha_kohadev as the user and password as the password - Grant more privileges to the koha_kohadev user: . mysql -uroot -ppassword -hkoha-db-1 . use mysql . grant all on koha_kohadev.* to koha_kohadev; . flush privileges; 7. No errors when updating the database structure I also tried using an earlier version: 1. Koha repository - checkout 22.11.x: git checkout 22.11.x 2. KTD - change KOHA_IMAGE in .env: . edit .env: KOHA_IMAGE=22.11 . ktd pull . ktd up ... same as above - step 3 onwards Do I have to manually alter the database tables so the conditions for the error to occur are in place before the upgrade? Happy to try again, otherwise I will leave to someone who is more familiar with how to work with different versions of Koha using KTD and doing upgrades.
*** Bug 35552 has been marked as a duplicate of this bug. ***
(In reply to David Nind from comment #5) > I'd like to test, but I'm not sure of the exact steps for step 1 of the test > plan (using KTD). > > This is what I tried, but I couldn't trigger the error: Same for me. Do we know in which context the problem appears?
FYI: I answered a query on Facebook about this bug - they were able to successfully fix it. Just a "normal" upgrade, I think. https://www.facebook.com/groups/6381238597/permalink/10161615713058598/?mibextid=oMANbw
Hi David and Jonathan, Feel free to look at Bug 35552 for additional details on how I ran into this issue when I initially reported it. Victor
Hi Jonathan, To your question on 1/11/2024: Can you report on bug 35687 which version of MySQL/MariaDB you are using please? I am using MySQL version 8.0.35 on Ubuntu 22.02.1. Victor
*Ubuntu 22.04.1
I still do not recreate. Server version: 8.0.35 MySQL Community Server - GPL MySQL [koha_kohadev]> show create table tmp_holdsqueue\G *************************** 1. row *************************** Table: tmp_holdsqueue Create Table: CREATE TABLE `tmp_holdsqueue` ( `biblionumber` int DEFAULT NULL, `itemnumber` int DEFAULT NULL, `barcode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `surname` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `firstname` mediumtext COLLATE utf8mb4_unicode_ci, `phone` mediumtext COLLATE utf8mb4_unicode_ci, `borrowernumber` int NOT NULL, `cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `reservedate` date DEFAULT NULL, `title` longtext COLLATE utf8mb4_unicode_ci, `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `pickbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` mediumtext COLLATE utf8mb4_unicode_ci, `item_level_request` tinyint NOT NULL DEFAULT '0', `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'date and time this entry as added/last updated', KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`), KEY `tmp_holdsqueue_ibfk_2` (`biblionumber`), KEY `tmp_holdsqueue_ibfk_3` (`borrowernumber`), CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tmp_holdsqueue_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tmp_holdsqueue_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci MySQL [koha_kohadev]> ALTER TABLE tmp_holdsqueue ADD PRIMARY KEY (itemnumber); Query OK, 0 rows affected (0.131 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [koha_kohadev]> show create table tmp_holdsqueue\G *************************** 1. row *************************** Table: tmp_holdsqueue Create Table: CREATE TABLE `tmp_holdsqueue` ( `biblionumber` int DEFAULT NULL, `itemnumber` int NOT NULL, `barcode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `surname` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `firstname` mediumtext COLLATE utf8mb4_unicode_ci, `phone` mediumtext COLLATE utf8mb4_unicode_ci, `borrowernumber` int NOT NULL, `cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `reservedate` date DEFAULT NULL, `title` longtext COLLATE utf8mb4_unicode_ci, `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `pickbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` mediumtext COLLATE utf8mb4_unicode_ci, `item_level_request` tinyint NOT NULL DEFAULT '0', `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'date and time this entry as added/last updated', PRIMARY KEY (`itemnumber`), KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`), KEY `tmp_holdsqueue_ibfk_2` (`biblionumber`), KEY `tmp_holdsqueue_ibfk_3` (`borrowernumber`), CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tmp_holdsqueue_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tmp_holdsqueue_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.001 sec)
I can't recreate either...but I wonder if it has something to do with the sql_mode? I can't seem to find or remember where it's configured, but if I recall correctly, the dev environment is set to strict mode, and production environments usually aren't, right? This page https://bugs.mysql.com/bug.php?id=93838 indicates that trying to make a nullable column non-nullable will actually succeed in strict mode but throw a "cannot change column used in foreign key constraint" in non-strict mode. (The logic being that, in non-strict mode, null values will be filled with a default value that may violate the foreign key constraint, whereas in strict mode the command will not be allowed to execute if there are any existing null values in the data.) Maybe adding a primary key follows the same logic, and will fail only in non-strict mode?
Look at bad638578b6bb3d0913841bd71ab9fba220a529e We've been facing this for a while.
(In reply to Tomás Cohen Arazi from comment #14) > Look at bad638578b6bb3d0913841bd71ab9fba220a529e > > We've been facing this for a while. Can we add a coding guideline for this? Something that's causing this many issues ought to be documented somewhere.
Created attachment 161141 [details] [review] Bug 35687 - Upgrade to 23.06.00.013 may fail There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' Test Plan: 1) Upgrade to post 23.06.00.013 2) Note the failure 3) Apply this patch set 4) Run updatedatabase.pl 5) Update should succeed! Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Created attachment 161142 [details] [review] Bug 35687: Fix db rev for 23.06.00.013 Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Testing notes: I replicated the failure with the following configuration: - git checkout v23.05.01 - Start KTD using MySQL 8 - Open a kshell, and edit /etc/koha/sites/kohadev/koha-conf.xml. Set <strict-sql-modes> to 0. - git switch 23.11.x - restart_all - updatedatabase Database update succeeded after applying the patch - signing off :)
> - Start KTD using MySQL 8 How does one do that? Just changing the DB image leads to failure due to default MySQL having changed and adding docker-compose.mysql8.0.yml causes a conflict with the default mariadb stuff already present the default compose file. More details: https://gitlab.com/koha-community/koha-testing-docker/-/issues/411
(In reply to Victor Grousset/tuxayo from comment #19) > > - Start KTD using MySQL 8 > > How does one do that? Just changing the DB image leads to failure due to > default MySQL having changed and adding docker-compose.mysql8.0.yml causes a > conflict with the default mariadb stuff already present the default compose > file. More details: > https://gitlab.com/koha-community/koha-testing-docker/-/issues/411 This how I start KTD with MySQL instead of the default MariaDB: 1. Edit your .env file and change DB_IMAGE=mysql:8.0 2. Run: ktd pull Result: koha-mysql kohadev will show the database server running as Server version: 8.0.36 MySQL Community Server - GPL
Looking here
Created attachment 161179 [details] [review] Bug 35687 - Upgrade to 23.06.00.013 may fail There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' Test Plan: 1) Upgrade to post 23.06.00.013 2) Note the failure 3) Apply this patch set 4) Run updatedatabase.pl 5) Update should succeed! Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Created attachment 161180 [details] [review] Bug 35687: Fix db rev for 23.06.00.013 Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Hmm ignoring: * Commit title does not start with 'Bug XXXXX: ' - 613004bd82
Created attachment 161221 [details] [review] Bug 35687: Upgrade to 23.06.00.013 may fail There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' Test Plan: 1) Upgrade to post 23.06.00.013 2) Note the failure 3) Apply this patch set 4) Run updatedatabase.pl 5) Update should succeed! Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
(In reply to Marcel de Rooy from comment #24) > Hmm ignoring: > * Commit title does not start with 'Bug XXXXX: ' - 613004bd82 It was just "Bug 35687 - " vs "Bug 35687: " --- Thanks Emily and Marcel for the testing :)
Pushed for 24.05! Well done everyone, thank you!
Pushed to 23.11.x for 23.11.02
Missing 23.05.x dependencies. No backport.