From dean.keeble at diamond.ac.uk Wed Apr 13 12:33:34 2022 From: dean.keeble at diamond.ac.uk (Keeble, Dean (DLSLtd,RAL,LSCI)) Date: Wed, 13 Apr 2022 09:33:34 +0000 Subject: [Cod-bugs] cannot connect through mysql Message-ID: Hi, I'm investigating how to perform some searches in the COD programmatically via the mysql interface. In following the example online 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 Dean S. Keeble Data Analysis Scientist - Diffraction Diamond Light Source, Harwell Campus, Oxon., OX11 0DE. -- 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: From andrius.merkys at gmail.com Wed Apr 13 12:42:32 2022 From: andrius.merkys at gmail.com (Andrius Merkys) Date: Wed, 13 Apr 2022 12:42:32 +0300 Subject: [Cod-bugs] cannot connect through mysql In-Reply-To: References: Message-ID: <6d93d693-4fba-ee14-8d15-a58496365e4e@gmail.com> Hi Dean, On 2022-04-13 12:33, Keeble, Dean (DLSLtd,RAL,LSCI) wrote: > $ 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 Your command worked fine for me on both the following set ups: $ mysql --version mysql Ver 14.14 Distrib 5.7.37, for Linux (x86_64) using EditLine wrapper $ mysql --version mysql Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper Could it be that your firewall settings block mysql connections (port 3306)? Are you able to connect to any other remote MySQL server? Hope this helps, Andrius -- Andrius Merkys Vilnius University Institute of Biotechnology, Saul?tekio al. 7 LT-10257 Vilnius, Lithuania -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. From grazulis at ibt.lt Wed Apr 13 13:09:13 2022 From: grazulis at ibt.lt (=?UTF-8?Q?Saulius_Gra=c5=beulis?=) Date: Wed, 13 Apr 2022 13:09:13 +0300 Subject: [Cod-bugs] cannot connect through mysql In-Reply-To: References: Message-ID: <14d60d26-0a3e-218b-49a9-a09abd73da3e@ibt.lt> 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 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: From dean.keeble at diamond.ac.uk Wed Apr 13 13:21:14 2022 From: dean.keeble at diamond.ac.uk (Keeble, Dean (DLSLtd,RAL,LSCI)) Date: Wed, 13 Apr 2022 10:21:14 +0000 Subject: [Cod-bugs] cannot connect through mysql In-Reply-To: <14d60d26-0a3e-218b-49a9-a09abd73da3e@ibt.lt> References: <14d60d26-0a3e-218b-49a9-a09abd73da3e@ibt.lt> Message-ID: 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 Sent: 13 April 2022 11:09 To: Keeble, Dean (DLSLtd,RAL,LSCI) ; 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 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. -- 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: From hansonr at stolaf.edu Wed Apr 13 17:31:58 2022 From: hansonr at stolaf.edu (Robert Hanson) Date: Wed, 13 Apr 2022 09:31:58 -0500 Subject: [Cod-bugs] Optimade question In-Reply-To: References: Message-ID: [cod-bugs -- see below. I had the wrong address] On Tue, Apr 12, 2022 at 7:58 AM Robert Hanson wrote: > Matthew, > > VERY much enjoyed your MADICES talk. Thank you for that and all your > brilliant work on OPTIMADE. > > I have a question for you. When I issue this request: > > > https://www.crystallography.net/cod/optimade/structures?filter=nelements=6&page_limit=2 > > I get a circular reference: > > [image: image.png] > > with > https://www.crystallography.net/cod/optimade/v1.1.0/structures/1000096 > having the same link -- to itself! > > I was hoping for a reference to a cif file. :) > > Q: Is this the way OPTIMADE is supposed to work? Or is there a problem > with COD's implementation? > > Hope to hear from you soon, > > Bob Hanson > > cc COD bug report e-mail > > > > > > -- > Robert M. Hanson > Professor of Chemistry > St. Olaf College > Northfield, MN > http://www.stolaf.edu/people/hansonr > > > If nature does not answer first what we want, > it is better to take what answer we get. > > -- Josiah Willard Gibbs, Lecture XXX, Monday, February 5, 1900 > > *We stand on the homelands of the Wahpekute Band of the Dakota Nation. We > honor with gratitude the people who have stewarded the land throughout the > generations and their ongoing contributions to this region. We acknowledge > the ongoing injustices that we have committed against the Dakota Nation, > and we wish to interrupt this legacy, beginning with acts of healing and > honest storytelling about this place.* > -- Robert M. Hanson Professor of Chemistry St. Olaf College Northfield, MN http://www.stolaf.edu/people/hansonr If nature does not answer first what we want, it is better to take what answer we get. -- Josiah Willard Gibbs, Lecture XXX, Monday, February 5, 1900 *We stand on the homelands of the Wahpekute Band of the Dakota Nation. We honor with gratitude the people who have stewarded the land throughout the generations and their ongoing contributions to this region. We acknowledge the ongoing injustices that we have committed against the Dakota Nation, and we wish to interrupt this legacy, beginning with acts of healing and honest storytelling about this place.* -- 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: -------------- next part -------------- A non-text attachment was scrubbed... Name: image.png Type: image/png Size: 20010 bytes Desc: not available URL: From andrius.merkys at gmail.com Wed Apr 13 17:40:29 2022 From: andrius.merkys at gmail.com (Andrius Merkys) Date: Wed, 13 Apr 2022 17:40:29 +0300 Subject: [Cod-bugs] Optimade question In-Reply-To: References: Message-ID: <5d6c2f77-c40a-34f6-dd3d-534c9225303e@gmail.com> Hello, On 2022-04-13 17:31, Robert Hanson wrote: > On Tue, Apr 12, 2022 at 7:58 AM Robert Hanson > wrote: > I have a question for you. When I issue this request: > > https://www.crystallography.net/cod/optimade/structures?filter=nelements=6&page_limit=2 > > > I get a circular reference: > > image.png > > with > https://www.crystallography.net/cod/optimade/v1.1.0/structures/1000096 > > having the same link -- to itself! Yes, this is intentional. "self" is supposed to be a link to the record itself per OPTIMADE specification, I believe. > I was hoping for a reference to a cif file. :) > > Q: Is this the way OPTIMADE is supposed to work? Or is there a > problem with COD's implementation? Support for links to files is not yet in the OPTIMADE specification. It has a long standing feature request, though [1]. [1] https://github.com/Materials-Consortia/OPTIMADE/issues/211 Best wishes, Andrius -- Andrius Merkys Vilnius University Institute of Biotechnology, Saul?tekio al. 7 LT-10257 Vilnius, Lithuania -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. From hansonr at stolaf.edu Wed Apr 13 17:54:00 2022 From: hansonr at stolaf.edu (Robert Hanson) Date: Wed, 13 Apr 2022 09:54:00 -0500 Subject: [Cod-bugs] Optimade question In-Reply-To: <5d6c2f77-c40a-34f6-dd3d-534c9225303e@gmail.com> References: <5d6c2f77-c40a-34f6-dd3d-534c9225303e@gmail.com> Message-ID: Thank you Andrius. Glad to see that that revised link worked. Interesting question -- clearly an advantage to *not* sending the data inline or having to convert from such a good primary standard as CIF. I will vote that one up! BTW, Andrius, the IUPAC FAIRSpec standard is really coming along. I think this could be of interest to you. VERY preliminary spec: https://docs.google.com/document/d/1BBxdcpBtzsIzhsUb54GCYsX55qD5_emqKRl_xg1PbiA/edit?usp=sharing sample finding aids: https://chemapps.stolaf.edu/iupac/site/ifd2/ On Wed, Apr 13, 2022 at 9:40 AM Andrius Merkys wrote: > Hello, > > On 2022-04-13 17:31, Robert Hanson wrote: > > On Tue, Apr 12, 2022 at 7:58 AM Robert Hanson > > wrote: > > I have a question for you. When I issue this request: > > > > > https://www.crystallography.net/cod/optimade/structures?filter=nelements=6&page_limit=2 > > < > https://www.crystallography.net/cod/optimade/structures?filter=nelements=6&page_limit=2 > > > > > > I get a circular reference: > > > > image.png > > > > with > > > https://www.crystallography.net/cod/optimade/v1.1.0/structures/1000096 > > < > https://www.crystallography.net/cod/optimade/v1.1.0/structures/1000096> > > having the same link -- to itself! > > Yes, this is intentional. "self" is supposed to be a link to the record > itself per OPTIMADE specification, I believe. > > > I was hoping for a reference to a cif file. :) > > > > Q: Is this the way OPTIMADE is supposed to work? Or is there a > > problem with COD's implementation? > > Support for links to files is not yet in the OPTIMADE specification. It > has a long standing feature request, though [1]. > > [1] https://github.com/Materials-Consortia/OPTIMADE/issues/211 > > Best wishes, > Andrius > > -- > Andrius Merkys > Vilnius University Institute of Biotechnology, Saul?tekio al. 7 > LT-10257 Vilnius, Lithuania > -- Robert M. Hanson Professor of Chemistry St. Olaf College Northfield, MN http://www.stolaf.edu/people/hansonr If nature does not answer first what we want, it is better to take what answer we get. -- Josiah Willard Gibbs, Lecture XXX, Monday, February 5, 1900 *We stand on the homelands of the Wahpekute Band of the Dakota Nation. We honor with gratitude the people who have stewarded the land throughout the generations and their ongoing contributions to this region. We acknowledge the ongoing injustices that we have committed against the Dakota Nation, and we wish to interrupt this legacy, beginning with acts of healing and honest storytelling about this place.* -- 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: From andrius.merkys at gmail.com Thu Apr 14 10:39:43 2022 From: andrius.merkys at gmail.com (Andrius Merkys) Date: Thu, 14 Apr 2022 10:39:43 +0300 Subject: [Cod-bugs] Optimade question In-Reply-To: References: <5d6c2f77-c40a-34f6-dd3d-534c9225303e@gmail.com> Message-ID: <4a2ec243-c96d-1fb8-bbf3-c5a611142f48@gmail.com> Hi Robert, On 2022-04-13 17:54, Robert Hanson wrote: > Thank you Andrius. Glad to see that that revised link worked. > Interesting question -- clearly an advantage to /not/ sending > ?the data inline or having to convert from such a good primary standard > as CIF. I will vote that one up! Thank you for support! > BTW, Andrius, the IUPAC FAIRSpec standard is really coming along. I > think this could be of interest to you. Thanks for an interesting link - I will give it a thorough read. Best wishes, Andrius -- Andrius Merkys Vilnius University Institute of Biotechnology, Saul?tekio al. 7 LT-10257 Vilnius, Lithuania -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.