Summary: | Database update fails for 21.12.00.016 Bug 30060 | ||
---|---|---|---|
Product: | Koha | Reporter: | Robert Cooper <rcooper> |
Component: | Database | Assignee: | Jonathan Druart <jonathan.druart> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | blocker | ||
Priority: | P5 - low | CC: | fechsaer, fridolin.somers, gmcharlt, jonathan.druart, keith, lucas, mhby87, nick, tomascohen |
Version: | unspecified | ||
Hardware: | PC | ||
OS: | Linux | ||
See Also: | https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31026 | ||
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
|
|
Circulation function: | |||
Bug Depends on: | 30060 | ||
Bug Blocks: | |||
Attachments: |
Bug 30912: Fix db rev 21.12.00.016
Bug 30912: [alternate] Drop primary key only if exists Script to allow running a single update Bug 30912: Fix db rev 21.12.00.016 [ALTERNATE] Bug 30912: Fix db rev 21.12.00.016 Bug 30912: Fix db rev 21.12.00.016 Bug 30912: Fix db rev 21.12.00.016 |
Description
Robert Cooper
2022-06-06 23:56:49 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. *** 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 |