 |
 |
Hi Daniel, I've patched partition.py in order to create *ObjectSelfOverlap_chunkid.csv* *ObjectFullOverlap_chunkid.csv* even if they are empty but only if Object_chunkid.csv exists and is not empty, This solved the previous error message (but this patch wasn't required in previous Qserv versions). Here's what i get now when launching the query against mysql proxy: mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o, Source s WHERE o.objectId = 390034570102582 AND o.objectId = s.objectId AND o.latestObsTime = s.taiMidPoint; +-----------------+-------------------+---------+-----------+ | ra | decl | raRange | declRange | +-----------------+-------------------+---------+-----------+ | 1.8100328888038 | -5.50529748780274 | NULL | NULL | | 1.8100328888038 | -5.50529748780274 | NULL | NULL | +-----------------+-------------------+---------+-----------+ 2 rows in set (1 min 52.13 sec) It's very slow, and it return 2 lines (it should only return 1) Furthermore, Qserv dies after answering the query. I've attached log file (i've added some verbose debug messages, but it doesn't seem to have a strong impact on performance for this query) Would you have an idea please ? Have a nice day, Fabrice On 10/04/2013 05:37 PM, Fabrice Jammes wrote: > Hi Daniel, > > I've made some progress on the bug. > It seems that i had not loaded correctly my meta (Source and Object > table where previously both annotated as CHUNKED) > > Now it seems better : > > [fjammes@clrlsst-dbmaster-vm qserv]$ ./meta/bin/metaClientTool.py > retrieveTableInfo -a ~/.lsst/qmsadm LSST Source > /usr/lib64/python2.6/xmlrpclib.py:612: DeprecationWarning: The xmllib > module is obsolete. Use xml.sax instead. > import xmllib # lazy subclassing (!) > phiColNo: 33 > objIdCol: objectId > phiCol: raObject > objIdColNo: 3 > clusteredIdx: NULL > thetaColNo: 34 > overlap: 0.0 > physChunking: 33 > isView: 0 > *logicalPart: 1* > isRefMatch: 0 > thetaCol: declObject > [fjammes@clrlsst-dbmaster-vm qserv]$ ./meta/bin/metaClientTool.py > retrieveTableInfo -a ~/.lsst/qmsadm LSST Object > /usr/lib64/python2.6/xmlrpclib.py:612: DeprecationWarning: The xmllib > module is obsolete. Use xml.sax instead. > import xmllib # lazy subclassing (!) > phiColNo: 2 > objIdCol: objectId > phiCol: ra_PS > objIdColNo: 0 > clusteredIdx: NULL > thetaColNo: 4 > overlap: 0.0 > physChunking: 33 > isView: 0 > *logicalPart: 2* > isRefMatch: 0 > thetaCol: decl_PS > > But i still have an error, and qserv-master fails and dies while > receiving next query : > > [fjammes@clrlsst-dbmaster-vm qserv]$ mysql --host 127.0.0.1 --port > 4040 --user qsmaster LSST -e "SELECT s.ra, > > s.decl, o.raRange, o.declRange FROM Object o, Source s WHERE > o.objectId = > > 390034570102582 AND o.objectId = s.objectId AND o.latestObsTime = > > s.taiMidPoint;" > ERROR 4120 (Proxy) at line 1: Error executing query using qserv. > > Here's an interesting info from xrootd.log : > > >>Table *'LSST.ObjectSelfOverlap_6630*' doesn't exist Error retrieving > results for query: CREATE DATABASE IF NOT EXISTS > Subchunks_LSST_6630;CREATE TABLE IF NOT EXISTS > Subchunks_LSST_6630.Object_6630_0 ENGINE = MEMORY AS SELECT * FROM > LSST.Object_6630 WHERE subChunkId = 0;CREATE TABLE IF NOT EXISTS > Subchunks_LSST_6630.ObjectSelfOverlap_6630_0 ENGINE = MEMORY AS SELECT > * FROM LSST.ObjectSelfOverlap_6630 WHERE subChunkId = 0;CREATE TABLE > IF NOT EXISTS Subchunks_LSST_6630.ObjectFullOverlap_6630_0 ENGINE = > MEMORY AS SELECT * FROM LSST.ObjectFullOverlap_6630 WHERE subChunkId = 0; > > Indeed partition.py doesn't seems to create > ObjectSelfOverlap_6630.csv, which would allow the loader to create the > missing table : > > [fjammes@clrlsst-dbmaster-vm qserv]$ ls > /opt/qserv-dev/tmp/qservTest_case01/Object_partition/stripe_39/ > Object_6630.csv Object_6631.csv ObjectFullOverlap_6631.csv > ObjectSelfOverlap_6631.csv > > Could you help me please ? > > Thanks, > > Fabrice > > P.S. : Do you know where i can find the definition of FullOverlap, and > SelfOverlap ? > > > On 10/03/2013 05:39 PM, Fabrice Jammes wrote: >> Hi Daniel, >> >> Thanks for your help, i answer between the lines. >> >> On 10/01/2013 10:51 PM, Daniel L. Wang wrote: >>> Hi Fabrice, >>> >>> Thanks for posting the query! >>> >>> >>> mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o >>> JOIN Source s USING (objectId) WHERE o.objectId = 390034570102582 >>> AND o.latestObsTime = s.taiMidPoint; >>> >>> I note that there is no code for supporting the FROM ... JOIN ... >>> USING ( ...) syntax. I started playing with it last month, but don't >>> have anything I can merge--it's just not on the road map, even >>> though I think that you all want it (there seem to be other things >>> more pressing). >>> >> I had a look a this code today. Even if i'm not very acquainted with >> parsing techniques, it looks clear. Do you want me to merge it to my >> current code in a personal branch and then test it. >> >>> I think >>> >>> SELECT s.ra, s.decl, o.raRange, o.declRange >>> FROM Object o, Source s, where o.objectId = >>> WHERE o.objectId = 390034570102582 >>> AND o.objectId = s.objectId >>> AND o.latestObsTime = s.taiMidPoint; >>> >>> should work, though I haven't tested it. >> It returns the good result but duplicated 288 times (mysql query only >> returns one row) >> >> In qserv : >> mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o, >> Source s WHERE o.objectId = 390034570102582 AND o.objectId = >> s.objectId AND o.latestObsTime = s.taiMidPoint; >> ... >> | 1.8100328888038 | -5.50529748780274 | NULL | NULL | >> | 1.8100328888038 | -5.50529748780274 | NULL | NULL | >> | 1.8100328888038 | -5.50529748780274 | NULL | NULL | >> | 1.8100328888038 | -5.50529748780274 | NULL | NULL | >> | 1.8100328888038 | -5.50529748780274 | NULL | NULL | >> +-----------------+-------------------+---------+-----------+ >> 288 rows in set (7.27 sec) >> >> >> I've done a few debugging and it seems that the master sends the >> query 228 times to the worker, via xrootd : >> >> Here's and extract of the qserv-master log file (qserv was patch to >> write SQL queries sent to worker) >> >> Msg cid=6630 with size=54091 TEST-DEBUG SQL=^H^B^R^DLSST^Xæ3"¾: >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> .... >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint >> ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS >> o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND >> o.objectId=s.objectId AND >> o.latestObsTime=s.taiMidPoint^Z*r_2536c46ad2383d164ac4bf716edf836a9_6630_0"(^X >> 1e122f26cf89e2b55bfd9c05bf5f9cd6 QuerySize 54095 >> 1e122f26cf89e2b55bfd9c05bf5f9cd6 WriteClose Thu Oct 3 17:31:55 2013 >> 0.001213 >> >> I try to do some more investigation, but your help on this issue is >> very welcome. >> >> Thanks, >> >> Fabrice >> >>> >>> If you'd like to have a look at the code I was playing with, it's on >>> branch >>> u/danielw/joinSyntax1 >>> but it's now behind the shared scan code (which I hope to clean up >>> to Serge's quality and merge soon) by a fair bit. >>> >>> -Daniel >>> >>> >>> On 09/30/2013 08:38 AM, Fabrice Jammes wrote: >>>> Hello Daniel, >>>> >>>> As told on Thursday, i've met several error on SQL queries wich >>>> works on >>>> previous version (without qms and new parser). >>>> I'm not sure if error come from the test procedure or from qserv new >>>> version. >>>> I propose to send it to you one by one with detailed log, instead of >>>> sending the whole list (bugs may be fixed once and solve many >>>> queries). >>>> >>>> Here's one example for test case 01 : >>>> >>>> While connected to qserv : >>>> >>>> mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o >>>> JOIN Source s USING (objectId) WHERE o.objectId = 390034570102582 >>>> AND o.latestObsTime = s.taiMidPoint; >>>> ERROR 4120 (Proxy): Error during execution: 'open failed for chunk(s): >>>> 6801 6630 6800 7138 7308 6970 6971 6968 6631 7140read failed for >>>> chunk(s): 7478 7648 7310' >>>> >>>> xrootd and qserv-master logs are attached and it seems the errors are >>>> reported in xrootd.log with SQL sentence like this one : >>>> >>>> >>Unknown column 's.ra' in 'field list' Unable to execute query: >>>> CREATE >>>> TABLE r_1e01fa2d9ea14d542987c2af43bb3645f_7478_0 SELECT >>>> s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_7478 AS o WHERE >>>> o.objectId=390034570102582 AND o.latestObsTime=s.taiMidPoint; >>>> <<---Error with piece 0 complete (size=1). >>>> >>>> Indeed it seems table Source should be in FROM clause, aliased with >>>> 's' ? >>>> >>>> I can of course give you more information if needed. >>>> >>>> Thanks, >>>> >>>> Fabrice >>> >> >> ######################################################################## >> 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 > ######################################################################## 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