Python: MySQLdb Connection Problems

Question

I'm having trouble with the MySQLdb module.

db = MySQLdb.connect(
    host = 'localhost', 
    user = 'root', 
    passwd = '', 
    db = 'testdb', 
    port = 3000)

(I'm using a custom port)

the error I get is:

Error 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Which doesn't make much sense since that's the default connection set in my.conf.. it's as though it's ignoring the connection info I give.. The mysql server is definitely there:

[root@baster ~]# mysql -uroot -p -P3000
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use testdb;
Database changed
mysql> 

I tried directly from the python prompt:

>>> db = MySQLdb.connect(user='root', passwd='', port=3000, host='localhost', db='pyneoform')
Traceback (most recent call last):
File "", line 1, in 
File "/usr/lib64/python2.5/site-packages/MySQLdb/__init__.py", line 74, in Connect
return Connection(*args, **kwargs)
File "/usr/lib64/python2.5/site-packages/MySQLdb/connections.py", line 169, in __init__
super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)")
>>>

I'm confused... :(

Answer 1

Maybe try adding the keyword parameter unix_socket = None to connect()?

Answer 2

Make sure that the mysql server is listening for tcp connections, which you can do with netstat -nlp (in *nix). This is the type of connection you are attempting to make, and db's normally don't listen on the network by default for security reasons. Also, try specifying --host=localhost when using the mysql command, this also try to connect via unix sockets unless you specify otherwise. If mysql is not configured to listen for tcp connections, the command will also fail.

Here's a relevant section from the mysql 5.1 manual on unix sockets and troubleshooting connections. Note that the error described (2002) is the same one that you are getting.

Alternatively, check to see if the module you are using has an option to connect via unix sockets (as David Suggests).

Answer 3

add unix_socket='path_to_socket' where path_to_socket should be the path of the mysql socket, e.g. /var/run/mysqld/mysqld2.sock

Answer 4

As far as I can tell, the python connector can ONLY connect to mysql through a internet socket: unix sockets (the default for the command line client) is not supported.

In the CLI client, when you say "-h localhost", it actually interprets localhost as "Oh, localhost? I'll just connect to the unix socket instead", rather than the internet localhost socket.

Ie, the mysql CLI client is doing something magical, and the Python connector is doing something "consistent, but restrictive".

Choose your poison. (Pun not intended ;) )

Answer 5

i had this issue where the unix socket file was some place else, python was trying to connect to a non-existing socket. once this was corrected using the unix_socket option, it worked.