Timeout error with user table crossmatch

[MelissaGraham]

Hi @eastonhonaker , thanks for posting.

Based on the error message, it looks like the service thinks the user table ut1 does not exist. As I understand it, ut1 needs to be an astropy table and to exist in memory. To help with the diagnosis of this error, we need the code snippet that shows how ut1 is defined.

And here’s a bit of extra info that might help. In the examples in this related topic, ut1 is generated from SDSS.query_region and is an astropy table. In the example in this DP0.3 tutorial notebook, ut1 is read in from a file that has column names in the first row with no # symbol at the start of the row.

from astropy.table import Table
fnm1 = 'data/dp03_06_user_table_1.cat'
ut1 = Table.read(fnm1, format='ascii.basic')

Let us know if that solves the issue, and/or post the code snippet where the table is defined if the error persists?

[eastonhonaker]

Hi @MelissaGraham, thank you for the help! I get the feeling I’m missing something simple here. I’m reading in a csv file as an astropy table, and I renamed the table from ut1 to something I’m more used to. However, I’m still running into the same issue. The table is not exceptionally long, it’s ~4400 rows and well below the 32MB user upload limit. Here are some code snippets from how the table is defined. I get the same error when I directly upload the table and do a multi-object search in the portal.

#import targets from Backyard worlds
BYW = Table.read('reducedBYW.csv')
BYW.rename_column(';shortname','shortname')
#just in case, make the columns long
BYW['BYWid'] = BYW['BYWid'].astype('long')
BYW['catWISE_RA'] = BYW['catWISE_RA'].astype('long')
BYW['catWISE_Dec'] = BYW['catWISE_Dec'].astype('long')

The BYW table only has 4 columns, shortname is a string that doesn’t get used in the query.

#query to grab crossmatches and photometry from DP1 matching on the BYW ra and decs
query = """
        SELECT objectId, coord_ra, coord_dec, u_calibFlux, g_calibFlux, r_calibFlux, i_calibFlux, z_calibFlux, y_calibFlux,
        BYW.BYWid AS BYW_id, BYW.catWISE_RA AS BYW_ra, BYW.catWISE_Dec AS BYW_dec
        FROM dp1.Object, TAP_UPLOAD.BYW AS BYW
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec), CIRCLE('ICRS', BYW.catWISE_RA, BYW.catWISE_Dec, 0.0027))=1
        ORDER BY coord_ra ASC
        """
job = rsp_tap.submit_job(query, uploads={"BYW": BYW})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()

assert job.phase == 'COMPLETED'
results = job.fetch_result()
print(len(results))

[MelissaGraham]

Hi @eastonhonaker , thanks this is informative.

• “I get the same error when I directly upload the table and do a multi-object search in the portal.”

This makes me think the error is actually a problem with the table format, not that it doesn’t exist to TAP (despite that being the error message). Try visualizing the table by executing a single cell just containing BYW. Does it look like a table, are the columns full of values?

Next, I suspect that converting the RA and Dec to long integers might give you a problem, as RA and Dec need to be non-integer values, and to be treated as such (e.g., float). The other topic only converted a column to long because TAP would not accept type unit64 (a different integer type).

Try those things and report back? If these fixes don’t work, sharing the first ~5 lines of the file you’re using might be helpful, if that’s possible.

[eastonhonaker]

Hi @MelissaGraham, the table doesn’t have any blank spaces. I switched back from the long to floats and integers as the columns originally were . Here’s a full pdf of the notebook and error.
BYWinDP1.pdf (126.9 KB)

[MelissaGraham]

I made a short csv file with just the five:
byw.csv (223 Bytes)

The following set of code will load that table and cross match it. There are no matches, since those 5 don’t overlap the DP1 fields, but that’s ok. It all does seem to be working as it should be.

Imports.

from astropy.table import Table
import matplotlib.pyplot as plt
from lsst.rsp import get_tap_service
rsp_tap = get_tap_service("tap")

Load table.

ut1 = Table.read('byw.csv', format='csv')
ut1

Define query.

query = """
        SELECT objectId, coord_ra, coord_dec,
        ut1.catWISE_RA AS ut1_ra, ut1.catWISE_Dec AS ut1_dec, ut1.BYWid AS ut1_objid
        FROM dp1.Object, TAP_UPLOAD.ut1 AS ut1
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', ut1.catWISE_RA, ut1.catWISE_Dec, 0.00027))=1
        ORDER BY coord_ra ASC
        """

Execute query with cross-match.

job = rsp_tap.submit_job(query, uploads={"ut1": ut1})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()

No errors, so proceed to fetch.

assert job.phase == 'COMPLETED'
results = job.fetch_result()
print(len(results))

Make a plot to illustrate why there are zero matches.

names = ['47Tuc', 'LowEclLat', 'Fornax', 'ECDFS',
         'EDFS', 'LowGalLat', 'Seagull']
ras = [6.02, 37.86, 40.00, 53.13, 59.10, 95.00, 106.23]
decs = [-72.08, 6.98, -34.45, -28.10, -48.73, -25.00, -10.51]

plt.plot(ras, decs, 's', ms=20, mew=0, alpha=0.5, color='grey')
plt.plot(ut1['catWISE_RA'], ut1['catWISE_Dec'], 'o', mew=0, alpha=1, color='blue')
plt.xlabel('RA')
plt.ylabel('Dec')
plt.show()

And here it is all in one:
user_uploaded_tables_BYW.ipynb (19.2 KB)

Let us know if that helps – and mark this reply as the solution if it does?

[eastonhonaker]

