Can't connect to local MySQL server through socket '/tmp/mysql.sock'

[GaryAshford]

Hi.
Lots of people have had a go at addressing the error you get when trying to connect to MySQL from a Linux machine, but none of the answers seem to apply when trying to connect to a REMOTE server via the MyODBC driver.
Why should you get this message when the ODBC DSN in the .odbc.ini file clearly refers to a remote machine, e.g.

[mysql]
driver = /usr/local/lib/libmyodbc.so
server = 194.131.70.164
description = MyODBC 3.51 -> MySQL 4.1
port =
user = gashford
password =
database = mysql

In this situation, what has /tmp/mysql.sock got to do with anything? Windows and Mac clients can connect to the same MySQL server (also running on Linux) without any problems.
I guess there must be a permissions issue somewhere on the client machine which is preventing the ODBC driver from making the connection but I cannot find it. (There is no mysql.sock on the client machine- possibly because this is something you only get on the server??)
Whenever I try connecting using the odbctest utility I get the following error:
[MySQL][MyODBC 3.51 Driver]Can't connect to local MySQL server through socket /tmp/mysql.sock' (2), SQLSTATE = HY000

Any help would be appreciated

 

[GaryAshford]

Hi again. Here I go answering my own question- but for anyone who is having the same problem, I now have some further information on this.

Every site i've been to with regard to the "Can't connect to local MySQL Server" problem seems to imply that the user is doing something wrong, where clearly they are not. The symptoms of a bigger problem are when you have tried the all of the following without success:
-You've installed iODBC or unixODBC on your Linux box.
-Installed one or more ODBC drivers.
-Set your ODBCINI and ODBCSYSINI environment variables.
-Checked that your /etc/odbc.ini file (or ~/.odbc.ini file) is present and the contents look valid.
-You are able to use the odbctest utility and enter '?' to view a list of DSNs contained in your odbc.ini file.
-You can use odbctest with a suitable connection string and successfully connect to the DSN! e.g. DSN=myodbc3;UID=myname;PWD=passwd
-You have tried removing ALL SPACE CHARACTERS from your test DSN, e.g. SERVER=192.167.20.123
-Yet when you try to use a third party ODBC compliant application, you still get the error when you try to connect.

This problem is reproduceable under RedHat9 and Suse9 OSs and possibly others, with unixODBC-2.2.10.
I solved the problem by downloading the source code distribution and recompiling libodbc.so.1.0.0 and libodbcinst.so.1.0.0 and replacing the files.
Bottom line is that the rpm distribution of unixODBC doesn't seem to work on newer versions of Linux/ using newer ODBC drivers.

If you are using libiodbc-3.51.2 (libiodbc.so.2.1.9) and are constantly getting the error "Data source name not found and no default driver specified", this is indicative of a another problem with iODBC. I tried the same approach- downloading and recompiling the driver manager from the source code but failed to solve the problem. it looks like iODBC (3.51 and 3.52 versions) do not work under RedHat9 and SuSe9 OSs. It is possible to list DSNs and log on using the iodbctest utility but when you run a third party application, you get the error- even when running the application from the same terminal window you just used with iodbctest! Perhaps this is why iodbc is no longer shipped with Suse operating systems(?)
Bottom line again here is that you have to use unixODBC on newer versions of Linux, and you need you compile libodbc.so.1.0.0 from the source code.

Hope this helps

 

[GaryAshford]

PS) Needless to say, the error has nothing to do with /tmp/mysql.sock error message. Sockets can only be used to connect to a local MySQL server (as I understand it) and do not apply when connecting to a remote MySQL server. You should definitely not get this error when your DSN contains the keyword pair,
SOCKET= <--implying that you want to use a TCP/IP connection.