[Resolved] 500 error with some custom reports

Developers
1

When running certain custom reports for the inddex24-dev project space:
Report 2a - Gaps Summary by Food Type
Report 2b - Detailed Information on Gaps
We receive a 500 error on the browser end. All services appear to be up.

EDIT
The postgresql log reveals this:

2021-08-05 09:05:35.730 GMT,"commcarehq","commcarehq_ucr",28441,"127.0.0.1:39104",610ba8d5.6f19,5,"SELECT",2021-08-05 09:01:09 GMT,4/2711888,0,ERROR,42P01,"relation ""ucr_inddex24-dev_food_consumption_indicat_67e3ea20"" does not exist",,,,,,"SELECT doc_id AS doc_id, inserted_at AS inserted_at, unique_respondent_id AS unique_respondent_id, location_id AS location_id, respondent_id AS respondent_id, recall_case_id AS recall_case_id, opened_by_username AS opened_by_username, owner_name AS owner_name, visit_date AS visit_date, opened_on AS opened_on, recall_status AS recall_status, gender AS gender, age_years_calculated AS age_years_calculated, age_months_calculated AS age_months_calculated, pregnant AS pregnant, breastfeeding AS breastfeeding, urban_rural AS urban_rural, supplements AS supplements, food_code AS food_code, food_name AS food_name, recipe_name AS recipe_name, food_type AS food_type, food_status AS food_status, base_term_food_code AS base_term_food_code, eating_time AS eating_time, time_block AS time_block, already_reported_food AS already_reported_food, already_reported_food_case_id AS already_reported_food_case_id, already_reported_recipe AS already_reported_recipe, already_reported_recipe_case_id AS already_reported_recipe_case_id, already_reported_recipe_name AS already_reported_recipe_name, is_ingredient AS is_ingredient, recipe_case_id AS recipe_case_id, short_name AS short_name, food_base_term AS food_base_term, tag_1 AS tag_1, other_tag_1 AS other_tag_1, tag_2 AS tag_2, other_tag_2 AS other_tag_2, tag_3 AS tag_3, other_tag_3 AS other_tag_3, tag_4 AS tag_4, other_tag_4 AS other_tag_4, tag_5 AS tag_5, other_tag_5 AS other_tag_5, tag_6 AS tag_6, other_tag_6 AS other_tag_6, tag_7 AS tag_7, other_tag_7 AS other_tag_7, tag_8 AS tag_8, other_tag_8 AS other_tag_8, tag_9 AS tag_9, other_tag_9 AS other_tag_9, tag_10 AS tag_10, other_tag_10 AS other_tag_10, conv_method_code AS conv_method_code, conv_method_desc AS conv_method_desc, conv_option_code AS conv_option_code, conv_option_desc AS conv_option_desc, measurement_amount AS measurement_amount, conv_units AS conv_units, portions AS portions, nsr_conv_method_code_post_cooking AS nsr_conv_method_code_post_cooking, nsr_conv_method_desc_post_cooking AS nsr_conv_method_desc_post_cooking, nsr_conv_option_code_post_cooking AS nsr_conv_option_code_post_cooking, nsr_conv_option_desc_post_cooking AS nsr_conv_option_desc_post_cooking, nsr_measurement_amount_post_cooking AS nsr_measurement_amount_post_cooking, nsr_consumed_cooked_fraction AS nsr_consumed_cooked_fraction 
FROM ""ucr_inddex24-dev_food_consumption_indicat_67e3ea20"" 
WHERE visit_date >= '2021-07-01 00:00:00' AND visit_date <= '2021-07-31 00:00:00' GROUP BY doc_id",2329,,""
2021-08-05 09:05:35.999 GMT,,,9385,,60db170c.24a9,21329,,2021-06-29 12:50:20 GMT,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""

TL;DR is probably this bit:
"relation ""ucr_inddex24-dev_food_consumption_indicat_67e3ea20"" does not exist"
Is there something I should be doing to generate that?

2

You can force a rebuild of the report's data source which should create the table.

The easiest way to do that is via the UCR admin UI (/a/domain/configurable_reports/ - requires enabling the user_reports feature flag for the user or domain).

Separately I'd recommend setting up Sentry for your project so you can get more useful error messages!

1 Like
3

You can also check the Django process logs, though with multiple webworkers, you'd have to check each. That's where Cory's suggestion to use Sentry can help out considerably!

You should also be able to adjust the Django logging configuration to send an email for every 500 error, though I'm not exactly certain how that's configured.

1 Like
4

Thanks Cory and Ethan, I'll do that and revert

5

Thanks for this @czue, I managed to turn on the feature flag and access the UCR admin UI, but there's pretty much nothing there - what should I be expecting? I get this:

image
image1148×563 40.6 KB

My understanding from conversations with the team to date is that the only thing I need to do to have the custom reports configured for the project space is to ensure that I use the correct project space name when creating it - i.e. one of these:

Ed

6

Ah, this stuff wasn't really built with this use-case in mind. You are correct that the domain name must be one of those, but static UCRs are also tagged to run only on specified environments. This is configured here:

We can add your server environment to that list - what is the value of settings.SERVER_ENVIRONMENT? (It's probably monolith).

7

It is indeed monolith, thanks Ethan!

8

Alright, I just changed that - if you deploy again it should be available on your environment.

1 Like
9

Thx for your work! Community bless you :wink:

10

This appears to be resolved, thanks all!