Thursday, August 03, 2006

Python can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock

A few months ago, when i am trying on learning python, i found a problem on connection between python and MySQL database. I got an error code like this below

Traceback (most recent call last):
File "./mysql_01.py", line 7, in ?
db = MySQLdb.connect(host="localhost", user="nobody",
passwd="nobody", db="qestar")
File "/usr/lib/python2.4/site-packages/MySQLdb/__init__.py", line 66,
in Connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
134, in __init__
super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2002, "Can't connect to local MySQL
server through socket '/var/run/mysqld/mysqld.sock' (2)")


For a note :
(1) MySQL Server had been activated, running, and when i am trying to get on the database via MySQL command shell with that user and password it's success.
(2) I've test using the other application that made with php using the same user and password and then no problem.
(3) python2.4-mysqldb package had been installed

My previous python source code look like this below

#!/usr/bin/python

# import MySQL module
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="nobody", passwd="nobody", db="qestar")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute("SELECT * FROM t_member_transaction")

# get the resultset as a tuple
result = cursor.fetchall()

# iterate through resultset
for record in result:
print record[0] , "-->", record[1]


When i'am using IDLE it's the same result:

IDLE 1.1.2
>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost", user="nobody", passwd="nobody", db="qestar")

Traceback (most recent call last):
File "", line 1, in -toplevel-
db = MySQLdb.connect(host="localhost", user="nobody",
passwd="nobody", db="qestar")
File "/usr/lib/python2.4/site-packages/MySQLdb/__init__.py", line 66,
in Connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
134, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2002, "Can't connect to local MySQL server through
socket '/var/run/mysqld/mysqld.sock' (2)")
>>>


So, is there any clue to explain this? Ok. I'll try to explain.

If we are looking on this :
_mysql_exceptions.OperationalError: (2002, "Can't connect to local MySQL
server through socket '/var/run/mysqld/mysqld.sock' (2)")


It's mean that MySQLdb module in Linux, if connecting to MySQL Server in the same computer, it's look like using "unix socket" instead of 127.0.0.1 tcp stack. I think this is for efficiency and speed. "address" on unix socket usually placed base on file, so the other process who need connect can read and know. Of course in the assume that the file address is in the same place.

It may MySQLdb didn't find it on "/var/run/mysqld/mysqld.sock". This can be happen if we are compiling MySQL our self or MySQLdb compiled with include file mysql whereas did not same as the MySQL server running. If it's the problem you can passing parameter "unix_socket" to the connection:

db = MySQLdb.connect(host="localhost", user="nobody", passwd="nobody", db="qestar", unix_socket="/var/lib/mysql/mysql.sock")

Read my previous article about Where is my MySQL socket? So, the source code would be look like this below.

#!/usr/bin/python

# import MySQL module
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="nobody", passwd="nobody", db="qestar", unix_socket="/tmp/mysql.sock")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute("SELECT * FROM t_member_transaction")

# get the resultset as a tuple
result = cursor.fetchall()

# iterate through resultset
for record in result:
print record[0] , "-->", record[1]


Thank you to Dody Suria Wijaya for helping me on solving the problem. Happy python coding. Cheers.

1 comment:

Shaddi said...

Thanks for the post, helped me solve this issue.