skip to Main Content

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


  1. Chosen as BEST ANSWER

    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.


  2. error:ERROR 1045 (28000): Access denied for user ‘joe’@’localhost’
    Check the user table, select host,user from mysql.user;

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search