Hi @MelissaGraham thank you for the notebook! I’m still getting the same Query Error: Qserv request failed: error: MySQL partitions removal failed for database: user_eastonhonaker, table: BYW_akkj1ftweu8jqgeo, worker: db02, error: BYW_akkj1ftweu8jqgeo:NO_SUCH_TABLE:Connection[2532]::execute(_inTransaction=1) mysql_real_query failed, query: 'ALTER TABLE us`, even using the notebook above. Are there any office hour/open help hours I can bring this to? If it’s helpful, this is the csv file, but I don’t think that’s where the issue is. While there are no matches in the first 5 sources, I do expect some matches later on.
reducedBYW.csv (154.8 KB)

[eastonhonaker]

Hi @MelissaGraham , after the service update I briefly ran into errors with timeouts, but am back to the original error today. Thoughts? I have not been able to successfully query through the notebooks or the portal, both had the same timeout errors and now sql-type errors.

[MelissaGraham]

Hi @eastonhonaker, I’m unable to reproduce these errors, but let’s keep working on this.

In the executed notebook below, you can see I don’t get any errors from cross-matching with the 4226 coordinates in your file (section 2). There are also no successful cross-matches, but that appears to be correct: none of the coordinates in your file fall inside the DP1 areas, though some are near (section 3 of the notebook).

Let’s try a clean test.

1. Log out of the RSP completely (in the Notebook Aspect, go to File, then “Save All, Exit and Log Out”).
2. Log back in with the default settings (the recommended image and a large server size).
3. Upload the two input files and the notebook to an otherwise empty folder in your home directory.
4. Open the notebook file. In the menu, go to “Kernel” and then “Restart Kernel and Clear Outputs of All Cells”.
5. Work through the notebook, executing each cell in order.

Then report back? This notebook should execute exactly the same for you as for me, so long as we’ve chosen the same settings when logging in to the Notebook Aspect.

Executed notebook:
user_uploaded_tables_BYW.ipynb (312.1 KB)

The two input files are:
byw.csv (223 Bytes)
reducedBYW.csv (154.8 KB)

And this file is written by the notebook in section 4, for upload to the Portal:
reducedBYW_subset.csv (1.2 KB)

[eastonhonaker]

Hi @MelissaGraham, thank you for helping again! I followed all the steps and used the provided notebook but still ran into the sql table error. I created a new folder in my home directory called ‘debuggingDP1query’. I cannot get either query to complete, they run for ~5 seconds and crash. I got @sidchaini to try the notebook and he also wasn’t able to reproduce the error. I tried in a different browser and got the same result. Are there any user settings or something else to try? When I upload the ‘reducedBYW_subset.csv’ file to the Portal, it does not finish the query but crashes with the same error.

[MelissaGraham]

Hi @eastonhonaker, thanks for running all these tests, and for having Sid try as well – I think we can definitely say there’s something odd about your account that is preventing the user uploaded table functionality from working.

The one last thing to try before I escalate this to the attention of our tech team is, when logging in to the notebook aspect and starting a new server, still choose the recommended image (v29.1.1) and large container and then also check the boxes that say enable debug logs and reset user environment (as in this post). And my apologies I should’ve just included that in the “clean test” from my last post.

[eastonhonaker]

Hi @MelissaGraham , I tried the extra steps for the clean test and checked the enable debug logs and reset user environment boxes, but got the same error. How should I proceed? My account is from ~2years ago during DP0, do I need to make any edits to its settings or even make a new rsp account? Thanks!

[MelissaGraham]

Thanks for doing that one final test @eastonhonaker – I’m going to turn to our tech team for advice here and I’ll be back in touch (might take a few days, TBD). Apologies for this inconvenience.

For reference (mostly for other Rubin staff) the internal work ticket is Issue navigator - Rubin Jira

[eastonhonaker]

Understood, thank you very much!

[gapon (Igor Gaponenko)]

I have analyzed the problem on the Qserv side, and it looks like the query has an unsupported SQL type. Here is the error message:

error: Unknown data type: 'unicodeChar', errno: 4161

And here is a query that was sent to Qserv by the TAP service:

CREATE TABLE IF NOT EXISTS `user_eastonhonaker`.`BYW_akkj1ftweu8jqgeo` (
  `qserv_trans_id` INT NOT NULL,
  `BYWid` long,
  `shortname` unicodeChar,
  `catWISE_RA` long,
  `catWISE_Dec` long) 

[MelissaGraham]

Thanks @gapon.

I can confirm that when I execute the notebook in this post, column shortname is interpreted as a string:

That is probably why I don’t encounter the same error as @eastonhonaker.

@eastonhonaker when you execute the same cell – and use the exact same input files (all from this post) – does it show in the output that shortname has a different type?

[frossie (Frossie Economou)]

Okay, I think we understand this now.

@eastonhonaker try again to confirm?

[eastonhonaker]

Hi @MelissaGraham @gapon @frossie , I also think it is a string on my end. I ran the notebook without any changes (it was already a str9 type column) and was able to complete the query and get results. I am rather confused since I didn’t make any changes but it is now working. I also tested it with a different dataset and notebook and was able to complete a query (I had issues with that one too in the past). Similarly, I can do the same queries through the portal now. The issue is resolved, but I’m unsure why since I didn’t make any changes on my end.

[gapon (Igor Gaponenko)]

Hi @eastonhonaker, the temporary database used for ingesting your tables was found in a broken state. The problem was resolved by deleting the database. Temporary databases are created automatically. The database turned into this state after an earlier attempt to ingest tables with a column of the unsupported data type unicodeChar. Please, be assured this was not your fault. The table ingest system is still not as robust as we would like it to be. It should tolerate mistakes. We’re working on improving the implementation.

[eastonhonaker]

Ah, I see! Thank you all for the help along the way, I greatly appreciate it, @MelissaGraham @gapon @frossie ! I’ll mark the issue as resolved now.