MySQL Bugs: #14829: Can't log in from localhost after dropping user

Bug #14829	Can't log in from localhost after dropping user
Submitted:	10 Nov 2005 14:34	Modified:	26 Jan 2006 16:47
Reporter:	Markus Popp	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15, 5.0.16,5.017 BK	OS:	Windows (Windows 2000, XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I logged in as root user, created a non privileged user - then dropped the non privileged user again. Afterwards, I couldn't log in with any user account who's host is localhost and get the error message

ERROR 1130 (HY000): #HY000Host 'localhost' is not allowed to connect to this MyS
QL server

After restarting the server, it's possible to log in again.

I made several tests, but the bug occurs only on some specific occasions. Unfortunately, I couldn't find out where exactly the source of the problem is.

How to repeat:
C:\>mysql -u mpopp_root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> exit
Bye

C:\>mysql -u testuser
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

C:\>mysql -u mpopp_root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop user testuser@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

C:\>mysql -u mpopp_root -p
Enter password: *******
ERROR 1130 (HY000): #HY000Host 'localhost' is not allowed to connect to this MyS
QL server

... after restarting the server ...

C:\>
C:\>
C:\>mysql -u mpopp_root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

I was unable to repeat this issue. If you do a flush privileges
after the drop user you get the same behavior as reported?

Thanks in advance.

After flushing the privileges, it works (I can log in). Maybe there are some special situations when the privileges aren't automatically flushed after doing a drop user statement. But then it's still strange because no changes should have taken place.

How I said you I was unable to repeat the behavior reported,
however please feel free for to open it again, if you are able
for to provide a repeatable sequence of statements with
preference with a fresh install. Also I will consult with my
co-workers if after a drop user must be done a flush privileges
since as I understand only GRANT/REVOKE commands does.

Thanks in advance.

I'm also not able to reproduce the bug on a fresh machine. Only after dumping my complete server to create a 100 % duplicate, I can reproduce it. So there are zillions of possible contexts what the source of the problem could be.

However, on the fresh installation, the drop user command did implicitly issue a privilege flush, like grant and revoke do. In my special occasion where the bug occurs, it seems that it doesn't flush the privileges correctly. Maybe there's a chance to automatically issue a 'flush privileges' command after every 'drop user', just as if the user had done it manually (in which case it also works in my occasion)?

The mysqlcheck command from version 5.0.16 solved this problem. However, there must have been a bug in mysqlcheck of version 5.0.15, because running this version against the mysql database had no effect. But this bug has probably been solved with another bug ;-).

So the source of the problem must have been some data corruption of the tables in the mysql database which could be repaired by mysqlcheck of 5.0.16 (but not of 5.0.15).

Seems, I was wrong - the bug is still there. However, I'll continue to try to isolate the bug and re-open this report when I've found a reproducable case.

Use the second dump file to reproduce the bug. Start with a fresh server that has no databases (except mysql of course) and only one user root@localhost with all privileges and no password.

C:\>mysql -u root mysql < dump_mysql.sql

C:\>mysqladmin -u root flush-privileges

C:\>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

mysql> exit
Bye

C:\>mysql -u mpopp
ERROR 1130 (HY000): #HY000Host 'localhost' is not allowed to connect to this 
MySQL server

... after restarting the server ...

C:\>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Still I was unable to repeat, please see below the 2 scenarios I did.

#1 Fresh Install without to remove anonymous user
c:\mysql\bin>mysql -u root mysql < dump_mysql.sql

c:\mysql\bin>mysqladmin -u root flush-privileges

c:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

mysql> exit
Bye

c:\mysql\bin>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

#2 Fresh Install removing  anonymous user
c:\mysql\bin>mysql -u root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete from user where user="";
Query OK, 0 rows affected (0.00 sec)

