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
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.
*** Bug 30932 has been marked as a duplicate of this bug. ***
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
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 **************
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
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
(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)
(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
(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`;
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 }
The code in the first attachment from Jonathan Druart works for me.
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>
(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
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>
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>
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>
Pushed to master for 22.11. Nice work everyone, thanks!
backported to 22.05.x for 22.05.01