Sqlstate[Hy000] [1698] Access Denied for User 'Root'@'Localhost'

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default.

Basically it means that: db_users using it, will be "authenticated" by the system user credentials. You can see if your root user is set up like this by doing the following:

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin.

There are two ways to solve this:

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

Option 1:

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

Remember that if you use option #2 you'll have to connect to MySQL as your system username (mysql -u YOUR_SYSTEM_USER).

Note: On some systems (e.g., Debian 9 (Stretch)) the 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

From andy's comment it seems that MySQL 8.x.x updated/replaced the auth_socket for caching_sha2_password. I don't have a system setup with MySQL 8.x.x to test this. However, the steps above should help you to understand the issue. Here's the reply:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this authentication plugin and you can log in from the Bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. There isn’t any need for the "UPDATE user SET plugin" step.

For the 8.0.4 default authentication plugin update, see MySQL 8.0.4: New Default Authentication Plugin: caching_sha2_password.

Do this with a single SQL

Warning: Not a DBA by any means. ;)

But, a quick, untested stab at it:

SELECT min(CLNDR_DATE) FROM [TABLE]
WHERE (EFFECTIVE_DATE IS NOT NULL)
  AND (CLNDR_DATE > (
    SELECT max(CLNDR_DATE) FROM [TABLE] WHERE EFFECTIVE_DATE IS NULL
  ))

Assuming you want the first CLNDR_DATE with EFFECTIVE_DATE after the last without.

If you want the first with after the first without, change the subquery to use min() instead of max().

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from `permissions`)

Multiple reasons can be the cause.

1. This can happen when the server has already been started before updating database details on your .ENV file.
2. It could also be from inputting wrong database authentication details.

Solution

1. Restart the server anytime you update values on your .ENV file.
2. Ensure you use the correct authentication details or better still create a new database user.

---

Related Topics