Print

Print
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



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