Skip to content
Advertisement

MySQLdb._exceptions.OperationalError: (1698, “Access denied for user ‘root’@’localhost'”)

When I ran a SQL python script it occurred these errors

MySQLdb._exceptions.OperationalError: (1698, "Access denied for user 'root'@'localhost'")
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1698, "Access denied for user 'root'@'localhost'")
(Background on this error at: http://sqlalche.me/e/13/e3q8)

I looked in the mysql.user table and get the following:

+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

Advertisement

Answer

I just had the same problem with MariaDB and found myself here expectating some answers, but no lucky. After some research I did an workaround, and judging by the similarities in both DB managers, I think it will help anyone having this problem too. I’m somewhat newbie so I can’t explain the technical stuff behind it but its the default for how mySQL(in my case MariaDB) works.

What I did was create a user on the DB manager (mySQL or MariaDB) for my user on linux and, for don’t potentially expose my password having to pass it to the script, I used the authentication plugin ‘unix_socket’ so you don’t need a password at all (of course it’s only for linux users):

MySQL -u root
CREATE USER user@localhost IDENTIFIED VIA 'unix_socket';

Then grant privileges to your new user:

GRANT ALL ON database_name.* TO user@localhost WITH GRANT OPTION;

If you want to connect remotely change ‘localhost’ to ‘%’; and if you are not a linux user I think you can create a user with a password and pass it to sqlalchemy and it will work.

OBS: I’m not sure but I think the problem is that ‘root’ uses both the ‘unix_socket’ and ‘mysql_native_password’ authentication plugins to increase security.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement