Bug 22128 - koha-remove fails mysql ERROR 1133 (42000) at line 2: Can't find any matching row in the user table
Summary: koha-remove fails mysql ERROR 1133 (42000) at line 2: Can't find any matching...
Status: Pushed to stable
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: 18.05
Hardware: All All
: P5 - low normal (vote)
Assignee: Rudolf Byker
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-01-14 11:48 UTC by Heinrich Hartl
Modified: 2019-07-25 21:53 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.03


Attachments
Use "DROP USER IF EXISTS" instead of "GRANT USAGE" before "DROP USER". (795 bytes, patch)
2019-05-10 08:50 UTC, Rudolf Byker
Details | Diff | Splinter Review
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE (1.36 KB, patch)
2019-05-24 18:30 UTC, Liz Rea
Details | Diff | Splinter Review
Bug 22128: Removed outdated comment (834 bytes, patch)
2019-06-11 13:40 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE (1.42 KB, patch)
2019-06-11 13:41 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 22128: Removed outdated comment (886 bytes, patch)
2019-06-11 13:41 UTC, M. Tompsett
Details | Diff | Splinter Review
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE (1.49 KB, patch)
2019-06-28 11:40 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 22128: Removed outdated comment (958 bytes, patch)
2019-06-28 11:40 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Heinrich Hartl 2019-01-14 11:48:50 UTC
The script fails in in a GRANT command - the user does not exist
This probably is due to changed behaviour of mysql 5.7ff

 From https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-overview:
Note
If an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode. It is also possible to use GRANT to specify nonprivilege account characteristics such as whether it uses secure connections and limits on access to server resources.
However, use of GRANT to create accounts or define nonprivilege characteristics is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER.

In my case mysql hosted a koha 16.11 database upgraded to 18.5 and the only existing user was koha_library@%. There was no user koha_library@localhost.

mysql was running in the version (> 5.7)supplied with ubuntu 16.4 (Xenial).

My solution to the problem was commenting out two lines in the script. 

 diff /usr/sbin/koha-remove /usr/sbin/koha-remove_ori
71c71
< # GRANT USAGE ON \`koha_$name\`.* TO \`koha_$name\`@\`$mysql_hostname\`;
---
> GRANT USAGE ON \`koha_$name\`.* TO \`koha_$name\`@\`$mysql_hostname\`;
73c73
< # DROP USER \`koha_$name\`@\`$mysql_hostname\`;
---
> DROP USER \`koha_$name\`@\`$mysql_hostname\`;
hhl@koha-VM-dev:~ >

For a general solution the koha-remove script should be made more resilient and should not use deprecated mysql features. Since I am only very occasionally dealing with mysql and moreover I have no knowledge which user(s) have been used for koha instances in the past it is beyond my skills to define the proper revision of the script.
Comment 1 Rudolf Byker 2019-05-10 08:50:06 UTC
Created attachment 89528 [details] [review]
Use "DROP USER IF EXISTS" instead of "GRANT USAGE" before "DROP USER".

MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the user does not exist. "DROP USER IF EXISTS" has been with us since 5.7, so all users should have it by now. This patch fixes the issue.
Comment 2 Liz Rea 2019-05-24 18:30:38 UTC
Created attachment 90081 [details] [review]
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE

MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the
user does not exist. "DROP USER IF EXISTS" has been with us since 5.7,
so all users should have it by now. This patch fixes the issue.

To test:

Try to koha_remove a site, and if it works with no errors, all good!

Note: 
I reformatted this patch to community guidelines, and did not test it. I took the intent of the patch, and made it so that it would work. The work is Rudolf's and I have attributed it as such.
Comment 3 lulihua 2019-06-02 08:44:13 UTC
Koha18.11.05.000 on Debian 9.9 and MariaDB10.1.38 has the same issue
Comment 4 lulihua 2019-06-02 08:46:49 UTC
$ sudo  koha-remove  klib 

Removing Koha instance klib

ERROR 1133 (28000) at line 1: Can't find any matching row in the user table
Comment 5 M. Tompsett 2019-06-11 13:40:25 UTC
Created attachment 90491 [details] [review]
Bug 22128: Removed outdated comment
Comment 6 M. Tompsett 2019-06-11 13:41:30 UTC
Created attachment 90492 [details] [review]
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE

MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the
user does not exist. "DROP USER IF EXISTS" has been with us since 5.7,
so all users should have it by now. This patch fixes the issue.

To test:

Try to koha_remove a site, and if it works with no errors, all good!

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Comment 7 M. Tompsett 2019-06-11 13:41:33 UTC
Created attachment 90493 [details] [review]
Bug 22128: Removed outdated comment

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Comment 8 Kyle M Hall 2019-06-28 11:40:35 UTC
Created attachment 91063 [details] [review]
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE

MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the
user does not exist. "DROP USER IF EXISTS" has been with us since 5.7,
so all users should have it by now. This patch fixes the issue.

To test:

Try to koha_remove a site, and if it works with no errors, all good!

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 9 Kyle M Hall 2019-06-28 11:40:43 UTC
Created attachment 91064 [details] [review]
Bug 22128: Removed outdated comment

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 10 Martin Renvoize 2019-06-28 13:30:31 UTC
Nice work!

Pushed to master for 19.11.00
Comment 11 Martin Renvoize 2019-06-28 13:36:36 UTC
Welcome aboard Rudolf, congratulations on your first submission to be pushed to Koha and thank you.
Comment 12 Fridolin SOMERS 2019-07-25 11:44:18 UTC
Pushed to 19.05.x for 19.05.03
Comment 13 Lucas Gass 2019-07-25 21:53:12 UTC
backported to 18.11.x for 18.11.09 

Thanks Rudolf!