MySQL Bugs: #96795: password hash should be a 41-digit hexadecimal number (code 1372) (server 5.6)

Bug #96795	password hash should be a 41-digit hexadecimal number (code 1372) (server 5.6)
Submitted:	9 Sep 2019 18:44	Modified:	11 Sep 2019 18:35
Reporter:	Rob Wagner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I'm running Workbench 8.0.17, and when connecting to a MySQL 5.6 server as a user with an expired password, I get prompted to change my password ("Password for MySQL account blah expired. Please pick a new password.") I enter my old password and a new password, click OK, and then I get "Password hash should be a 41-digit hexadecimal number (code 1372).

Only seems to happen when connecting to 5.6; connections to 5.7 are fine. And if I connect to the same 5.6 server with the same account using the MySQL CLI, it works fine:

rowagn@dig229au:~#> /sso/sfw/mysql/bin/mysql -utest -ptest -hblah.com
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1016592
Server version: 5.6.10

Copyright (c) 2000, 2017, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password = password('test123');
Query OK, 0 rows affected (0.01 sec)

I suspect Workbench is sending "set password = 'secretPassword'" instead of "set password = password('secretPassword')".

Any way to resolve this? Thanks.

How to repeat:
On a 5.6 server, create a user with an expired password:

mysql> create user 'test'  identified by 'test' ;
Query OK, 0 rows affected (0.05 sec)
mysql> alter user test password expire;
Query OK, 0 rows affected (0.01 sec)

Then in MySQL Workbench, select Database->Connect to Database and enter the server's hostname, port and username (test).  Click OK.  It will prompt for your password, enter ir, and click OK again.  Now, you should see a window titled "Password Expired."  It will say "Password for MySQL account test@host expired.  Please pick a new password."  Enter the old password (test) and a new password (test123).  Click OK.  An error will appear "Password hash should be a 41-digit hexadecimal number (code 1372)."  At this point, you cannot login to the server using MySQL Workbench, hence, I gave it S2 (Serious).

Suggested fix:
Workbench should properly change an expired password on a MySQL 5.6 server just like it does on a 5.7 server.  The documentation indicates Workbench 8.0 supports MySQL 5.6 (see https://dev.mysql.com/doc/mysql-compat-matrix/en/).

Hello Rob Wagner,

Thank you for the report and feedback.

regards,
Umesh

Note, I don't see this issue on 6.3.10.

I have looked into this as this is affecting our users. On a positive note, if you click on Cancel, the connection is still established. Then you can run SET PASSWORD to change to the new password.

Ignore my previous comment as I had another connection and the Query TAB was from that one. I have done some more analysis using tcpdump and this is what I have found. MySQLWorkbench is indeed sending SET PASSWORD incorrectly. I have tested this on the latest version - Version 8.0.19 build 15713499 CE (64 bits) Community. This is a show stopper for us as users can't change their expired passwords and this in turn is blocking SOCK2 compliance or creating huge support overheads. Someone should address this urgently!

---
2020-01-23 11:08:27.253323 IP 127.0.0.1.57377 > 127.0.0.1.57363: tcp 52
E..h..@.@............!.....O.VYP.....\.....
M/QcM/Qc0....SET PASSWORD = 'a7ggjgjhg67Gkjll'
2020-01-23 11:08:27.253333 IP 127.0.0.1.57363 > 127.0.0.1.57377: tcp 0
E..4..@.@..............!.VYP.........(.....
M/QcM/Qc
2020-01-23 11:08:27.253338 IP 127.0.0.1.57363 > 127.0.0.1.57377: tcp 0
E..4..@.@..............!.VYP.........(.....
M/QcM/Qc
2020-01-23 11:08:27.271736 IP 127.0.0.1.57363 > 127.0.0.1.57377: tcp 66
E..v..@.@..............!.VYP.........j.....
M/QuM/Qc>....\.#HY000Password hash should be a 41-digit hexadecimal number
---

Is there any plans to have this fixed?