can not connect to mysql database

Discussion:

Zhenguo Zhang

2011-02-01 02:56:40 UTC

Dear UCSC team,

Sorry to interrupt.

I have installed the tools from
http://hgdownload.cse.ucsc.edu/admin/jksrc.zip successfully. I think some of
the tools need using mysql tables. Since my mysql data is not at the
standard place /var/lib/mysql/, when I ran 'hgLoadSqlTab hg18 chromInfo
chromInfo.sql chromInfo.tab', I got the error message:

Couldn't connect to database hg18 on localhost as ucsc.
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

Here 'ucsc' is the name I specified in ~/.hg.conf for connecting mysql
database. I also tried this user name and am sure it works. My actual place
for mysql.sock is at /home/zhangzg/mysql/mysql.sock, which has been
correctly set in mysql config file ~/.my.cnf.

Now I have no administrator privilege to set a symbol link at
'/var/lib/mysql/mysql.sock'. Is it possible to modify the code in jksrc.zip
to point to the correct place of mysql data? or any advice which does not
need administrator privilege? I have searched the mailing list and
unfortunately I can not find solution for my case.

Thank you in advance!

Best wishes!

Zhenguo

PS: other tools relying on mysql also show the same problems, such as
mafFrags. However, hgsql works. I have no idea why.

Hiram Clawson

2011-02-01 18:12:55 UTC

Permalink

Good Morning Zhenguo:

Please try setting this environment variable in your shell environment:

$ export HGDB_SOCKET=/home/zhangzg/mysql/mysql.sock

--Hiram

Post by Zhenguo Zhang
Dear UCSC team,
Sorry to interrupt.
I have installed the tools from
http://hgdownload.cse.ucsc.edu/admin/jksrc.zip successfully. I think some of
the tools need using mysql tables. Since my mysql data is not at the
standard place /var/lib/mysql/, when I ran 'hgLoadSqlTab hg18 chromInfo
Couldn't connect to database hg18 on localhost as ucsc.
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)
Here 'ucsc' is the name I specified in ~/.hg.conf for connecting mysql
database. I also tried this user name and am sure it works. My actual place
for mysql.sock is at /home/zhangzg/mysql/mysql.sock, which has been
correctly set in mysql config file ~/.my.cnf.
Now I have no administrator privilege to set a symbol link at
'/var/lib/mysql/mysql.sock'. Is it possible to modify the code in jksrc.zip
to point to the correct place of mysql data? or any advice which does not
need administrator privilege? I have searched the mailing list and
unfortunately I can not find solution for my case.
Thank you in advance!
Best wishes!
Zhenguo
PS: other tools relying on mysql also show the same problems, such as
mafFrags. However, hgsql works. I have no idea why.

Zhenguo Zhang

2011-02-01 19:02:33 UTC

Permalink

Hi Hiram,

I tried. It does not work. I am wondering whether there is a way to tell
mysql-related ucsc tools the correct fonfiguration, like mysql reading
config from the user's home directory '~/.my.cnf'. It seems this is
hard-coded in hgLoadSqlTable, hgLoadMaf, etc. Is there any advice? By the
way, I am using the jksrc.zip downloaded in Jan, 2010. Does this matters
with the option HGDB_SOCKET. Thank you in advance.

Have a nice day!

Zhenguo

Post by Hiram Clawson
$ export HGDB_SOCKET=/home/zhangzg/mysql/mysql.sock
--Hiram

--
——————————————————————
Zhenguo Zhang
Postdoctoral Scholar
Institute of Molecular Evolutionary Genetics
Penn State University
312 Mueller Lab, University Park, PA 16802
Tel: 814-865-2796
Homepage: http://www.personal.psu.edu/zuz17/
Lab: http://homes.bio.psu.edu/people/Faculty/Nei/

Hiram Clawson

2011-02-01 19:14:53 UTC

Permalink

You are correct, the kent utilities are not reading ~/.my.cnf

This HGDB_SOCKET option was added in Sept 2010
If you can update your kent source, you can use this option.

The bigger puzzle is that the socket definition is defined
in the MySQL libraries and not in any kent source code.
You can see the socket name from your libmysqlclient.a,
for example:

$ strings /usr/lib64/mysql/libmysqlclient.a | grep mysql.sock
/var/lib/mysql/mysql.sock

--Hiram

