Bug 35687 - Upgrade to 23.06.00.013 may fail
Summary: Upgrade to 23.06.00.013 may fail
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low blocker (vote)
Assignee: Kyle M Hall
QA Contact: Marcel de Rooy
URL:
Keywords:
: 35552 (view as bug list)
Depends on: 28966
Blocks:
  Show dependency treegraph
 
Reported: 2024-01-03 14:05 UTC by Kyle M Hall
Modified: 2024-03-20 12:08 UTC (History)
10 users (show)

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


Attachments
Bug 35687 - Upgrade to 23.06.00.013 may fail (1.83 KB, patch)
2024-01-03 14:09 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 35687: Fix db rev for 23.06.00.013 (1.26 KB, patch)
2024-01-03 14:32 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 35687 - Upgrade to 23.06.00.013 may fail (1.90 KB, patch)
2024-01-18 16:06 UTC, Emily Lamancusa
Details | Diff | Splinter Review
Bug 35687: Fix db rev for 23.06.00.013 (1.32 KB, patch)
2024-01-18 16:06 UTC, Emily Lamancusa
Details | Diff | Splinter Review
Bug 35687 - Upgrade to 23.06.00.013 may fail (2.00 KB, patch)
2024-01-19 10:07 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 35687: Fix db rev for 23.06.00.013 (1.42 KB, patch)
2024-01-19 10:07 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 35687: Upgrade to 23.06.00.013 may fail (1.96 KB, patch)
2024-01-19 19:57 UTC, Victor Grousset/tuxayo
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Kyle M Hall 2024-01-03 14:05:44 UTC
There are reports that this update triggers the error Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1'
Comment 1 Kyle M Hall 2024-01-03 14:09:02 UTC
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!
Comment 2 Jonathan Druart 2024-01-03 14:27:55 UTC
Shouldn't this be done before the problematic DB rev?
Comment 3 Kyle M Hall 2024-01-03 14:28:54 UTC
(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.
Comment 4 Kyle M Hall 2024-01-03 14:32:18 UTC
Created attachment 160462 [details] [review]
Bug 35687: Fix db rev for 23.06.00.013
Comment 5 David Nind 2024-01-05 19:19:04 UTC
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.
Comment 6 Jonathan Druart 2024-01-11 14:21:19 UTC
*** Bug 35552 has been marked as a duplicate of this bug. ***
Comment 7 Jonathan Druart 2024-01-11 14:28:07 UTC
(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?
Comment 8 David Nind 2024-01-11 21:26:29 UTC
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
Comment 9 Victor Zuniga 2024-01-15 20:38:18 UTC
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
Comment 10 Victor Zuniga 2024-01-15 21:14:42 UTC
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
Comment 11 Victor Zuniga 2024-01-15 21:15:43 UTC
*Ubuntu 22.04.1
Comment 12 Jonathan Druart 2024-01-16 09:28:57 UTC
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)
Comment 13 Emily Lamancusa 2024-01-17 22:08:16 UTC
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?
Comment 14 Tomás Cohen Arazi 2024-01-18 11:52:01 UTC
Look at bad638578b6bb3d0913841bd71ab9fba220a529e

We've been facing this for a while.
Comment 15 Emily Lamancusa 2024-01-18 15:36:52 UTC
(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.
Comment 16 Emily Lamancusa 2024-01-18 16:06:44 UTC
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>
Comment 17 Emily Lamancusa 2024-01-18 16:06:47 UTC
Created attachment 161142 [details] [review]
Bug 35687: Fix db rev for 23.06.00.013

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Comment 18 Emily Lamancusa 2024-01-18 16:12:01 UTC
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 :)
Comment 19 Victor Grousset/tuxayo 2024-01-19 01:54:15 UTC
> - 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
Comment 20 David Nind 2024-01-19 04:56:16 UTC
(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
Comment 21 Marcel de Rooy 2024-01-19 10:00:22 UTC
Looking here
Comment 22 Marcel de Rooy 2024-01-19 10:07:13 UTC
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>
Comment 23 Marcel de Rooy 2024-01-19 10:07:16 UTC
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>
Comment 24 Marcel de Rooy 2024-01-19 10:07:49 UTC
Hmm ignoring:
        * Commit title does not start with 'Bug XXXXX: ' - 613004bd82
Comment 25 Victor Grousset/tuxayo 2024-01-19 19:57:00 UTC
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>
Comment 26 Victor Grousset/tuxayo 2024-01-19 19:59:29 UTC
(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 :)
Comment 27 Katrin Fischer 2024-01-26 14:17:55 UTC
Pushed for 24.05!

Well done everyone, thank you!
Comment 28 Fridolin Somers 2024-01-29 09:14:20 UTC
Pushed to 23.11.x for 23.11.02
Comment 29 Lucas Gass 2024-02-02 18:03:48 UTC
Missing 23.05.x dependencies. No backport.