https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Robert Cooper <rcooper@inceptlabs.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|critical |minor Component|Installation and upgrade |Database |(web-based installer) | --- Comment #1 from Robert Cooper <rcooper@inceptlabs.com.au> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|minor |blocker -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #3 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 135877 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=135877&action=edit 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 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #4 from Keith Sorbo <keith@thesorbos.com> --- 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 ************** -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #6 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 135881 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=135881&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #8 from Keith Sorbo <keith@thesorbos.com> --- (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 are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #10 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- 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 } -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Harald <fechsaer@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fechsaer@gmail.com --- Comment #11 from Harald <fechsaer@gmail.com> --- The code in the first attachment from Jonathan Druart works for me. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #135877|0 |1 is obsolete| | Attachment #135880|0 |1 is obsolete| | --- Comment #12 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 135958 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=135958&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.

update systempreferences set value="21.1200015" where variable="version"; \q $ restart_all $ updatedatabase => SUCCESS: All good :-D

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

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #14 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 136024 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=136024&action=edit [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 3. Run: $ koha-mysql kohadev 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> -- You are receiving this mail because: You are watching all bug changes.

update systempreferences set value="21.1200015" where variable="version"; \q $ restart_all $ updatedatabase => SUCCESS: All good :-D

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

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #136028|0 |1 is obsolete| | --- Comment #16 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 136029 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=136029&action=edit 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 3. Run: $ koha-mysql kohadev 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> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 --- Comment #17 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 22.11. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|22.11.00 |22.11.00, 22.05.01 released in| | Status|Pushed to master |Pushed to stable --- Comment #18 from Lucas Gass <lucas@bywatersolutions.com> --- backported to 22.05.x for 22.05.01 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30912 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_22_05_candidate | -- You are receiving this mail because: You are watching all bug changes.