I have an entry for ‘joe’@’%’ with the same password and the same mysql_native_password plugin on a MySQL 5.7 and MySQL 8 server for these tests.
The "mysql" binary from MySQL 5.7 honours "-p" as expected when I want to connect as a general user:
# on the MySQL 5.7 localhost
$ mysql -u joe -h localhost -P 3306 -p
Enter password:
# on the MySQL 5.7 localhost to the MySQL 8 remote host
$ mysql -u joe -h remotehost -P 3306 -p
Enter password:
or as the root user defined in .my.cnf (which lets you connect without passed user/password information):
# on the MySQL 5.7 localhost using the root user defined in .my.cnf
$ mysql -u root -p
Enter password:
but the "mysql" binary from MySQL 8 doesn’t seem to honour "-p" and instead, immediately throws an error:
# on the MySQL 8 local host
$ mysql -u joe -h localhost -P 3306 -p
ERROR 1045 (28000): Access denied for user 'joe'@'localhost' (using password: YES)
# on the MySQL 8 local host to the MySQL 5.7 remote host
$ mysql -u joe -h remotehost -P 3306 -p
ERROR 1045 (28000): Access denied for user 'joe'@'remotehost' (using password: YES)
while as the root user defined in .my.cnf, it fails to honour but succeeds to connect:
# on the MySQL 8 local host using the root user defined in .my.cnf
$ mysql -u root -p
Welcome to the MySQL monitor.
Putting the credentials for ‘joe’ into .my.cnf allows me to connect fine (confirming the password is good):
$ mysql -u joe -p
Welcome to the MySQL monitor.
I’m puzzled and look forward to being embarrassed by a simple solution 🙂
Workaround: mysql --no-defaults
will honour -p
2
Answers
This was a bug introduced in 8.0.29 and has been fixed in 8.0.31.
Release Notes state "Following a bug fix in MySQL 8.0.29, if the password was provided in an option file, the mysql client did not prompt for a password if the -p option was specified at login. The client now always prompts for a password when the -p option is specified and uses the specified password, even if an alternative was populated from an option file. (Bug #107205, Bug #34134984)"
Although my scenario was from a different angle -- attempting to connect to a remote server with -u and -p -- the 8.0.31 client now offers a Password prompt as expected.
error:ERROR 1045 (28000): Access denied for user ‘joe’@’localhost’
Check the user table, select host,user from mysql.user;