Bug 30912 - Database update fails for 21.12.00.016 Bug 30060
Summary: Database update fails for 21.12.00.016 Bug 30060
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: unspecified
Hardware: PC Linux
: P5 - low blocker (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 30932 (view as bug list)
Depends on: 30060
Blocks:
  Show dependency treegraph
 
Reported: 2022-06-06 23:56 UTC by Robert Cooper
Modified: 2023-06-08 22:27 UTC (History)
9 users (show)

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


Attachments
Bug 30912: Fix db rev 21.12.00.016 (2.15 KB, patch)
2022-06-09 11:44 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 30912: [alternate] Drop primary key only if exists (2.01 KB, patch)
2022-06-09 12:58 UTC, Nick Clemens
Details | Diff | Splinter Review
Script to allow running a single update (567 bytes, application/x-perl)
2022-06-09 13:00 UTC, Nick Clemens
Details
Bug 30912: Fix db rev 21.12.00.016 (2.25 KB, patch)
2022-06-10 19:57 UTC, Nick Clemens
Details | Diff | Splinter Review
[ALTERNATE] Bug 30912: Fix db rev 21.12.00.016 (2.70 KB, patch)
2022-06-13 13:23 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 30912: Fix db rev 21.12.00.016 (2.69 KB, patch)
2022-06-13 13:46 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 30912: Fix db rev 21.12.00.016 (2.74 KB, patch)
2022-06-13 13:47 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Cooper 2022-06-06 23:56:49 UTC
This is my first time bug reporting, so please let me know if I'm missing details.

Upgrading to 22.05 from 21.12.00.015.

OS - Ubuntu v 20.04.4 LTS
MySQL Server v 8.0

The koha-common package update installed through Ubuntu apt-get 

During the Web installer phase, the following error message is generated:

Upgrade to 21.12.00.016 [09:03:01]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column
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 'IF EXISTS `PRIMARY`' at line 1 at /usr/share/koha/lib/C4/Installer.pm line 739
Comment 1 Robert Cooper 2022-06-08 00:30:08 UTC
Digging into this further, I found that the following was causing an error in MySql v8 server:

ALTER TABLE user_permissions DROP INDEX IF EXISTS `PRIMARY`;

I have manually run the changes in the db fix file and the rest of the updates using koha-upgrade-schema utility proceeded normally. I have updated the details in my bug report, but I'm not sure what the process is for fixing this further.
Comment 2 Jonathan Druart 2022-06-09 09:59:50 UTC
*** Bug 30932 has been marked as a duplicate of this bug. ***
Comment 3 Jonathan Druart 2022-06-09 11:44:30 UTC
Created attachment 135877 [details] [review]
Bug 30912: Fix db rev 21.12.00.016

Looks like MySQL v8 does not support the IF EXISTS syntax to delete
primary key (to confirmed)

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 'IF EXISTS `PRIMARY`' at line 1 at /usr/share/koha/lib/C4/Installer.pm line 739

Test plan:
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase

ALTER TABLE user_permissions DROP PRIMARY KEY;
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase
Comment 4 Keith Sorbo 2022-06-09 11:58:28 UTC

Where should I run the test plan?
It looks like the test plan has sql commands. Do I run in the mysql command line? 
I assume the restart_all and updatedatabase commands are macros created by koha?

(Please excuse my ignorance in the koha system.)

***************
Test plan:
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase

ALTER TABLE user_permissions DROP PRIMARY KEY;
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase
**************
Comment 5 Nick Clemens 2022-06-09 12:58:58 UTC
Created attachment 135880 [details] [review]
Bug 30912: [alternate] Drop primary key only if exists

To test:
1 - Have koha running with MySQL 8
2 - sudo koha-mysql kohadev
3 - To confirm current indexes:
    SHOW INDEXES FROM user_permissions WHERE Key_name = 'PRIMARY';
4 - Drop them to ensure the update will run
    ALTER TABLE user_permissions DROP INDEX `PRIMARY`;
5 - Run the DB update (using attached script) and ensure it works when there is no key
6 - Drop the key again
   ALTER TABLE user_permissions DROP INDEX `PRIMARY`;
7 - Create new index that will still trigger update
   ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit);
8 - Run update again and ensure it works when there is a key
Comment 6 Nick Clemens 2022-06-09 13:00:16 UTC
Created attachment 135881 [details]
Script to allow running a single update

Provide the file name of the update you wish to run in the update option.

e.g.

