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:
[log in to unmask]" type="cite">
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:
[log in to unmask]" type="cite">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