MySQL Bugs: #72684: new password can not be set non-interactively by mysql cli

Bug #72684	new password can not be set non-interactively by mysql cli
Submitted:	19 May 2014 17:49	Modified:	2 Feb 2016 23:55
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Reported from: https://blog.starkandwayne.com/2014/05/14/changing-root-password-on-mysql-5-6/

How to repeat:
#!/usr/bin/env bash
mysql_secret=$(awk '/password/{print $NF}' /root/.mysql_secret)  
mysql -u root --password=$mysql_secret -e "SET PASSWORD = PASSWORD('mynewpassword');"  

Results in an initially unhelpful error message:

#!/usr/bin/env bash
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.  

Suggested fix:
It should be possible to complete this task with the CLI program without having to interactively change the password.

In looking at the code, it seems that the client compatibility flag indicating that mysql can handle expired passwords is explicitly not set when in batch mode, unless the --connect-expired-password option is specified.  This causes batch mode (triggered by the -e option) to fail to complete the handshake successfully, and is aimed to fail loudly for batch programs which assume ability to execute statements once a valid connection is established.

The workaround is to include the --connect-expired-password option when using -e:

R:\ade\mysql-5.7.5-m15-winx64>bin\mysql -uexp -P3307 -e"SET PASSWORD = PASSWORD(
'testing');" -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1862 (HY000): Your password has expired. To log in you must change it usin
g a client that supports expired passwords.

R:\ade\mysql-5.7.5-m15-winx64>bin\mysql -uexp -P3307 -e"SET PASSWORD = PASSWORD('testing');" -ptest --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.

R:\ade\mysql-5.7.5-m15-winx64>bin\mysql -uexp -P3307 -e"SET PASSWORD = PASSWORD('testing');" -ptest --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'exp'@'localhost' (using password: YES)

I think this is a documentation bug, in that http://dev.mysql.com/doc/refman/5.6/en/password-expiration-sandbox-mode.html should reference the --connect-expired-password option for mysql:

http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_connect-exp...

Changed to docs bug.

Filed http://bugs.mysql.com/bug.php?id=72696 as a separate feature request (improve error message returned via mysql cli)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

http://dev.mysql.com/doc/refman/5.6/en/password-expiration-sandbox-mode.html
now mentions --connect-expired-password