Missing mysql.sock; yielding OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")

Question

Firstly, I swear that I have looked at every single question that references this error. Nearly every solution someone offers is different, and no one seems to understand the systemic reasons for the error. What I, and many people on the web who encounter this common problem, need, is an explanation of what is actually going wrong.

Basically, when I try to run:

python manage.py shell
from django.db import connection
cursor = connection.cursor()

with Django 1.4 and python 2.7.3, I get the following error: OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")

I believe that my settings.py file is correct. And everything I need installed is installed. Here is the settings.py:

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
    'NAME': 'database_name.mwb',                      # Or path to database file if using sqlite3.
    'USER': 'user',                      # Not used with sqlite3.
    'PASSWORD': 'PASS',                  # Not used with sqlite3.
    'HOST': '/tmp/mysql.sock',                      # Set to empty string for localhost. Not used with sqlite3.
    'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
}}

After suffering through weeks of scouring the web for answers, including every one on stackoverflow, I have come to the conclusion that the real issue I am experiencing is that there no mysql.sock file on my computer, even though mysql is installed. So, the real question is how do I get one? The answer may be embarrassingly simple.

When I run:

cd /
sudo find . -name ".sock"

I don't get anything. And yes, I have installed mysql 5.25 on my mac osx lion. It's definitely installed:

sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysql.plist
com.mysql.mysqld: Already loaded

However, running:

mysql

yields:

-bash: mysql: command not found

After a long time, I found this bug: http://bugs.mysql.com/bug.php?id=4174 .. so what's the solution??

Full disclosure: I'm just a programmer. I really don't know anything about computers. So, I don't know anything about mysql, databases, or sockets. I think this question will require somebody who really understands computers to fix. As, though it seems obvious now, it has taken a long time to formulate this error as being from a missing socket. 99% of people on the web with this error do not seem to be approaching it this way.

Piaffe

Where is the connection type specified? MySQL can connect either through a file socket or through a socket that accepts connections via a port. I don't use Django so I can't help you much more, but I'd look for the settings that control what database Django connects to and change the URI. – Sago

Ah' I just edited to include the settings file. What do you think? Do they look wrong? – Piaffe

Well, 'HOST': '/tmp/mysql.sock' isn't working. You need to provide something like mysql://{username}:{password}@{host}/{database}. As I don't use Django, I have no idea what format Django expects. – Sago

Are you sure that's the source of it not working? Would the following be what you are talking about: "mysql://user:pass@/tmp/mysql.sock/whats_in_my_fridge.mwb"? – Piaffe

I got the same problem. I solved it just now. #18151358 – Hourly

Answer 1

You have the mysql client installed but not the mysql-server that handles connections for the client. You need to install the mysql server and run it.

Crossexamine

Why is this never mentioned in any installation instructions? – Piaffe

So, I need to install "mysql server"? Any additional information needed? – Piaffe

Is this the correct link to the files? dev.mysql.com/downloads/mysql Because I've already installed that. – Piaffe

You're on a mac right ? Use brew. Get it and then use it to install mysql. Should work perfectly :) – Crossexamine

Brew is not a python app. Follow the instructions here: github.com/mxcl/homebrew/wiki/installation – Crossexamine

Is brew just a better macports? Why use something for .dmgs that I can just double-click? – Piaffe

Hmmm. Okay, I'll give it a go. I'll report back. If it doesn't work, I'll sue ;) – Piaffe

No good Ashray, I can't start my server: $ sudo /usr/local/mysql/support-files/mysql.server start Password: Starting MySQL .. ERROR! The server quit without updating PID file (/...local.pid). – Piaffe

Answer 2

As far as I can tell, it is either of the two things:

1. You MySQL Server is not running, or
2. It is running, but is not configured to use /tmp/mysql.sock as its socket

As far as 2 is concerned, I believe that is the default setting for a freshly installed MySQL, but it doesn't hurt to check. Try looking at the contents of /etc/my.cnf. Check whether there is a line that looks like this: socket=/path/to/socket - where /path/to/socket is, as it says the file path of the socket. If there is such an entry and the file path is different than /tmp/mysql.sock, you've found your problem. Change either that line, or your Django config so they match.

Note: In case you don't find /etc/my.cnf you can create it yourself and add the appropriate socket settings (i.e. just add the line socket=/tmp/mysql.sock) just to make sure it's properly configured.

As far as 1 is concerned, you can follow the instructions available here and make sure your MySql server is running.

Good luck!

Wilonah

Thanks for the answer! So, initially I had a blank my.cnf or didn't have one at all (can't remember). So I edited that file like you said, with the following: [client] socket=/tmp/mysql.sock [mysqld] socket=/tmp/mysql.sock As for #1, I definitely went through the installer you are linking to. Several times. Not sure what else I could do to install it, a .dmg is pretty fool proof. – Piaffe

Does it matter that there is no actual "mysql.sock" file? Also, all of the folder tree elements that are mysql are definitely there, exactly as the readme says (/usr/local/mysql<version), with a symlink to the same spot. – Piaffe

Hmmm, I'm not quite sure then. Maybe you could try using IP instead of sockets. Just in case try 127.0.0.1 as HOST and 3306 as PORT, which are the default settings for a freshly installed mysql. – Wilonah

Okay, I'll try this and let you know how it goes. Thank you. – Piaffe

Yes! We are definitely moving forward! I now get an access denied error, which seems much more benign: OperationalError: (1045, "Access denied for user 'mhalpin0'@'localhost' (using password: YES)"). So, the question is why it claims I used that password... so, if you or anyone has any suggestions, they will be most welcome. However, I will begin researching. – Piaffe

It's not saying that you used that password, it's just saying that you used a password. Did you setup a user in your MySQL? You could try username root without a password, since The Mac mysql installation includes by default a user named root with no password – Wilonah

We are moving forward. With the root username, I think I finally got an error that is the source of the new problem: OperationalError: (1049, "Unknown database 'whats_in_my_fridge.mwb'"). So... How do I create a database from a terminal? Because whatever I have done is no good. – Piaffe

Answer 3

As I answered here:

with

mysql_config --socket

I found out where mysql socket is, I apply it:

'HOST': '/Applications/MAMP/tmp/mysql/mysql.sock',

Skeen

Same issue occurs when you apply the socket location to the host input of the MySQL connection. New error message, not error code: OperationalError: (2005, "Unknown MySQL server host '/tmp/mysql.sock' (0)") – Lament