Python: error connecting to mysql server using socket

Question

I'm tring to connect to my local mysqlserver.

The following code works on another machine:

root@host> mysql  --protocol=SOCKET
MariaDB [(none)]>

root@host> ls -la /var/run/mysqld/mysqld.sock 
srwxrwxrwx 1 mysql mysql 0 Jun  9 20:27 /var/run/mysqld/mysqld.sock

root@host> python3
>>> import mysql.connector
>>> mysql.connector.connect( unix_socket='/var/run/mysqld/mysqld.sock' )

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 173, in connect
    return MySQLConnection(*args, **kwargs)
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 102, in __init__
    self.connect(**kwargs)
File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 735, in connect
    self._open_connection()
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 250, in _open_connection
    self._do_auth(self._user, self._password,
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 172, in _do_auth
    self._auth_switch_request(username, password)
File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 216, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user ''@'localhost'

What may be the root of the problem?

Note that

I want to use a Socket and not TCP for reasons.
I don't want to use username/password.
This works on my other host but I can't figure out why it wont work on this one.

error shows Access denied for user ''@'localhost' so you can't access it with empty user. — furas
– furas, Commented Jun 11, 2021 at 1:26
Thanks for your comment. I tried adding user="root" but this resulted in 1698 (28000): Access denied for user 'root'@'localhost' — Scheintod
– Scheintod, Commented Jun 11, 2021 at 8:42
did you try with password= ..? I had problem to connect using mysql-connector but not when I removed it and install mysql-connector-python (created by MySQL) — furas
– furas, Commented Jun 11, 2021 at 12:20

rassakra · Accepted Answer · 2021-06-11 09:08:58Z

1

If you want to use mysql with socket you can use this method :

import MySQLdb
db = MySQLdb.connect("localhost", "root", "l542212h", "TESTDB", charset='utf8' ,unix_socket="/tmp/mysql.sock")

I hope that can help you to resolve your issue.

answered Jun 11, 2021 at 9:08

rassakra

1,1217 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scheintod Over a year ago

Thanks for your anser again and for your effort ;) I'd like to not use username and password like I don't need them in mysql --protocol=SOCKET command. As I wrote this works on another host quite well.

rassakra · Accepted Answer · 2021-06-10 21:16:36Z

0

what I suggest first of all is to use a GUI tools like MYSQL workbench or Dbeaver to connect to your database, if it's established you can use this script to connect to your database:

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

I hope that these can help you to resolve your issue.

answered Jun 10, 2021 at 21:16

rassakra

1,1217 silver badges9 bronze badges

1 Comment

Scheintod Over a year ago

Thanks for your answer. I know how to connect to mysql using TCP. My question is about connecting using a socket. root@host> mysql --protocol=SOCKET tests if this works (which it does) but not with python on this particular host.

webaware · Accepted Answer · 2022-08-19 07:34:20Z

0

You need to specify the user, and clear the password; e.g.

mysql.connector.connect(unix_socket='/run/mysqld/mysqld.sock', user='root', password=None)

answered Aug 19, 2022 at 7:34

webaware

2,8193 gold badges34 silver badges39 bronze badges

Collectives™ on Stack Overflow

Python: error connecting to mysql server using socket

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related