MySQL Bugs: #98503: Can't connect with MySQL Shell after changing stored password

Bug #98503	Can't connect with MySQL Shell after changing stored password
Submitted:	6 Feb 2020 16:29	Modified:	29 Apr 2020 20:30
Reporter:	Peter Fales	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
If you save the password for a MySQL Shell connection, and the subsequently change the password, it's no longer possible to connect.   

This seems to be a problem introduced in 8.0.19.   I'm teaching a class and many students have run into it.  I didn't see the problem in 8.0.18, and students were able to resolve the problem by installing 8.0.18 instead.

How to repeat:
1) Use the MySQL shell to connect to a server (\connect)
2) When asked about saving your password select Y
3) Within the session, change your password (SET PASSWORD = ...)
4) reconnect (\connect)
5) Instead of prompting for, and storing, the new password, you get "Access denied for user ..."

According to https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-pluggable-password-store.html the expected behavior is 

"Before prompting, the Secret Store Helper is queried for a password using the session's URL. If a match is found this password is used to open the session. If the retrieved password is invalid, a message is added to the log, the password is erased from the Secret Store and MySQL Shell prompts you for a password."

Suggested fix:
Restore the documented behavior (which was the way it worked in 8.0.18 and earlier)

A workaround is to delete the stored credential using the javascript command:

shell.deleteCredential("username@hostname:port")  (where username@hostname:port is whatever connection was originally used with\connect)

Hello Peter,

Thank you for the report and feedback.
I tried to reproduce the reported issue at my end but not seeing any issues while using \connect i.e changed password within the session and when attempted to connect again it prompted for restore etc but observed access denied error only with \reconnect.  Could you please confirm if this is the issue? Thank you.

-- Win10 - MySQL Server/MySQL Shell version 8.0.19
-- CLI
create user 'tt'@'%' identified by 'mysql123';
grant all on *.* to 'tt'@'%';

--
C:\Users\umshastr>mysqlsh --log-level=debug3
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect tt@127.0.0.1
Creating a session to 'tt@127.0.0.1'
Please provide the password for 'tt@127.0.0.1': ********
Save password for 'tt@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 16 (X protocol)
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  127.0.0.1:33060+ ssl  JS >
 MySQL  127.0.0.1:33060+ ssl  JS >

 MySQL  127.0.0.1:33060+ ssl  SQL > SET PASSWORD = 'mysql123!';
Query OK, 0 rows affected (0.0230 sec)
 MySQL  127.0.0.1:33060+ ssl  SQL > \connect
\connect [--mx|--mysqlx|--mc|--mysql] <URI>
 MySQL  127.0.0.1:33060+ ssl  SQL > \connect tt@127.0.0.1
Creating a session to 'tt@127.0.0.1'
Please provide the password for 'tt@127.0.0.1': *********
Save password for 'tt@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 18 (X protocol)
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

-- reconnect fails

MySQL  127.0.0.1:33060+ ssl  SQL >
 MySQL  127.0.0.1:33060+ ssl  SQL > SET PASSWORD = 'mysql123!';
Query OK, 0 rows affected (0.0230 sec)
 MySQL  127.0.0.1:33060+ ssl  SQL > \reconnect
Attempting to reconnect to 'mysqlx://tt@127.0.0.1:33060'..............
The global session could not be reconnected automatically.
Please use '\reconnect' instead to manually reconnect.
 MySQL  SQL >

regards,
Umesh

Hmm, that's puzzling are you using 8.0.19? I'm still seeing the problem there as are a number of other students in the class.   I'll attach a screenshot that demonstrates the problem.   

- Connect using \connect
- Save the password 
- Use \connect again
- Login fails

Though not shown in this scenario, the problem occurs if I exit from the shell after chaning the password and start it up again.

Screenshot showing the problem with 8.0.19

Attachment: ss.png (image/png, text), 65.64 KiB.

Does the server version matter in this case?   We are using a 5.6.45 server.

The reason I wonder is that I noticed in your example, you used

SET PASSWORD = 'xxxx';

If do that, I get an error message that the password must be a hash.  I have to say

SET PASSWORD = PASSWORD('yyyyyy');

Thank you for the feedback.
Issue observed when (re)connecting with 5.6/5.7 servers.

-- MySQL Shell 8.0.19 and MySQL Server 5.6.47

Microsoft Windows [Version 10.0.18362.592]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\umshastr>mysqlsh --log-level=debug3
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect tt@*****.no.oracle.com:3333
Creating a session to 'tt@*****.no.oracle.com:3333'
Please provide the password for 'tt@*****.no.oracle.com:3333': ********
Save password for 'tt@*****.no.oracle.com:3333'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 3
Server version: 5.6.47 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  *****.no.oracle.com:3333  JS >
 MySQL  *****.no.oracle.com:3333  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  *****.no.oracle.com:3333  SQL >
 MySQL  *****.no.oracle.com:3333  SQL > SET PASSWORD = PASSWORD('mysql123!');
Query OK, 0 rows affected (0.3468 sec)
 MySQL  *****.no.oracle.com:3333  SQL >
 MySQL  *****.no.oracle.com:3333  SQL > \connect tt@*****.no.oracle.com:3333
Creating a session to 'tt@*****.no.oracle.com:3333'
MySQL Error 1045 (28000): Access denied for user 'tt'@'10.74.117.78' (using password: YES)
 MySQL  *****.no.oracle.com:3333  SQL >
 
 
-- MySQL Shell 8.0.19 and MySQL Server 5.7.29

C:\Users\umshastr>mysqlsh --log-level=debug3
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect tt57@*****.no.oracle.com:3333
Creating a session to 'tt57@*****.no.oracle.com:3333'
Please provide the password for 'tt57@*****.no.oracle.com:3333': ********
Save password for 'tt57@*****.no.oracle.com:3333'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 6
Server version: 5.7.29 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  *****.no.oracle.com:3333 ssl  JS >
 MySQL  *****.no.oracle.com:3333 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  *****.no.oracle.com:3333 ssl  SQL >
 MySQL  *****.no.oracle.com:3333 ssl  SQL >  SET PASSWORD = PASSWORD('mysql123!');
Query OK, 0 rows affected, 1 warning (0.3651 sec)
Warning (code 1287): 'SET PASSWORD = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD = '<plaintext_password>' instead
 MySQL  *****.no.oracle.com:3333 ssl  SQL >
 MySQL  *****.no.oracle.com:3333 ssl  SQL > \connect tt57@*****.no.oracle.com:3333
Creating a session to 'tt57@*****.no.oracle.com:3333'
MySQL Error 1045 (28000): Access denied for user 'tt57'@'10.74.117.78' (using password: YES)
 MySQL  *****.no.oracle.com:3333 ssl  SQL >

Posted by developer:
 
This bug only affects classic connections, X protocol connections are not affected.

Posted by developer:
 
Changelog entry added for MySQL Shell 8.0.19:

        A bug in MySQL Shell 8.0.19, affecting classic MySQL protocol
        connections only, meant that access was denied if a user had
        stored the connection's password with MySQL Shell and
        afterwards changed it. The password store now removes invalid
        passwords and presents the user with a password prompt as
        expected.

This appears to be fixed in 8.0.20