Bug 22128

Summary: koha-remove fails mysql ERROR 1133 (42000) at line 2: Can't find any matching row in the user table
Product: Koha Reporter: Heinrich Hartl <Heinrich.Hartl>
Component: Command-line UtilitiesAssignee: Rudolf Byker <rudolfbyker>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: fridolin.somers, lucas, lulihua, martin.renvoize, mtompset, robin, rudolfbyker
Version: 18.05   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23022
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.11.00,19.05.03
Bug Depends on:    
Bug Blocks: 23945    
Attachments: Use "DROP USER IF EXISTS" instead of "GRANT USAGE" before "DROP USER".
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE
Bug 22128: Removed outdated comment
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE
Bug 22128: Removed outdated comment
Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE
Bug 22128: Removed outdated comment

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 Mark Tompsett 2019-06-11 13:40:25 UTC
Created attachment 90491 [details] [review]
Bug 22128: Removed outdated comment
Comment 6 Mark 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 Mark 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!
Comment 14 Martin Renvoize 2019-11-04 12:21:10 UTC
This was a bad push on my part.

We still support Debian Jessie (which comes with Mysql5.5 by default) and this breaks our support for Jessie as such... Stretch is also pinned to MySQL5.5 which makes this worse but you can get MariaDB 10.1.4 which resolves it in Stretch (MariaDB supports IF EXISTS from 10.1.3)
Comment 15 Rudolf Byker 2019-11-04 12:52:57 UTC
Not just your fault. I proposed the fix without checking which distributions Koha is supposed to support.

We have a conflict between trying to support the old DBs vs the new DBs, because as I mentioned above, the latest versions of MySQL and MariaDB fail on "GRANT USAGE" if the user does not exist, which is problematic.

Maybe we should check the MySQL version, and then run the appropriate code in response to that. It's a simple IF statement. One day, when oldstable has moved on to the next release, the old code and the IF statement can simply be removed.
Comment 16 Martin Renvoize 2019-11-04 14:31:13 UTC
In better news.. Jessie drops out of support just after the release of 20.05.. so we won't have to support this for long..

Having said that.. we don't limit people to MariaDB.. so in Stretch they'll still have issues if they want to stick to mysql-server and not myself-server-transitional (which installs mariadb I believe).

One for broader discussion there.