perl run_single_update.pl --update 211200016.pl
Comment 7 Nick Clemens 2022-06-09 13:02:31 UTC
(In reply to Keith Sorbo from comment #4)
> 
> Where should I run the test plan?
> It looks like the test plan has sql commands. Do I run in the mysql command
> line? 
> I assume the restart_all and updatedatabase commands are macros created by
> koha?
> 
> (Please excuse my ignorance in the koha system.)
> 
> ***************
> Test plan:
> update systempreferences set value="21.1200015" where variable="version";
> restart_all
> updatedatabase
> 
> ALTER TABLE user_permissions DROP PRIMARY KEY;
> update systempreferences set value="21.1200015" where variable="version";
> restart_all
> updatedatabase
> **************

To run the SQL you should enter mysql like:
sudo koha-mysql kohadev

Where "kohadev" is your instance name

You can 'sudo koha-list' to confirm instance name(s)
Comment 8 Keith Sorbo 2022-06-10 02:12:13 UTC
(In reply to Nick Clemens from comment #6)
> Created attachment 135881 [details]
> Script to allow running a single update
> 
> Provide the file name of the update you wish to run in the update option.
> 
> e.g.
> 
> perl run_single_update.pl --update 211200016.pl

(Again, please forgive my koha ignorance!)

What folder do I run this from? 
I tried from /usr/share/koha/

and get Can't locate C4/Context.pm in @INC
Comment 9 Jonathan Druart 2022-06-10 06:56:26 UTC
(In reply to Keith Sorbo from comment #8)
> (In reply to Nick Clemens from comment #6)
> > Created attachment 135881 [details]
> > Script to allow running a single update
> > 
> > Provide the file name of the update you wish to run in the update option.
> > 
> > e.g.
> > 
> > perl run_single_update.pl --update 211200016.pl
> 
> (Again, please forgive my koha ignorance!)
> 
> What folder do I run this from? 
> I tried from /usr/share/koha/
> 
> and get Can't locate C4/Context.pm in @INC

You need a MySQL CLI then run:
  ALTER TABLE user_permissions DROP INDEX `PRIMARY`;
Comment 10 Jonathan Druart 2022-06-10 06:59:47 UTC
Nick, don't we actually only need that?

 14             if ( primary_key_exists ('user_permissions') {
 15                 $dbh->do(q{
 16                     ALTER TABLE user_permissions DROP INDEX `PRIMARY`                                 
 17                 }) 
 18                 say $out "Dropped any previously created primary key";                                
 19             }
Comment 11 Harald 2022-06-10 07:44:40 UTC
The code in the first attachment from Jonathan Druart works for me.
Comment 12 Nick Clemens 2022-06-10 19:57:32 UTC
Created attachment 135958 [details] [review]
Bug 30912: Fix db rev 21.12.00.016

Looks like MySQL v8 does not support the IF EXISTS syntax to delete
primary key (to confirmed)

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 'IF EXISTS `PRIMARY`' at line 1 at /usr/share/koha/lib/C4/Installer.pm line 739

Test plan:
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase

ALTER TABLE user_permissions DROP PRIMARY KEY;
update systempreferences set value="21.1200015" where variable="version";
restart_all
updatedatabase

Signed-off-by: Harald <fechsaer@gmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 13 Nick Clemens 2022-06-10 19:58:46 UTC
(In reply to Jonathan Druart from comment #10)
> Nick, don't we actually only need that?
> 
>  14             if ( primary_key_exists ('user_permissions') {

That's probably true, not sure why I checked for each? I suppose if you had the three and an additional?  Anyways, original patch works, was approved, let's not hold this up
Comment 14 Tomás Cohen Arazi 2022-06-13 13:23:14 UTC
Created attachment 136024 [details] [review]
[ALTERNATE] Bug 30912: Fix db rev 21.12.00.016

This is a mix of Nick's patch and Jonathan's comment on it. I tested it
with KTD using MySQL 8 and it works correctly.

To test:
1. Launch KTD with bells and whistles:
   $ docker compose -f docker-compose.yml \
                    -f docker-compose.mysql8.0.yml \
                    up -d
2. Inside of it, do:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > \q
   $ restart_all
   $ updatedatabase
=> SUCCESS: All good :-D
3. Run:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > ALTER TABLE user_permissions DROP PRIMARY KEY;
   > \q
   $ updatedatabase
=> FAIL: You get:
Upgrade to 21.12.00.016  [12:47:09]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column
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 'IF EXISTS `PRIMARY`' at line 1 at /kohadevbox/koha/C4/Installer.pm line 739
4. Apply this patch
5. Run:
   $ updatedatabase
=> SUCCESS: Update goes well
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 15 Nick Clemens 2022-06-13 13:46:32 UTC
Created attachment 136028 [details] [review]
Bug 30912: Fix db rev 21.12.00.016

This is a mix of Nick's patch and Jonathan's comment on it. I tested it
with KTD using MySQL 8 and it works correctly.

To test:
1. Launch KTD with bells and whistles:
   $ docker compose -f docker-compose.yml \
                    -f docker-compose.mysql8.0.yml \
                    up -d
2. Inside of it, do:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > \q
   $ restart_all
   $ updatedatabase
=> SUCCESS: All good :-D
3. Run:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > ALTER TABLE user_permissions DROP PRIMARY KEY;
   > \q
   $ updatedatabase
=> FAIL: You get:
Upgrade to 21.12.00.016  [12:47:09]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column
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 'IF EXISTS `PRIMARY`' at line 1 at /kohadevbox/koha/C4/Installer.pm line 739
4. Apply this patch
5. Run:
   $ updatedatabase
=> SUCCESS: Update goes well
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 16 Nick Clemens 2022-06-13 13:47:57 UTC
Created attachment 136029 [details] [review]
Bug 30912: Fix db rev 21.12.00.016

This is a mix of Nick's patch and Jonathan's comment on it. I tested it
with KTD using MySQL 8 and it works correctly.

To test:
1. Launch KTD with bells and whistles:
   $ docker compose -f docker-compose.yml \
                    -f docker-compose.mysql8.0.yml \
                    up -d
2. Inside of it, do:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > \q
   $ restart_all
   $ updatedatabase
=> SUCCESS: All good :-D
3. Run:
   $ koha-mysql kohadev
   > update systempreferences set value="21.1200015" where variable="version";
   > ALTER TABLE user_permissions DROP PRIMARY KEY;
   > \q
   $ updatedatabase
=> FAIL: You get:
Upgrade to 21.12.00.016  [12:47:09]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column
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 'IF EXISTS `PRIMARY`' at line 1 at /kohadevbox/koha/C4/Installer.pm line 739
4. Apply this patch
5. Run:
   $ updatedatabase
=> SUCCESS: Update goes well
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 17 Tomás Cohen Arazi 2022-06-13 14:10:11 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 18 Lucas Gass 2022-06-24 15:41:24 UTC
backported to 22.05.x for 22.05.01