MySQL Bugs: #34993: Grant and Create user error messages/Documentation

Bug #34993	Grant and Create user error messages/Documentation
Submitted:	3 Mar 2008 8:30	Modified:	6 May 2008 17:42
Reporter:	Peter Volk (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	create user, documentation, error code, Grant Privileges

Description:
In versions < 5.1 it was possible to create a user with the grant statement automatically. With 5.1 it is a prerequisit to create a user prior the grant statements. This is a change in functionality not documented in: http://dev.mysql.com/doc/refman/5.1/en/grant.html. Also the error message does not give enough information about the error that accured. 

How to repeat:
Prerequ: 
-Freshly installed MySQL database server
-No additional users created (except for root)

1. Logon as root.
2. Try to grant privileges to a unknown (dummy) user:
in version 5.0.45:

mysql> grant ALL ON *.* TO 'dummy'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--> User is created

in Version 5.1.23:

mysql> GRANT ALL ON *.* TO 'dummy'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
--> no user is created. 

Suggested fix:
Change the documentation in http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html and http://dev.mysql.com/doc/refman/5.1/en/grant.html to include the hint to the change in functionality. Change the error message to (or similar): Can't change privileges of an unknown user.

Thank you for the report.

I can not repeat described behavior.

Please describe step-by-step how did you install MySQL 5.1.23 database. Did you upgrade MySQL 5.0?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Peter,

we still need to know step by step how did you install MySQL.

Hi,

I did a fresh install. Downloaded it from the website and used the standard installation procedure. Is that enough information? 

Cheers,
Peter

Thank you for the feedback.

I still can not repeat the problem on my side:

ssmirnova@foo ~
$cd mysql-5.1.23-rc-linux-i686-glibc23/

ssmirnova@foo ~/mysql-5.1.23-rc-linux-i686-glibc23
$./scripts/mysql_install_db --basedir=. --datadir=./data
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h foo.mysql.com password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./scripts/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

ssmirnova@foo ~/mysql-5.1.23-rc-linux-i686-glibc23
$ ./bin/mysqld --defaults-file=support-files/my-small.cnf --datadir=./data --socket=/tmp/mysql_ssmirnova.sock --basedir=. --log-error &
[1] 19357

ssmirnova@foo ~/mysql-5.1.23-rc-linux-i686-glibc23
$080404 20:12:05 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072

ssmirnova@foo ~/mysql-5.1.23-rc-linux-i686-glibc23
$./bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-log MySQL Community Server (GPL)

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

mysql> GRANT ALL ON *.* TO 'dummy'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

So we need: your operating system, exact name of MySQL Server package (filename) which you downloaded and example how you make fresh install similar to one I provided above.

You probably have SQL_MODE set to TRADITIONAL. According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html it's includes NO_AUTO_CREATE_USER. The latter SQL mode is responsible for the behavior you observe. Besides, it was added in 5.0.2.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

yes I can verify that i simply have a different sql_mode. It might be useful having a notice in the "Create User" Syntax section to the sql_mode section since this would be the first place to look.

Cheers,
Peter

Thank you for the feedback.

Verified as documentation request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The GRANT section mentions the NO_AUTO_CREATE_USER SQL mode.

The CREATE USER section does not, because the mode does not apply to that status.