16

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... :(

codeforester
  • 39,467
  • 16
  • 112
  • 140
Ian
  • 24,116
  • 22
  • 58
  • 96

7 Answers7

54

Changing localhost to 127.0.0.1 solved my problem using MySQLdb:

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

Using 127.0.0.1 forces the client to use TCP/IP, so that the server listening to the TCP port can pickle it up. If host is specified as localhost, a Unix socket or pipe will be used.

Gilles 'SO- stop being evil'
  • 104,111
  • 38
  • 209
  • 254
garg
  • 1,246
  • 2
  • 16
  • 21
  • 1
    yes, this is especially tricky: a request for a TCP connection over 'localhost' triggers a unix socket error. – mathieu Jan 25 '13 at 13:16
  • 1
    amazing, this solved my problem too! This works with XAMPP and Big Brother Bot (B3)! – gaborous Feb 25 '13 at 22:32
  • 3
    Alternatively, you could find out where your MySQL unix socket is, and specify it using `unix_socket=/path/to/mysql.sock` – daviewales Jun 28 '13 at 08:03
12

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

Himanshu
  • 31,810
  • 31
  • 111
  • 133
3

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.

Jens
  • 69,818
  • 15
  • 125
  • 179
thushara
  • 31
  • 1
3

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).

Dana the Sane
  • 14,762
  • 8
  • 58
  • 80
  • Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3300 0.0.0.0:* LISTEN 5361/mysqld – Ian Apr 17 '09 at 03:26
  • Yeah, my bad, in the examples above I wrote port 3000. I fixed that.. and strangely, the problem persists. heh. ugh. – Ian Apr 17 '09 at 03:27
  • Have you restarted the server since you updated the configuration? – Dana the Sane Apr 17 '09 at 03:27
  • I restarted earlier today.. but I didn't modify the server, I just updated the script to use 3300 instead of 3000 (that was a mistake in the code). – Ian Apr 17 '09 at 03:31
  • I just noticed something weird.. [root@baster httpd]# mysql -uroot -p -P666 Enter password: mysql> Apparently mysql ignores my port number.. :| – Ian Apr 17 '09 at 03:32
  • I think that unless you specify a host, it connects via unix socket by default. Try using -h or --host as above, this should also make the command fail. – Dana the Sane Apr 17 '09 at 03:34
  • [root@baster httpd]# mysql -uroot -p -P3000 -h=localhost Enter password: ERROR 2005 (HY000): Unknown MySQL server host '=localhost' (2) [root@baster httpd]# Aha. – Ian Apr 17 '09 at 03:38
  • Cancel that. I'm stupid. [root@baster httpd]# mysql -uroot -p -P3300 -hlocalhost Enter password: mysql> – Ian Apr 17 '09 at 03:39
  • I checked my.conf, "skip-networking" isn't there either. This is so weird. – Ian Apr 17 '09 at 03:41
  • Have you tried to connect with your code again, using port 3300? – Dana the Sane Apr 17 '09 at 03:42
  • Yeah, doesn't work.. I did get it to work by putting in the unix_socket however.. but this is really weird that it wont work outside of the socket.. boo. – Ian Apr 17 '09 at 03:44
  • Yeah, probably, I'm new to linux in general, so sockets are a bit strange for me.. I'm used to doing everything over TCP/IP. – Ian Apr 17 '09 at 03:53
  • Yup! Just found out. Mysql ignores custom ports and goes to socket mode if you use "localhost" and not "127.0.0.1". http://www.reddit.com/r/Python/comments/8d4ic/im_new_to_python_wheres_the_best_placeforum_to/c08wy9s – Ian Apr 17 '09 at 12:17
2

Mysql uses sockets when the host is 'localhost' and tcp/ip when the host is anything else. By default Mysql will listen to both - you can disable either sockets or networking in you my.cnf file (see mysql.com for details).

In your case forget about the port=3000 the mysql client lib is not paying any attention to it since you are using localhost and specify the socket as in unix_socket='path_to_socket'.

If you decided to move this script to another machine you will need to change this connect string to use the actual host name or ip address and then you can loose the unix_socket and bring back the port. The default port for mysql is 3306 - you don't need to specify that port but you will need to specify 3000 if that is the port you are using.

Frank Flynn
  • 164
  • 2
0

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 ;) )

Ch'marr
  • 1,284
  • 11
  • 8
  • This is completely wrong. If you try to connect to `127.0.0.1` you will connect via TCP/IP. If you try to connect to `localhost`, you will connect via unix sockets. The default socket for MySQL is usually `/tmp/mysql.sock`, but if your socket is somewhere else, you have to specify where it is using `unix_socket='/path/to/mysql.sock` – daviewales Jun 28 '13 at 07:56
  • Note: this also depends on which library you do use. – ibre5041 Apr 27 '21 at 11:22
-2

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

David Z
  • 128,184
  • 27
  • 255
  • 279
  • Just tried that, it doesn't like None, so I did '' instead and now it complains that '' isn't a valid socket. weird, it's like it only wants to connect via socket.. – Ian Apr 17 '09 at 03:11