MySQL Bugs: #20599: Incorrect behavoiur of "flush privileges" command

Bug #20599	Incorrect behavoiur of "flush privileges" command
Submitted:	21 Jun 2006 10:24	Modified:	1 Jul 2006 7:43
Reporter:	Andrea Pantaleoni	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (linux redhat and debian)
Assigned to:		CPU Architecture:	Any

Description:
When someone want to give to a new user the create privilege on a db, updating the db table in mysql database, it doesn't work until the client disconnect and reconnect to server!!
The client cannnot create tables before he disconnect and reconnect to the server.
 

How to repeat:
from the server:

1) create new user with no general privileges (user table in mysql with 'N') 
2) create new db

from client: 

1)connect with the user account just before created

from the server:

1)in the table mysql.db update the fields Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y'

from the client:

1) show databases shows the new db just before created
2) user can not create a table on the new db because has no rights 

from the server:

1) in the table mysql.db update the field Create_priv='Y'

from the client:

1) user can not create a table on the new db because has no rights!!!!!!!

Now only if the client disconnect from the server and reconnect he is able to create a table!!

Suggested fix:
No Idea

Please, send the exact sequence of SQL statements that demonstrates the behaviour you described. With FLUSH PRIVILEGES, if any.

ON THE SERVER AS ROOT
1)use mysql
2)insert into user (Host,User,Password) values ('%','soccer',PASSWORD('soccer'));
3)flush privileges;
4)create database soccer;
5)insert into db (Host,Db,User) values ('%','soccer','soccer');
6)flush privileges;

ON THE SERVER AS USER SOCCER
1)show databases;
I don't not soccer db

ON THE SERVER AS ROOT
1)Update db set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y' where host='%' and db='soccer' and user='soccer';
2)flush privileges;

ON THE SERVER AS USER SOCCER
1)show databases;
I see soccer db

ON THE SERVER AS ROOT
1)Update db set Create_priv='Y' where host='%' and db='soccer' and user='soccer';
2)Flush privileges;

ON THE SERVER AS USER SOCCER
1)Create table test (idTest int(11) NOT NULL);
I get: ERROR 1142 (42000): CREATE command denied to user 'soccer'@'localhost'...

To create the table I have to disconnect and reconnect as user Soccer then I have the rights to perform the create statement

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See also http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html