# 500 Server Error on query

**URL:** <https://www.rubin.community/t/500-server-error-on-query/7202>\
**Category:** Support\
**Tags:** dp0\
**Created:** [November 10, 2022, 12:59am UTC](https://www.rubin.community/t/500-server-error-on-query/7202 "2022-11-10T00:59:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![medan](https://avatars.discourse-cdn.com/v4/letter/m/f0a364/32.png) [@medan](https://www.rubin.community/u/medan)\
**Post date:** [November 10, 2022, 12:59am UTC](https://www.rubin.community/t/500-server-error-on-query/7202/1 "2022-11-10T00:59:27Z")

</div>

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

```auto
# 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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![jjbuchanan](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/jjbuchanan/32/1308_2.png) [@jjbuchanan](https://www.rubin.community/u/jjbuchanan)\
**Post date:** [November 10, 2022, 7:24am UTC](https://www.rubin.community/t/500-server-error-on-query/7202/2 "2022-11-10T07:24:05Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![MelissaGraham](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/melissagraham/32/2549_2.png) [@MelissaGraham](https://www.rubin.community/u/MelissaGraham)\
**Post date:** [November 10, 2022, 5:53pm UTC](https://www.rubin.community/t/500-server-error-on-query/7202/3 "2022-11-10T17:53:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![medan](https://avatars.discourse-cdn.com/v4/letter/m/f0a364/32.png) [@medan](https://www.rubin.community/u/medan)\
**Post date:** [November 10, 2022, 8:44pm UTC](https://www.rubin.community/t/500-server-error-on-query/7202/4 "2022-11-10T20:44:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jjbuchanan](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/jjbuchanan/32/1308_2.png) [@jjbuchanan](https://www.rubin.community/u/jjbuchanan)\
**Post date:** [November 10, 2022, 9:27pm UTC](https://www.rubin.community/t/500-server-error-on-query/7202/5 "2022-11-10T21:27:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![MelissaGraham](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/melissagraham/32/2549_2.png) [@MelissaGraham](https://www.rubin.community/u/MelissaGraham)\
**Post date:** [November 11, 2022, 1:42am UTC](https://www.rubin.community/t/500-server-error-on-query/7202/6 "2022-11-11T01:42:48Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![MelissaGraham](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/melissagraham/32/2549_2.png) [@MelissaGraham](https://www.rubin.community/u/MelissaGraham)\
**Post date:** [November 11, 2022, 7:41am UTC](https://www.rubin.community/t/500-server-error-on-query/7202/7 "2022-11-11T07:41:42Z")

</div>

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](https://www.rubin.community/uploads/short-url/wwQWFoQC1KMe64J14gU2jBpd0vd.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 ☑ 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?

---

<div class="post-metadata">

**Author:** ![medan](https://avatars.discourse-cdn.com/v4/letter/m/f0a364/32.png) [@medan](https://www.rubin.community/u/medan)\
**Post date:** [November 11, 2022, 2:30pm UTC](https://www.rubin.community/t/500-server-error-on-query/7202/8 "2022-11-11T14:30:56Z")

</div>

@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!

---

<div class="post-metadata">

**Author:** ![MelissaGraham](https://sea2.discourse-cdn.com/flex002/user_avatar/www.rubin.community/melissagraham/32/2549_2.png) [@MelissaGraham](https://www.rubin.community/u/MelissaGraham)\
**Post date:** [November 11, 2022, 5:57pm UTC](https://www.rubin.community/t/500-server-error-on-query/7202/9 "2022-11-11T17:57:27Z")

</div>

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!
