MySQL hell... can't connect to database /tmp/mysql.sock

Question

Update: I've STOPPED the older mysql process which was running and causing some confusion. Now I think I have only the newer (5.1.40) version running. BUT, it's pointing to the wrong data file. It's pointing to a default install data file and I'd like it to point to the existing data file in /var/mysql. Here's a portion of /etc/my.cnf

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = /var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/mysql/mysql.sock

this is pointing to the older mysql.sock. I can't seem to find in the directory tree of the newer MySQL install?!? unless it's somewhere obscure.

Anyone help? Basically I installed a newer MySQL and now need to get this new version to launch with my existing data. And sort out this mysql.sock thing....

---

I've recently updated MySQL on Mac OS X Server and am having a hell of a time connecting to it from a rails app. or consistently from the command-line for that matter.

i'm sure this is an obvious error on my part but I only have moderate command-line experience so hoping someone can help...

also related is my rails app no longer can connect. Failing to connect via /tmp/mysql.sock but I'm not sure why it's looking there because there is no mysql.sock in /tmp and I don't know what/where it should be....

Edit: adding results from mysql_config --sockets

$ mysql_config --sockets
Usage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
        --cflags         [-I/usr/local/mysql/include  -g -Os -arch ppc -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL]
        --include        [-I/usr/local/mysql/include]
        --libs           [-arch ppc  -L/usr/local/mysql/lib -lmysqlclient -lz -lm     -lmygcc]
        --libs_r         [-arch ppc  -L/usr/local/mysql/lib -lmysqlclient_r -lz -lm     -lmygcc]
        --plugindir      [/usr/local/mysql/lib/plugin]
        --socket         [/tmp/mysql.sock]
        --port           [0]
        --version        [5.1.40]
        --libmysqld-libs [-arch ppc  -L/usr/local/mysql/lib -lmysqld -ldl  -lz -lm       -lmygcc]

Edit2 which mysql_config

$ which mysql_config
/usr/local/mysql/bin/mysql_config

Lavenialaver

Not to be a complete idiot, but I've gotta ask it...are you sure you've run mysql.server start from the command line? – Idalia

never overestimate me ;-) i'll check... ok. command returns with: -bash: mysql.server: command not found. I CAN connect to the 'old' database..and there's an mysqld process so I assume one of 'em's running – Lavenialaver

Answer 1

You are trying to use different sockets for the server and client. Your Rails is trying to connect to /tmp/mysql.sock, by MySQL is listening on /var/mysql/mysql.sock.

Normally MySQL configuration is stored in /etc/my.cnf, but in your ps your output I see socket path is given as a parameter. So really depends on your system's specifics.

Anyway, look in /etc/my.cnf and your database.yml and make sure mysql.sock appears at the same path in both files.

Piquant

so, how can I find which socket to use? can I list them? I'm assuming I want to connect to the one associated with the newer version of MySQL. – Lavenialaver

And the newer one installed under /usr/local/mysql? It looks to me you are not running this instance. – Piquant

ok. this helped alot. i think what's going on is the Admin tools for OS X Server are keeping my old mysql alive even when i've thought it had been "dead". looking into how to handle that now... – Lavenialaver

The default location switched for the .sock file between 10.5 and 10.6. Either symlink the new file to the old location, or fix your (ruby|php|mysql) configuration appropriately. – Timbal

Sorry, but I can't even find the new mysql.sock file so I can't symlink to it. This particular server is running OS X 10.5.8 (if that matters). – Lavenialaver

etc/my.cnf clearly sets the socket to: socket = /var/mysql/mysql.sock but for whatever reason typing $ mysql says it can't connect via /tmp/mysql.sock. where is this getting set? perhaps the etc/my.cnf is for the older MySQL. how can I locate the my.cnf that the newer version of MySQL I recently installed is looking for? – Lavenialaver

That's configuration for your old instance. Check whether you have /usr/local/etc/my.cnf or anywhere under /usr/local/mysql. – Piquant

Also, it looks to me that you are running your new server from inside build directory. Do you think you should do something like make install first? – Piquant

Answer 2

Not sure whether this will completely solve your problem (you may need to fix the socket path as suggested by cababunga), but try 127.0.0.1 to bypass the socket connection and establish a TCP one.

Repetitious

could you please give me an example of what you mean? i'm really not a master of the command line. i know enough to get around...and get into trouble (obviously) but not sure what you mean by 127.0.0.1 which i know only as the IP of the localhost. – Lavenialaver

@Lavenialaver I mean specifying host: 127.0.0.1 in the yml file – Repetitious

Answer 3

The function real_connect in the Ruby MySQL module actually takes a bunch of parameters:

real_connect(host,user,password,db,port,socket,flags)

The one you need to change is the "socket" parameter. On my Python apps on Mac OS X, I have to set that socket parameter to be /var/mysql/mysql.sock.

So notice, it's not the host you need to change, it's the actual socket. Is there a socket: parameter you can use in your config?

So here's what you can do...

Run the command mysql_config --socket on your command line in a terminal in OS X and it should return you the value of the "socket" you need to use (probably /var/mysql/mysql.sock).

Then in your database.yml file, make sure you add the line:

socket: /var/mysql/mysql.sock

I've mostly run into this via Python on OS X and fixed it in a similar fashion, but I'm going to guess that Ruby will give you the same issue.

Stapleton

thanks for the help! so mysql_config --socket returns with /tmp/mysql.sock. But there is NO "mysql.sock" in my /tmp directory?!? – Lavenialaver

Was MySQL up and running when you did the check? I believe the file won't be there unless it is. You could also try this: mysql -e \"show variables like 'socket'\" and look for the socket parameter (don't forget to add username/password params if you need them) – Stapleton

ok. now I have managed to stop the old MySQL and get the newer version running! am now able to get something back from mysql_config --socket (see above)/ But again it says the socket is /tmp/mysql.sock but there is no file of such name in /tmp?!? So not sure what's going on. – Lavenialaver

Just out of curiosity, are you sure that the mysql_config command you're running is the one for the new database and not the old? You might want to do a which mysql_config on the command line and double-check. – Stapleton

I think personally I'd just try both socket: /tmp/mysql.sock and socket: /var/mysql/mysql.sock in the .yml file (one at a time) and see if either one works. – Stapleton

it appears to be the new mysql_config... i'm now wondering if maybe the new mysql is not pointing to the right data file?!? – Lavenialaver