Post by Zhenguo Zhang
Hi Hiram,
I tried. It does not work. I am wondering whether there is a way to tell
mysql-related ucsc tools the correct fonfiguration, like mysql reading
config from the user's home directory '~/.my.cnf'. It seems this is
hard-coded in hgLoadSqlTable, hgLoadMaf, etc. Is there any advice? By the
way, I am using the jksrc.zip downloaded in Jan, 2010. Does this matters
with the option HGDB_SOCKET. Thank you in advance.

Zhenguo Zhang

2011-02-01 21:33:52 UTC

Permalink

Hi Hiram,

Thank you very much for your help. Now I set up the tools using the latest
code and set the environment for HGDB_SOCKET. Now it works for hgLoadMaf.
Before setting it, it gave the error message:
'*Couldn't connect to database hg18 on localhost as ucsc.
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)*'.
After setting HGDB_SOCKET, it works.

However, when I want to extract some data with the tool mafFrags, it gave
the following error:
'*ASH: Couldn't connect to database hgcentral on localhost as ucsc. mysql:
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2) pid=10615
ASH: hConnectCentral failed over to backupcentral! pid=10615
can't find profile backupcentral in hg.conf*'.

This seems mafFrags does not recognize the variable HGDB_SOCKET. I really
need mafFrags to manipulate large dataset. Is it possible to change the code
to let it know the place of mysql data as hgLoadMaf does. Thank you a lot.

Best regards!

Zhenguo

Post by Hiram Clawson
You are correct, the kent utilities are not reading ~/.my.cnf
This HGDB_SOCKET option was added in Sept 2010
If you can update your kent source, you can use this option.
The bigger puzzle is that the socket definition is defined
in the MySQL libraries and not in any kent source code.
You can see the socket name from your libmysqlclient.a,
$ strings /usr/lib64/mysql/libmysqlclient.a | grep mysql.sock
/var/lib/mysql/mysql.sock
--Hiram

Hiram Clawson

2011-02-01 21:57:32 UTC

Permalink

In your $HOME/.hg.conf file, add the entry:

central.socket=/your/socket/path/mysql.sock

----- Original Message -----
From: "Zhenguo Zhang" <***@gmail.com>
To: "Hiram Clawson" <***@soe.ucsc.edu>, "genome" <***@soe.ucsc.edu>
Sent: Tuesday, February 1, 2011 1:33:52 PM
Subject: Re: [Genome] can not connect to mysql database

Hi Hiram,

Thank you very much for your help. Now I set up the tools using the latest code and set the environment for HGDB_SOCKET. Now it works for hgLoadMaf. Before setting it, it gave the error message:
' Couldn't connect to database hg18 on localhost as ucsc.
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) '.
After setting HGDB_SOCKET, it works.

However, when I want to extract some data with the tool mafFrags, it gave the following error:
' ASH: Couldn't connect to database hgcentral on localhost as ucsc. mysql: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) pid=10615
ASH: hConnectCentral failed over to backupcentral! pid=10615
can't find profile backupcentral in hg.conf '.

This seems mafFrags does not recognize the variable HGDB_SOCKET. I really need mafFrags to manipulate large dataset. Is it possible to change the code to let it know the place of mysql data as hgLoadMaf does. Thank you a lot.

Best regards!

Zhenguo

Zhenguo Zhang

2011-02-01 22:29:31 UTC

Permalink

Hi Hiram,

It works now after adding central.socket to the ~/.hg.conf file. Thank you a
lot.

Zhenguo

Post by Hiram Clawson
central.socket=/your/socket/path/mysql.sock
----- Original Message -----
Sent: Tuesday, February 1, 2011 1:33:52 PM
Subject: Re: [Genome] can not connect to mysql database
Hi Hiram,
Thank you very much for your help. Now I set up the tools using the latest
code and set the environment for HGDB_SOCKET. Now it works for hgLoadMaf.
' Couldn't connect to database hg18 on localhost as ucsc.
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2) '.
After setting HGDB_SOCKET, it works.
However, when I want to extract some data with the tool mafFrags, it gave
Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2) pid=10615
ASH: hConnectCentral failed over to backupcentral! pid=10615
can't find profile backupcentral in hg.conf '.
This seems mafFrags does not recognize the variable HGDB_SOCKET. I really
need mafFrags to manipulate large dataset. Is it possible to change the code
to let it know the place of mysql data as hgLoadMaf does. Thank you a lot.
Best regards!
Zhenguo