Bug 6416 - koha-remove fails when dropping user
Summary: koha-remove fails when dropping user
Status: REOPENED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: 20.05
Hardware: All Linux
: P5 - low normal (vote)
Assignee: Robin Sheat
QA Contact: Bugs List
URL:
Keywords:
Depends on: 6684
Blocks: 24383
  Show dependency treegraph
 
Reported: 2011-05-27 10:07 UTC by Magnus Enger
Modified: 2024-01-04 10:25 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2011-05-27 10:07:40 UTC
I have MySQL running on the same machine as Apache/Koha, and have created a new library with "sudo koha-create --create-db demo". 

When i do "sudo koha-remove demo" I get this: 

Removing Koha instance demo
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for 'koha_demo'@'%'

When I log in to mysql as root and do this: 

DROP USER failed for 'koha_demo'@'%';

I get pretty much the same message. But when I replace % with localhost and do this: 

DROP USER failed for 'koha_demo'@'localhost';

it succeeds.
Comment 1 Robin Sheat 2011-09-05 04:46:25 UTC
I've had a confirmation of this. FWIW, the removal line is:

DROP USER \`koha_$name\`;

There is a comment here:
http://dev.mysql.com/doc/refman/5.1/en/drop-user.html
saying:
As of 5.0.37, if the user does not exists, MySQL responds with: 'ERROR 1396 (HY000): Operation DROP USER failed for ...'

There could be funny version things happening.

Also:
http://dev.mysql.com/doc/refman/5.1/en/account-names.html
says that '%' is implied if not there, and is treated as a wildcard, so I'd expect it should work.

I may have to do more testing at some stage.
Comment 2 Simon Story 2011-11-18 13:57:35 UTC
The '%' wildcard seems to match when mysql is checking the host the connection is being made, not when matching users to delete.

I see in koha-create that 'koha_demo'@'localhost' shouldn't have been created. Since the code is:
CREATE USER \`$mysqluser\`@'%' IDENTIFIED BY '$mysqlpwd'

Perhaps Magnus you are mistaken as to the course of events?

Observe the obtuseness of mysql:

mysql> create user mysql_is_erratic;
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from user where User like 'mysql_is_erratic';
+------------------+------+
| User             | Host |
+------------------+------+
| mysql_is_erratic | %    |
+------------------+------+
1 row in set (0.00 sec)

mysql> create user 'mysql_is_erratic'@'';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'mysql_is_erratic'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from user where User like 'mysql_is_erratic';
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| mysql_is_erratic |           |
| mysql_is_erratic | %         |
| mysql_is_erratic | localhost |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> drop user 'mysql_is_erratic';              
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from user where User like 'mysql_is_erratic';
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| mysql_is_erratic |           |
| mysql_is_erratic | localhost |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> drop user 'mysql_is_erratic'@'';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'mysql_is_erratic'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from user where User like 'mysql_is_erratic';
Empty set (0.00 sec)

simons@simonskoha:~/src/koha/debian/scripts$ mysql --version
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
Comment 3 Katrin Fischer 2015-01-06 18:12:08 UTC
Is this still valid?
Comment 4 Owen Leonard 2016-08-12 12:50:53 UTC
(In reply to Magnus Enger from comment #0)
> I have MySQL running on the same machine as Apache/Koha, and have created a
> new library with "sudo koha-create --create-db demo". 
> 
> When i do "sudo koha-remove demo" I get this: 
> 
> Removing Koha instance demo
> ERROR 1396 (HY000) at line 1: Operation DROP USER failed for 'koha_demo'@'%'

I cannot reproduce this error in master, so I'm marking it fixed.
Comment 5 Rainer Stowasser 2020-06-29 11:23:55 UTC
with the upgrade to Koha 20.5
I had the same problem

a Koha-dump (19.11)
koha-restore (in 20.5)

created a 'user'@'%'
in the database 
that could not be dropped

System 
Debian buster
10.3.22-MariaDB
Comment 6 Rainer Stowasser 2020-06-29 11:58:08 UTC
It seems to have something to do with the restore script

MariaDB [information_schema]> select * from USER_PRIVILEGES;

'koha_bibzamg'@'localhost' | def           | USAGE                   | NO           
'koha_zabibli'@'%'         | def           | USAGE                   | NO    

the user bibzamg was created with koha-create
the user koha_zabibli'@'%'with koha-restore
Comment 7 Tomás Cohen Arazi 2020-06-29 13:40:09 UTC
(In reply to rainer.stowasser from comment #6)
> It seems to have something to do with the restore script
> 
> MariaDB [information_schema]> select * from USER_PRIVILEGES;
> 
> 'koha_bibzamg'@'localhost' | def           | USAGE                   | NO   
> 
> 'koha_zabibli'@'%'         | def           | USAGE                   | NO    
> 
> the user bibzamg was created with koha-create
> the user koha_zabibli'@'%'with koha-restore

(In reply to Simon Story from comment #2)
> The '%' wildcard seems to match when mysql is checking the host the
> connection is being made, not when matching users to delete.
> 
> I see in koha-create that 'koha_demo'@'localhost' shouldn't have been
> created. Since the code is:
> CREATE USER \`$mysqluser\`@'%' IDENTIFIED BY '$mysqlpwd'
> 
> Perhaps Magnus you are mistaken as to the course of events?

This is current code (20.05's): 

    mysql_hostname="localhost"
    # Set up MySQL database for this instance.
    if [ "$op" = create ]
    then
        if [ ! -e /etc/mysql/debian.cnf ]; then
            MYSQL_OPTIONS="-u root"
            echo "WARNING: The koha-common.cnf file is a dead soft link!"
        else
            MYSQL_OPTIONS="--defaults-extra-file=/etc/mysql/koha-common.cnf"
        fi
        mysql $MYSQL_OPTIONS <<eof
CREATE DATABASE \`$mysqldb\`;
CREATE USER \`$mysqluser\`@'$mysql_hostname' IDENTIFIED BY '$mysqlpwd';
GRANT ALL PRIVILEGES ON \`$mysqldb\`.* TO \`$mysqluser\`@'$mysql_hostname';
FLUSH PRIVILEGES;
eof
Comment 8 Rainer Stowasser 2020-08-17 08:17:08 UTC
Did some tests
installed 20.5 on jessie
installed 19.11 on buster
and the restore "problem" prevails

adding a @localhost user "by hand" and everything works fine ...

have not figured out why this is not a common problem
checking the settings

the "original" system of the dump is one that was steadily upgraded since 16.5