500 Server Error on query

Support Data Preview 0
#1

I am trying to run the following query via some python code:

# LSST
from lsst.rsp import get_tap_service

service = get_tap_service()
assert service is not None

query = "SELECT ROUND(dp02_dc2_catalogs.CcdVisit.obsStartMJD, -1) as mjd_index, COUNT(*) as n "\
        "FROM dp02_dc2_catalogs.ForcedSource as source "\
        "JOIN dp02_dc2_catalogs.Object as obj ON source.objectId = obj.objectId "\
        "JOIN dp02_dc2_catalogs.CcdVisit on source.ccdVisitId = dp02_dc2_catalogs.CcdVisit.ccdVisitId "\
        "WHERE scisql_nanojanskyToAbMag(obj.g_psfFlux) - scisql_nanojanskyToAbMag(obj.r_psfFlux) > 0.4 "\
        "AND scisql_nanojanskyToAbMag(obj.g_psfFlux) - scisql_nanojanskyToAbMag(obj.r_psfFlux) < 1.5 "\
        "AND scisql_nanojanskyToAbMag(obj.r_psfFlux) - scisql_nanojanskyToAbMag(obj.i_psfFlux) > 0 "\
        "AND scisql_nanojanskyToAbMag(obj.r_psfFlux) - scisql_nanojanskyToAbMag(obj.i_psfFlux) < 1.2 "\
        "AND obj.detect_isPrimary = 1 AND obj.g_extendedness = 0 AND obj.r_extendedness = 0 AND obj.i_extendedness = 0 "\
        "AND dp02_dc2_catalogs.CcdVisit.obsStartMJD < 60919 "\
        "GROUP BY mjd_index "

results = service.search(query)
diaSources = results.to_table()

The goal of this query is to build a histogram of the number of visits expected for K/M dwarfs over the first couple of years of the survey.

My issue is I am getting a 500 Server Error after the query has been executing a bit, with the full error message being:

ValueError                                Traceback (most recent call last)
File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:242, in DALQuery.execute_votable(self, post)
    241 try:
--> 242     return votableparse(self.execute_stream(post=post).read)
    243 except Exception as e:

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/utils/decorators.py:546, in deprecated_renamed_argument.<locals>.decorator.<locals>.wrapper(*args, **kwargs)
    544         warnings.warn(msg, warning_type, stacklevel=2)
--> 546 return function(*args, **kwargs)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/io/votable/table.py:160, in parse(source, columns, invalid, verify, chunk_size, table_number, table_id, filename, unit_format, datatype_mapping, _debug_python_based_parser)
    156 with iterparser.get_xml_iterator(
    157         source,
    158         _debug_python_based_parser=_debug_python_based_parser) as iterator:
    159     return tree.VOTableFile(
--> 160         config=config, pos=(1, 1)).parse(iterator, config)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/io/votable/tree.py:3572, in VOTableFile.parse(self, iterator, config)
   3570 config['_current_table_number'] = 0
-> 3572 for start, tag, data, pos in iterator:
   3573     if start:

ValueError: 1:0: syntax error

During handling of the above exception, another exception occurred:

DALServiceError                           Traceback (most recent call last)
Input In [2], in <cell line: 13>()
      1 query = "SELECT ROUND(dp02_dc2_catalogs.CcdVisit.obsStartMJD, -1) as mjd_index, COUNT(*) as n "\
      2         "FROM dp02_dc2_catalogs.ForcedSource as source "\
      3         "JOIN dp02_dc2_catalogs.Object as obj ON source.objectId = obj.objectId "\
   (...)
     10         "AND dp02_dc2_catalogs.CcdVisit.obsStartMJD < 60919 "\
     11         "GROUP BY mjd_index "
