MySQL Bugs: #77227: mysqld_multi stop does not work

Bug #77227	mysqld_multi stop does not work
Submitted:	2 Jun 2015 15:01	Modified:	9 Jun 2015 8:47
Reporter:	Georgi Iovchev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.6.25	OS:	Any (CentOS 6.6 / 7.0)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
mysqld_multi stop command does not work with 5.6.25.
It returns error error: 'Access denied for user 'user'@'localhost' (using password: YES)', even if the user has enough permissions.
Tested on CentOS 6.6 and CentOS 7.

With version 5.6.24 there were no such problem!

How to repeat:
Install MySQL 5.6.25 on CentOS, using yum from official mysql repo.

Prepare minimal my.cnf

[mysqld_multi]
user       = root
password   = 123456

[mysqld1]
mysqld         = /usr/bin/mysqld_safe
bind-address   = 0.0.0.0
port           = 3306
user           = mysql
datadir        = /var/lib/mysql
socket         = /var/lib/mysql/mysqld.sock

Start mysql:
# mysqld_multi start

Set root password:
mysql> update mysql.user set password='123456' where user='root';
mysql> flush privileges;

Try to stop mysql using mysqld_multi
# mysqld_multi --no-log stop 1

Output should be:
mysqld_multi log file version 2.16; run: Tue Jun  2 17:39:59 2015
Stopping MySQL servers
Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Try directly stopping mysql server with same user:
# mysqladmin --user=root --password=123456 --protocol=socket --socket=/var/lib/mysql/mysqld.sock shutdown

# ps -ef |grep [m]ysqld | wc -l
0

it has stopped.

Suggested fix:
As a current workaround I use in my.cnf in [mysqld_multi] section I use pass = ... instead of password = ...

Instead
password = 123456
I use:
pass = 123456

And mysqld_multi works this way.

Thank you for the report.
I didn't try the issue at my end but the way you have updated the root password could be the issue here, please set the root password as:

UPDATE mysql.user SET Password = PASSWORD('123456') WHERE User = 'root';
FLUSH PRIVILEGES;

Please test at your end once again after resetting root password and report us back if you are still having the issue.

Bug #77240 marked as duplicate of this

Sorry my mistake with the password, I have typed a bit wrong example. In my real setup the password have been properly updated and is hashed in mysql.user table. Also I have tried with another user, dedicated only for it.
grant shutdown on multi@localhost identified by '123456'
Still the same result - error: 'Access denied for user 'multi'@'localhost' (using password: YES)

another wrong example :) I have to stop typing and start pasting...
in my previous comment user creation should be: grant shutdown on *.* to multi@localhost identified by '123456';

Thank you for the feedback.
Observed this with 5.6.25 build(5.6.24 not affected) on Oracle Linux Server release 7.0.

Thanks,
Umesh

test results

Attachment: 77227.txt (text/plain), 23.42 KiB.

I have the same problem. After adding some traces in mysqld_multi, I have found the problem : since 5.6.25, my_print_defaults no longer returns password in readable form (we have to use the "--show" option to obtain it). mysqld_multi call  my_print_default to obtain the user and password to use with mysqladmin.

Also, to be able to stop instances with mysqld_multi, I have made a modification in the "defaults_for_group" function of mysqld_multi. I have changed the following line :
my $com= join ' ', 'my_print_defaults', @defaults_options, $group;
with :
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

Regards

Bug #77946 marked as duplicate of this

Bug #78974 marked as duplicate of this

Bug #81682 marked as duplicate of this

It is always there with 5.7.13 on CentOS 6.6

Bug #82288 marked as duplicate of this

Still present in version 5.7.17. Workaround with configuring pass instead of password works.

Still present in version 5.7.20. Workaround with configuring pass instead of password works.