mysql> delete from db where user="";
Query OK, 2 rows affected (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysqladmin -u root shutdown

c:\mysql\bin>mysql -u root mysql < dump_mysql.sql

c:\mysql\bin>mysqladmin -u root flush-privileges

c:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

mysql> exit
Bye

c:\mysql\bin>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

I tried it once more on a completely fresh installation (Windows 2000 SP 4, but my production system, where the bug also occurs is Windows XP SP 1) and again, the bug was there.

To make sure that absolutely everything is completely identical, delete the 'test' database that's automatically installed and delete all data from the mysql.db table (after installation, there are 2 records which allow all users to access databases starting with 'test'). Finally, please use the my.ini file that I've attached (very important could be that strict sql_mode is enabled).

Please do the following 3 commands before the other steps:

drop database test;
delete from mysql.db;
flush privileges;

According your last instructions. Still I was unable to repeat:

c:\mysql\bin>mysql -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.db;
Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -u root mysql < dump_mysql.sql
Enter password:

c:\mysql\bin>
c:\mysql\bin>mysqladmin -u root flush-privileges
Enter password:

c:\mysql\bin>mysql -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

mysql> exit
Bye

c:\mysql\bin>mysql -u mpopp
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

That's very strange - I also don't find any difference between your attempts and mine anymore.

I will ask for help in my weblog, maybe other users can help to find a clue why it works sometimes and sometimes not.

Output of my session on 5.0.15-nt
-------------------------------------------

F:\mysql-5.0.15-win32\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user shane@localhost;
Query OK, 0 rows affected (2.77 sec)

mysql> select user,host,password from mysql.user;
+---------+-----------+----------+
| user    | host      | password |
+---------+-----------+----------+
| mpopp   | localhost |          |
| martina | localhost |          |
| root    | localhost |          |
| web112  | localhost |          |
| web157  | localhost |          |
| web78   | localhost |          |
| mpopp   | 10.%      |          |
| shane   | localhost |          |
+---------+-----------+----------+
8 rows in set (2.31 sec)

mysql> drop user shane@localhost;
Query OK, 0 rows affected (2.23 sec)

mysql> select user,host,password from mysql.user;
+---------+-----------+----------+
| user    | host      | password |
+---------+-----------+----------+
| mpopp   | localhost |          |
| martina | localhost |          |
| root    | localhost |          |
| web112  | localhost |          |
| web157  | localhost |          |
| web78   | localhost |          |
| mpopp   | 10.%      |          |
+---------+-----------+----------+
7 rows in set (2.23 sec)

mysql> exit
Bye

F:\mysql-5.0.15-win32\bin>mysql -u root
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

F:\mysql-5.0.15-win32\bin>

-----
Here I had to terminate mysqld-debug.exe and restart it
-----

F:\mysql-5.0.15-win32\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Here's a repeatable testcase, works brand new installation of 5.0.16-noinstall-win32 and 5.0.15-noinstall-win32:

-------------------
mysql -u root

CREATE USER 'aaaaaa'@'localhost';
CREATE USER 'bbbbbb'@'localhost';
CREATE USER 'cccccc'@'localhost';
CREATE USER 'dddddd'@'localhost';
CREATE USER 'eeeeee'@'localhost';
CREATE USER 'ffffff'@'10.%';
exit;

mysql -u root

CREATE USER 'gggggg'@'localhost';
DROP USER 'gggggg'@'localhost';
exit;

mysql -u root

------------------

The last mysql -u root will fail with error: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

I was able to repeat the issue with Shane's test case just on Windows
and not on Linux:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'aaaaaa'@'localhost';
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE USER 'bbbbbb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'cccccc'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'dddddd'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'eeeeee'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'ffffff'@'10.%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

c:\mysql\bin>mysql -uroot
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'gggggg'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'gggggg'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

c:\mysql\bin>mysql -uroot
Enter password:
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

c:\mysql\bin>

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'aaaaaa'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'bbbbbb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'cccccc'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'dddddd'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'eeeeee'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'ffffff'@'10.%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'gggggg'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'gggggg'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
miguel@hegel:~/dbs/5.0> 
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Hello!

I am having a similar problem.

I have a small personal server running FreeBSD 5.4 STABLE

This is the version of mysql  Ver 11.18 Distrib 3.23.58, for portbld-freebsd5.3 (i386)

I don't know much about mysql I just got it installed so I can use a gallery or forum script that needs database, so I have installed PhpMyAdmin too.

Until now I never had this problem, everything worked fine, when I try to login from PhpMyAdmin, from any IP using ANY user, I get the following:

#1130 - #HY000Host '217.126.111.68' is not allowed to connect to this MySQL server

I have shut down mysql server, and then start it again.
Same error:

#1130 - #HY000Host '217.126.111.68' is not allowed to connect to this MySQL server

I did a reload/flush privileges, and it's still not working.

I dont know who and what did to the mysql server in the past days, only one friend and me have access to it.

The last thing, my server reached his max uptime, 

 2:34AM  up 41 days, 11:28, 2 users, load averages: 0.01, 0.02, 0.00

before now it wasn't online more than 10-15 days.

There's no way to connect via phpmyadmin, maybe I'm stupid, maybe I did something wrong, maybe something's wrong with phpmyadmin that has nothing to do with mysql, but if anyone wants to study this error you can contact me. 

alexandru_trandafir(at)yahoo(dot)com

Solved my problem!

My friend changed something in phpmyadmin

$cfg['Servers'][$i]['host']

that should be 

$cfg['Servers'][$i]['localhost']

Bye!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74

Is there a relation between this bug and http://bugs.mysql.com/bug.php?id=15775?

Duplicate to #15775. Patch for #15775 almost identical to patch commited for #14829, and fixes them problem #14829 as well.