---> 13 results = service.search(query)
     14 diaSources = results.to_table()

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/tap.py:248, in TAPService.run_sync(self, query, language, maxrec, uploads, **keywords)
    219 def run_sync(
    220         self, query, language="ADQL", maxrec=None, uploads=None,
    221         **keywords):
    222     """
    223     runs sync query and returns its result
    224 
   (...)
    244     TAPResults
    245     """
    246     return self.create_query(
    247         query, language=language, maxrec=maxrec, uploads=uploads,
--> 248         **keywords).execute()

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/tap.py:942, in TAPQuery.execute(self)
    928 def execute(self):
    929     """
    930     submit the query and return the results as a TAPResults instance
    931 
   (...)
    940        for errors parsing the VOTable response
    941     """
--> 942     return TAPResults(self.execute_votable(), url=self.queryurl, session=self._session)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:244, in DALQuery.execute_votable(self, post)
    242     return votableparse(self.execute_stream(post=post).read)
    243 except Exception as e:
--> 244     self.raise_if_error()
    245     raise DALFormatError(e, self.queryurl)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:253, in DALQuery.raise_if_error(self)
    251 if self._ex:
    252     e = self._ex
--> 253     raise DALServiceError.from_except(e, self.queryurl)

DALServiceError: 500 Server Error:  for url: https://data.lsst.cloud/api/tap/sync/rmuujbzy2i7dh47k/run

There is a link to click on that provides more information on the error that reads:

unexpected exception: java.lang.RuntimeException: ca.nrc.cadc.uws.server.JobPhaseException: cannot execute job rmuujbzy2i7dh47k when phase = EXECUTING

I am unsure what this exception above is supposed to mean and how to avoid this error to be able to execute the above query. Any help is appreciated, thanks.

#2

I’m getting the same error, with a different query. The same code I’m using was working fine last week. Like you, I’m using

service = get_tap_service()
results = service.search(query)

In my case I’m getting get_tap_service with
from rubin_jupyter_utils.lab.notebook import get_tap_service
but hopefully it means the same thing.

#3

Hi @medan and @jjbuchanan, thanks for reporting. I can say we are seeing some similar errors with TAP in the automated RSP tests that are always running in the background. I’ll check into it and report back.

And just in case you didn’t try shutting down the server, logging out of the Notebook Aspect, and restarting – do try that in case it was a temporary issue that’s been fixed. I tested NB 02 just now and it was executing TAP queries normally.

1 Like
#4

Just to follow up, I did shut down/logged out and restarted everything and I am still getting the error. What is strange, is I am only getting it the error on the query above. I am able to run other queries just fine.

#5

I exited+shut down and restarted, and now it’s working for me. I’m using a different query though.

#6

I think there were two separate underlying issues here. One was a backend issue affecting all queries which (I think) has since resolved (or at least is not persistent, given TAP is now working for other queries).

@medan I think the way that query is set up might be inefficient. I’m going to test it and see if I can reformat it to be optimal for the DP0.2 data products. I think if your goal is, as you say, to build a histogram of the number of visits, you only need the CcdVisit table and do not need to join to Object or ForcedSource. If you can describe a bit more your end goal, that would help me out?

#7

Hi again @medan, I took a closer look at the query pasted in the original post, and estimate that it was attempting to retrieve 1.8 billion results. I put a demo of some options for you in this Jupyter Notebook below.

Ilija_Medan_2022-11-10.ipynb (173.6 KB)

If that resolves the issue, please post a reply that confirms it (or click on the three-dot icon below and then use the :ballot_box_with_check: Solution icon to mark this issue as resolved.

If it does not resolve the issue, please follow up with a reply post in this thread so we can keep working on it?

#8

@MelissaGraham Ah, ok, after looking at this notebook I do realize how my original query would return so many rows (probably should have realized this sooner)! I guess it makes more sense why that would be occurring then (though I do agree I would expect the kernel to crash, not the server error still?). The above notebook does work fine for me, so I think my lesson here is to NOT try to do these types of queries for the entire sky. Thanks for the help!

1 Like
#9

You’re very welcome, and thanks for trying out new kinds of queries – the RSP is still in development so it does help Rubin staff a lot to see the kinds of things users will want to do. And thanks for participating in DP0!