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

[19 May 2014 17:49] Morgan Tocker
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.
[19 May 2014 19:32] Todd Farmer
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)
[20 May 2014 19:32] Todd Farmer
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...
[20 May 2014 19:42] Todd Farmer
Changed to docs bug.
[20 May 2014 20:06] Morgan Tocker
Filed http://bugs.mysql.com/bug.php?id=72696 as a separate feature request (improve error message returned via mysql cli)
[2 Feb 2016 23:55] Paul Dubois
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