Bug #40717 mysqladmin --flush-privileges password "*" msg w/ skip-grant-tables incorrect

• Submitted: 13 Nov 2008 21:15
• Modified: 20 Nov 2008 10:45
• Reporter: Shannon Wade
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.0.45-log ,5.1.26-rc-log, 5.0, 5.1, 6.0 bzr
• OS: Any
• CPU Architecture: Any

[13 Nov 2008 21:15] Shannon Wade

Description:
When the server is started with skip-grant-tables, and one tries to use mysqladmin to change a password. mysqladmin responds with a message indicating that grant tables must be enabled:

mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead

However when you follow the advice of the message:

Use: "mysqladmin flush-privileges password '*'" instead

This doesn't work, Performing what is suggested results in:

mysqladmin: unable to change password; error: 'Can't find any matching row in the user table'

This is due to anonymous connection. However after that statement is ran, grant checking is turned on due to the flush privs and the statement will succeed if ran twice,.

How to repeat:
set skip-grant-tables (--skip-grant-tables or in my.cnf)

sh-3.2# ./bin/mysqladmin -uroot -S tmp/mysql.sock password "password"
./bin/mysqladmin: 
You cannot use 'password' command as mysqld runs
 with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead

sh-3.2# ./bin/mysqladmin -uroot -S tmp/mysql.sock flush-privileges password "password"
./bin/mysqladmin: unable to change password; error: 'Can't find any matching row in the user table'

sh-3.2# ./bin/mysqladmin -uroot -S tmp/mysql.sock flush-privileges password "password"
./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

from general query log:
081113 15:55:55    3 Connect	root@ as anonymous on 
		    3 Query	SHOW VARIABLES LIKE 'old_passwords'
		    3 Query	set sql_log_off=1
081113 15:55:59    4 Connect	root@ as anonymous on 
		    4 Query	flush privileges
		    4 Query	SHOW VARIABLES LIKE 'old_passwords'
		    4 Query	set sql_log_off=1
		    4 Quit	
081113 15:56:01    5 Connect	Access denied for user 'root'@'localhost' (using password: NO)

Suggested fix:
suggest use: "mysqladmin flush-privileges" then "mysqladmin password '*'" 

or change behavior of flush-privileges when used with password.

[20 Nov 2008 10:45] Sveta Smirnova

Thank you for the report.

Verified with different behavior:

$./bin/mysqladmin  -uroot --socket=/tmp/mysql_ssmirnova.sock password "password"
./bin/mysqladmin: 
You cannot use 'password' command as mysqld runs
 with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead

$./bin/mysqladmin  -uroot --socket=/tmp/mysql_ssmirnova.sock flush-privileges password "password"
./bin/mysqladmin: unable to change password; error: 'Can't find any matching row in the user table'

$./bin/mysqladmin  -uroot --socket=/tmp/mysql_ssmirnova.sock flush-privileges password "password"

$./bin/mysqladmin  -uroot --socket=/tmp/mysql_ssmirnova.sock flush-privileges password ""
./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Although password changes only in second command (which finishes without error), behavior is still buggy.