MySQL Bugs: #13505: Grant new user without password got error.

Bug #13505	Grant new user without password got error.
Submitted:	26 Sep 2005 22:01	Modified:	15 Oct 2005 8:01
Reporter:	Tetsuro Ikeda	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Windows (Windows2000)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When I tried to grant new user without password on ver5.0.13 by windows-installer-version, I got error like following:

ERROR 1133 (42000): Can't find any matching row in the user table

How to repeat:
Use the 5.0.13 with "Installer" version, and try the following:

mysql> grant all on test.* to "foo"@"localhost";
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant all on test.* to "bar"@"localhost" identified by "";
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>
mysql> grant all on test.* to "foobar"@"localhost" identified by "foobar";
Query OK, 0 rows affected (0.00 sec)

ver5.0.13 on Linux (x86, glibc-2.2, static (Standard only), gcc) and Windows-without-installer works well.

This happens only on Windows-with-installer version of 5.0.13.

C:\Arquivos de programas\MySQL\MySQL Server 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.13-rc-nt

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

mysql> grant all on test.* to "foo"@"localhost";
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant all on test.* to "bar"@"localhost" identified by "";
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant all on test.* to "foobar"@"localhost" identified by "foobar";
Query OK, 0 rows affected (0.00 sec)

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

Additional info:

This is behaving as expected.  The reason it is failing for the installed windows binaries is because those are using a my.ini with a sql_mode including NO_AUTO_CREATE_USER.  Below is what our docs say about this sql mode.

"Prevent GRANT from automatically creating new users if it would otherwise do so, unless a password also is specified. (Added in MySQL 5.0.2) "

Still happening on MySQL 5.0.19 (Windows XP with SP2):

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.19-community

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

mysql> use mysql
Database changed
mysql> grant all privileges on test.* to 'foo'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>