[Cod-bugs] cannot connect through mysql

[Saulius Gražulis]

Dear Dean,

thank you for your inquiry!

On 2022-04-13 12:33, Keeble, Dean (DLSLtd,RAL,LSCI) wrote:
> I'm investigating how to perform some searches in the COD
> programmatically via the mysql interface. In following the example
> online <https://wiki.crystallography.net/howtoquerycod/>I'm not
> managing to connect, however. Do you have any advice on how I might
> proceed? Below I show results of a ping (success), the mysql (with the
> error) and my mysql version.
>
> Many thanks in advance for your help!
>
> All the best,
> Dean
>
> $ ping -c 1 sql.crystallography.net
> PING sql.crystallography.net (158.129.170.75) 56(84) bytes of data.
> 64 bytes from 158.129.170.75 (158.129.170.75): icmp_seq=1 ttl=46
> time=47.7 ms
>
> --- sql.crystallography.net ping statistics ---
> 1 packets transmitted, 1 received, 0% packet loss, time 0ms
> rtt min/avg/max/mdev = 47.777/47.777/47.777/0.000 ms
>
> $ mysql -u cod_reader -h sql.crystallography.net -e 'select count(*)
> from data where flags like "%has Fobs%"' cod | cat
> ERROR 2003 (HY000): Can't connect to MySQL server on
> 'sql.crystallography.net' (110)
>
> $ mysql --version
> mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using
> readline 5.1

All your commands seem OK. I have tested your SQL query on my machine,
and it works as expected from my location:

> saulius at tasmanijos-velnias 2022-USNC-Cr-Database-Workshop-III/ $ mysql
> -u cod_reader -h sql.crystallography.net -e 'select count(*) from data
> where flags like "%has Fobs%"' cod | cat
> count(*)
> 57568

As Andrius pointed out, the reason why the command does not work for you
might be a firewall.

I could add to that that need the TCP port 3306 open to be able to
access mysql directly:

> saulius at tasmanijos-velnias 2022-USNC-Cr-Database-Workshop-III/ $ grep
> mysql /etc/services
> mysql        3306/tcp

Please check with your site admin if they have blocked that port (if you
are in Diamond, this could be the case – the sysadmins are simtimes be
extra-cautious at large facilities...); if they have, maybe they can
open the port for you. In addition to that, you may want to check the
same command though your home Internet provide or via you mobile phone.

HTH,
Saulius

-- 
Dr. Saulius Gražulis
Vilnius University, Life Science Center, Institute of Biotechnology
Saulėtekio al. 7, LT-10257 Vilnius, Lietuva (Lithuania)
phone (office): (+370-5)-2234353, mobile: (+370-684)-49802, (+370-614)-36366


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20220413/b5b50367/attachment-0001.htm>