[Cod-bugs] cannot connect through mysql

[Keeble, Dean (DLSLtd,RAL,LSCI)]

Hi Saulius,

Thanks for your fast and helpful reply! You're correct, that port is filtered for external connections. I have requested with our IT team to add an exception to allow this.

All the best,
Dean

$ nmap -p 3306 sql.crystallography.net

Starting Nmap 6.40 ( http://nmap.org ) at 2022-04-13 11:18 BST
Nmap scan report for sql.crystallography.net (158.129.170.75)
Host is up (0.048s latency).
PORT     STATE    SERVICE
3306/tcp filtered mysql

________________________________
From: Saulius Gražulis <grazulis at ibt.lt>
Sent: 13 April 2022 11:09
To: Keeble, Dean (DLSLtd,RAL,LSCI) <dean.keeble at diamond.ac.uk>; cod-bugs at ibt.lt <cod-bugs at ibt.lt>
Subject: Re: [Cod-bugs] cannot connect through mysql

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<http://www.mailscanner.info/>, and is
believed to be clean.

-- 
This e-mail and any attachments may contain confidential, copyright and or privileged material, and are for the use of the intended addressee only. If you are not the intended addressee or an authorised recipient of the addressee please notify us of receipt by returning the e-mail and do not use, copy, retain, distribute or disclose the information in or attached to the e-mail.
Any opinions expressed within this e-mail are those of the individual and not necessarily of Diamond Light Source Ltd. 
Diamond Light Source Ltd. cannot guarantee that this e-mail or any attachments are free from viruses and we cannot accept liability for any damage which you may sustain as a result of software viruses which may be transmitted in or with the message.
Diamond Light Source Limited (company no. 4375679). Registered in England and Wales with its registered office at Diamond House, Harwell Science and Innovation Campus, Didcot, Oxfordshire, OX11 0DE, United Kingdom


-- 
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/9c2f8454/attachment.htm>