 |
 |
Hi Tatiana, Hmm, "select count" should work, we are routinely running unit tests that include "select count(*)", did you try running it on tables other than DeepCoadd? (which I believe is a view, which might be the culprit). As to "unexpected token /:", I suspect jdbc is adding some type of quoting that our parser is not handling correctly, can you peek into qserv log files and send us the exact query that is causing the problems? Normally Serge would do all that debugging for you, but he is on vacation this week, and the rest of us does not have access to your cluster. It is definitely not John's fault :) Jacek On 11/11/2014 06:00 PM, Tatiana Goldina wrote: > Yes, we figured that whenever select statements (like "select count …") hang, the qserv status shows that "xrootd is dead" and qserv has to be restarted. > > We also confirmed that we can not connect to Qserv's mysql proxy using JDBC. The error we get is > > SQLException: Qserv error: 'ParseException:ANTLR parse error:unexpected token: /:' > > I am including John Rector, who did set up the QSERV for us. He has more details. > > Tatiana > > > On Nov 11, 2014, at 3:59 PM, Jacek Becla <[log in to unmask]> wrote: > >> Tatiana >> >> When you are talking to Qserv, the front end that you are talking to >> is mysql proxy (which looks exactly like standard mysql on the surface). >> Under the hood, we are intercepting each query and do all sorts of >> somewhat uncommon things. The bottom line is that >> "select columns from %table%' from mysql" will not give you what >> is expected, because were is currently not handled >> by qserv/mysqlProxy. >> >> >> Regarding the issue with DeepCoadd, I vaguely recall DeepCoadd >> is a view, and there were some issues with views in the past. >> Looking at the log files from qserv czar and xrootd would help >> understand this problem, do you know where to look? >> >> Jacek >> >> >> >> On 11/11/2014 12:27 PM, Tatiana Goldina wrote: >>> Thank you for a quick reply. >>> >>> I am issuing "select columns from %table%' from mysql command line >>> client. For now this is the only client that I know which can connect >>> to QSERVE mysql proxy. It could be that MySQL Query Browser and >>> IntelliJ (via JDBC driver) fail to connect because in addition to >>> connecting they try to get extra information about the the schema. I'll >>> test it tonight. >>> >>> The other issue is that I can not make any of the select statements >>> work. It appears to hang for a long while, then produces an error. For >>> example, how do I get a row count for a table? >>> >>> mysql> select count(deepCoaddId) from DeepCoadd; >>> ERROR 4120 (Proxy): Error executing query using qserv. >>> >>> Tatiana >>> >>> On Nov 11, 2014, at 10:14 AM, Serge Monkewitz <[log in to unmask] >>> <mailto:[log in to unmask]>> wrote: >>> >>>> Hi Tatiana, >>>> >>>> I’m moving this discussions to the qserv mailing list. There’s a >>>> page on the SQL constructs that qserv supports here: >>>> https://dev.lsstcorp.org/trac/wiki/db/ScalableArchSQL. >>>> >>>> In general, it’s SQL 92 minus sub-queries, and with some spatial >>>> support. That means that MySQL specific syntax usually isn’t >>>> supported. Unfortunately, I’m not aware of a page that lists out the >>>> exact MySQLisms that we do support, but I believe it’s limited to: >>>> >>>> SHOW TABLES >>>> SHOW DATABSES >>>> DESCRIBE >>>> >>>> Are you saying that the JDBC driver itself is issuing SHOW COLUMNS, or >>>> is that initiated on your end? >>>> >>>> Cheers, >>>> Serge >>>> >>>> Begin forwarded message: >>>> >>>>> *From: *Tatiana Goldina <[log in to unmask] >>>>> <mailto:[log in to unmask]>> >>>>> *Subject: **connecting to qserv database* >>>>> *Date: *November 11, 2014 at 9:07:02 AM PST >>>>> *To: *Serge Monkewitz <[log in to unmask] >>>>> <mailto:[log in to unmask]>> >>>>> >>>>> Hi Serge, >>>>> >>>>> John Rector has set a QSERV for us here at IPAC, and we are trying >>>>> to figure out how to access it. >>>>> >>>>> It seems that I can not access it with a MySql GUI client or JDBC >>>>> (jdbc:mysql://lsst-db1.ipac.caltech.edu >>>>> <http://lsst-db1.ipac.caltech.edu/>:4040/LSST) >>>>> >>>>> I am able to access it with command line mysql client. I can get the >>>>> tables with "show tables;" but >>>>> >>>>> mysql> show columns from DeepSource from LSST; >>>>> ERROR 4110 (Proxy): Qserv error: 'ParseException:ANTLR parse >>>>> error:unexpected token: show:' >>>>> >>>>> (The same error that we get when trying to connect with JDBC.) >>>>> >>>>> On the other hand "describe DeepSource;" works. >>>>> >>>>> How do I find out which commands are supported? >>>>> >>>>> Tatiana >>>> >>> >>> >>> ------------------------------------------------------------------------ >>> >>> Use REPLY-ALL to reply to list >>> >>> To unsubscribe from the QSERV-L list, click the following link: >>> https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1 >>> >> >> >> >> -- >> BEGIN-ANTISPAM-VOTING-LINKS >> ------------------------------------------------------ >> >> Teach CanIt if this mail (ID 04NenXkSl) is spam: >> Spam: https://canit.ipac.caltech.edu/canit/b.php?i=04NenXkSl&m=ad12a6f5bb2b&c=s >> Not spam: https://canit.ipac.caltech.edu/canit/b.php?i=04NenXkSl&m=ad12a6f5bb2b&c=n >> Forget vote: https://canit.ipac.caltech.edu/canit/b.php?i=04NenXkSl&m=ad12a6f5bb2b&c=f >> ------------------------------------------------------ >> END-ANTISPAM-VOTING-LINKS >> > ######################################################################## Use REPLY-ALL to reply to list To unsubscribe from the QSERV-L list, click the following link: https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1