MySQL Bugs: #20367: GRANT statements with host parameter broken?

Bug #20367	GRANT statements with host parameter broken?
Submitted:	9 Jun 2006 19:56	Modified:	29 Nov 2006 14:31
Reporter:	eric	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.20	OS:	Windows (WinXP)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Source Editors

Description:
I'm pointing my query browser at a 5.0.18-max server, and trying to run a script that generates users and permissions.  However, Query Browser chokes on the first grant statement that includes a host component to the user name.  The same script runs fine with 'source script.sql' from the mysql command line client.

How to repeat:
Steps to reproduce:
Open QB and connect to server
Open a new script tab (File/New Script Tab)
Type in: 
GRANT USAGE ON *.* TO user@'%' IDENTIFIED BY 'whatever';
GRANT SELECT ON 'test'.* TO user@'%';

Notice that the breakpoint indicator shows that QB thinks these statements are tied together somehow, and executing shows a syntax error near '; GRANT SELECT ON (...)'

Removing the hostname components (or even just the '@' sign) from the GRANT statements fixes this behavior.

Suggested fix:
Remove whatever confusion is surrounding '@' in the query parsing logic.

Thank you for a problem report. Sorry, but you really have error in your second statement:

GRANT SELECT ON 'test'.* TO user@'%';

And you'll see the same error message in mysql client. You have to write that as (note backticks!):

GRANT SELECT ON `test`.* TO user@'%';

or 

GRANT SELECT ON test.* TO user@'%';

If you correct your second statement, your script will work in QB 1.1.20.

I have tried your suggestion (both removing the single ticks and replacing them with backticks) with no change in behavior.  Furthermore, as I stated in the original bug report, the script does indeed work unmodified (and modified) in the commandline client.  I will attach screenshots shortly, but as far as I can tell I'm using legal syntax.  Are you running Linux or other OS?  Perhaps that could account for why your changes worked for you when you tested them, but they do not work for me.

I've also tried changing the username in case the term 'user' is causing problems,  but that didn't help either.

Screenshot of 1.1.20 failing to work with modified script

Attachment: mysqlqb-bug20367.png (image/png, text), 61.75 KiB.

MySQL command line client executing same script w/ no problems

Attachment: mysqlqb-bug20367-cmdlinetest.png (image/png, text), 13.48 KiB.

Sorry, you are right. The following statements:

GRANT USAGE ON *.* TO user@'%' IDENTIFIED BY 'whatever';
GRANT SELECT ON `test`.* TO user@'%';

also does NOT work in Script tab of QB 1.1.20. I've got the same error message as on your screenshot. It is a bug.

I checked them last time as two different statements, one by one (hence different result I've got)...

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/