SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' (Connection: mysql, SQL: select * from `users` limit 1)

All of a sudden I get the error when I try to login on my local machine. I'm on Windows11 and XAMPP with 10.4.32-MariaDB. My settings:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=livewire3
DB_USERNAME=root
DB_PASSWORD=

In phpmyadmin I can see that user root is there with password no and with ALL PRIVILEGES

LaryAI

1 year ago

Level 58

The error message "SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'" typically indicates an issue with authentication when trying to connect to the MySQL database. Here are some steps you can take to resolve this issue:

Check MySQL Authentication Method:
- Sometimes, MySQL is configured to use a plugin like auth_socket for authentication, which doesn't use a password. You can check this by running the following command in your MySQL command line or phpMyAdmin:
```
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
```
- If the plugin is set to auth_socket, you can change it to mysql_native_password:
```
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;
```
Ensure MySQL Service is Running:
- Make sure that the MySQL service is running in XAMPP. You can check this in the XAMPP control panel.
Verify Database Credentials:
- Double-check your .env file to ensure that the database credentials are correct. Since you are using the root user with no password, make sure the DB_PASSWORD is indeed empty.
Check MySQL User Privileges:
- Ensure that the root user has the necessary privileges. You can verify this by running:
```
SHOW GRANTS FOR 'root'@'localhost';
```
- If necessary, you can grant all privileges again:
```
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
Restart MySQL and Apache:
- After making changes, restart both MySQL and Apache services in XAMPP to ensure that all changes take effect.
Check for Multiple MySQL Installations:
- Ensure that there are no conflicting MySQL installations on your system that might be causing the issue.

By following these steps, you should be able to resolve the access denied error and connect to your MySQL database successfully. If the problem persists, consider checking the MySQL error logs for more detailed information.

muuucho

1 year ago

Level 11

when I run FLUSH PRIVILEGES; I get #1030 - Got error 176 "Read page with wrong checksum" from storage engine Aria

And User root seems to have all privileges : GRANT ALL PRIVILEGES ON *.* TO root@localhost``

jlrdw

1 year ago

Level 75

https://www.apachefriends.org/faq_windows.html

Quote

How can I set a root password in MySQL?

Configure it with the "XAMPP Shell" (command prompt). Open the shell from the XAMPP control pane and execute this command:
mysqladmin.exe -u root password secret

Unquote

Of course change secret to what ever password you choose.

Also modify the phpmyadmin config.

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'zzzzzzzz';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Lang'] = '';

muuucho

1 year ago

Level 11

@jlrdw When i try to execute the command mysqladmin.exe -u root password secret I get the error error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I there a way to enter no/empty password?

jlrdw

1 year ago

Best Answer

Level 75

@muuucho no password is the default. I suggest uninstall and reinstall xampp. And just don't mess with passwords.

I never have got that error. I use my own custom wamp now.

1 like

muuucho

1 year ago

Level 11

@jlrdw OK, will reinstall.

Please or to participate in this conversation.