Hi,
So I am trying to upgrade an instance from 18.04 to 22.04, and I finally thought I managed to get everything right, django-manage showed all SUCCESS, and the databases were restored, but when I tried to look at the historic data, there was nothing, as in I went and looked under the commcarehq_ucr schema, and did a select * from commcarehq_ucr.oneofthapss, but every table had zero records.
From some previous forum messages (eg: eg1) I 'think'/'hope' that the ucr tables are rebuilt by a pillowtop process? Could it be that the process which rebuilds the ucr tables is broken?
When looking at /home/cchq/www/monolith/log/pillowtop-case-pillow-0.log, I see a significant number of error messages.
Am I on the right track or is there something else I should be looking into?
Hi @dirkdejager
You are on the right track. UCRs are rebuilt by a separate process.
It sounds like you need to initiate that process. Here is how to do that:
- Navigate to Configurable Reports by clicking the "Reports" menu, choose "View All", and click "Configurable Reports" on the left, under "Tools".
- Choose a Data Source, either by selecting it from the dropdown, or by clicking on it in the menu on the left.
- Click the "Rebuild Data Source" button at the top.
That should repopulate its table in the commcarehq_ucr schema.
1 Like
I can't seem to find the "Configurable Reports" section you are referring to. Is there are backend command whereby I can rebuild all the ucr tables?
Hi @dirkdejager
I wonder whether it's Elasticsearch that is missing its data, not UCRs / custom reports.
The following command will set up the Elasticsearch indices, and populate them. (Replace <env> with the name of your environment. ("monolith"?))
$ cchq <env> django-manage ptop_preindex
I hope that helps.
Norman
Hi Norman,
Firstly, thank you very much. I didn't realise that the user had to be enabled to see the configurable reports, which I found could be enabled via the first response here: [Resolved] 500 error with some custom reports.
Once I went to the https://server.com/a/mydomain/configurable_reports page, I found the feature_flag link in the top left corner, and managed to enable the configurable reports which in turn updated the tables in the ucr schema. Thank you!
Figure 1. when going to the /a/your_application/configurable_reports page, one needs to enable a feature_flag to view the configurable reports.
I tried running the commcare-cloud monolith django-manage ptop_preindex, but I received a Nothing needs to be reindexed response...
What I am struggling with now, are the OData feeds which also don't seem to be getting data anymore - it all seems blank, or with only the most recent data trickling through. I tried to look at the domain stats via a command commcare-cloud monolith django-manage print_domain_stats domain_a, and it shows me three columns with widely varying numbers in each column. Is there a way to somehow synchronize these? Or am I again looking at the wrong things...
Thank you very much,
Dirk
(cchq) ubuntu@monolith:~$ commcare-cloud monolith django-manage print_domain_stats domain_a
ssh ubuntu@172.31.26.146 -t -o UserKnownHostsFile=/home/ubuntu/environments/monolith/known_hosts 'sudo -iu cchq bash -c '"'"'cd /home/cchq/www/monolith/current; python_env/bin/python manage.py print_domain_stats domain_a'"'"''
Ubuntu 22.04.2 LTS
Doc Type | Couch | SQL | ES
-------------------------------------------------- | -------------------- | -------------------- | --------------------
app_manager.GlobalAppConfig | | 6 |
Application | 323 | |
blobs.BlobMeta | | 426479 |
case_importer.CaseUploadFileMeta | | 38 |
case_importer.CaseUploadFormRecord | | 1589 |
case_importer.CaseUploadRecord | | 38 |
case_search.DomainsNotInCaseSearchIndex | | 1 |
CaseExportDataSchema | 10 | |
CaseExportInstance | 27 | |
cloudcare.ApplicationAccess | | 1 |
CommCareCase | | 312965 | 572
CommCareCase-Deleted | | 2800 |
CommCareMultimedia | 69 | |
CommCareUser | 144 | | 38
CommCareUser-Deleted | 7 | |
custom_data_fields.CustomDataFieldsDefinition | | 1 |
custom_data_fields.Field | | 3 |
data_dictionary.CaseProperty | | 342 |
data_dictionary.CaseType | | 7 |
data_interfaces.AutomaticUpdateRule | | 43 |
data_interfaces.CaseRuleAction | | 43 |
data_interfaces.CaseRuleCriteria | | 20 |
data_interfaces.CaseRuleSubmission | | 21087 |
data_interfaces.CreateScheduleInstanceActionDefinition | | 30 |
data_interfaces.DomainCaseRuleRun | | 2930 |
data_interfaces.MatchPropertyDefinition | | 20 |
data_interfaces.UpdateCaseDefinition | | 13 |
DataSourceConfiguration | 1 | |
domain.AppReleaseModeSetting | | 1 |
domain.DomainAuditRecordEntry | | 1 |
domain.SuperuserProjectEntryRecord | | 430 |
ExportDataSchema | 0 | |
ExportInstance | 0 | |
fixtures.LookupTable | | 3 |
fixtures.LookupTableRow | | 306 |
form_processor.CaseTransaction | | 1262716 |
form_processor.CommCareCaseIndex | | 303363 |
form_processor.XFormOperation | | 139830 |
FormExportDataSchema | 20 | |
FormExportInstance | 22 | |
Group | 0 | |
LinkedApplication | 0 | |
MobileAuthKeyRecord | 135 | |
phonelog.DeviceReportEntry | | 599182 |
phonelog.ForceCloseEntry | | 16 |
phonelog.UserEntry | | 30820 |
phonelog.UserErrorEntry | | 49 |
Product | 0 | |
Program | 0 | |
ReportConfig | 0 | |
ReportConfiguration | 0 | |
ReportNotification | 0 | |
saved_reports.ScheduledReportsCheckpoint | | 67077 |
scheduling.AlertEvent | | 26 |
scheduling.AlertSchedule | | 26 |
scheduling.ImmediateBroadcast | | 3 |
scheduling.SMSContent | | 56 |
scheduling.TimedEvent | | 30 |
scheduling.TimedSchedule | | 7 |
scheduling_partitioned.AlertScheduleInstance | | 1 |
scheduling_partitioned.CaseTimedScheduleInstance | | 171011 |
sms.MessagingEvent | | 1316080 |
sms.MessagingSubEvent | | 1316085 |
sms.PhoneNumber | | 97276 |
sms.SMS | | 655941 |
translations.SMSTranslations | | 1 |
user_importer.UserUploadRecord | | 15 |
users.Invitation | | 6 |
users.RolePermission | | 38 |
users.UserRole | | 5 |
WebUser | 4 | | 2
XFormArchived | | 3652 |
XFormDeprecated | | 54 |
XFormDuplicate | | 533 | 6
XFormInstance | | 387892 | 763
XFormInstance-Deleted | | 154 |
Connection to 172.31.26.146 closed.
Listing 1. Couch, SQL and Elastic Search all seem to have different values. In the console, the rows: CommcareCase, CommcareUser, WebUser, XFormDuplicate and XFormInstance are highlighted in red. Why? Is it possible to align these?
Hi @dirkdejager
Differences between Couch and SQL are expected. But the Elasticsearch value for CommcareCase and XFormInstance ought to be the same as the SQL value, and CommcareUser and WebUser ought to be the same as the Couch value.
We've established that the Elasticsearch indices exist. Considering new data is trickling through, Kafka must be fine. So I think we need to reset the indices to repopulate them with historical data. The command for that is:
$ commcare-cloud monolith django-manage ptop_preindex --reset
Once it's complete, the values in print_domain_stats should align, and the OData feeds should have data.
1 Like
Looks like Norman's got you pointed in the right direction - OData feeds are backed by elasticsearch, and it looks like data hasn't been fully copied there. I wanted to chime in briefly on how to interpret the output of print_domain_stats.
Some data models are stored in CouchDB, some in PostgreSQL, and occasionally we'll have models stored in both, typically when we're in the long process of migrating a model from Couch to SQL. Everything stored in elasticsearch is pulled from primary data models in either couch or SQL, so for models that do have an elasticsearch index, it should more or less agree with the count in the primary database for that model. (There are a couple caveats to that, but I don't think we need to go into that).
Hi Norman and Ethan,
Thank you so much for the assistance and explanation of what to look out for! I kicked off the rebuild by running commcare-cloud monolith django-manage ptop_preindex --reset which took some time, but the entries are looking much better!
Doc Type | Couch | SQL | ES
-------------------------------------------------- | -------------------- | -------------------- | --------------------
app_manager.GlobalAppConfig | | 6 |
Application | 323 | | 323
blobs.BlobMeta | | 429160 |
case_importer.CaseUploadFileMeta | | 38 |
case_importer.CaseUploadFormRecord | | 1589 |
case_importer.CaseUploadRecord | | 38 |
case_search.DomainsNotInCaseSearchIndex | | 1 |
CaseExportDataSchema | 10 | |
CaseExportInstance | 27 | |
cloudcare.ApplicationAccess | | 1 |
CommCareCase | | 315433 | 315433
CommCareCase-Deleted | | 2800 |
CommCareMultimedia | 69 | |
CommCareUser | 144 | | 151
CommCareUser-Deleted | 7 | |
custom_data_fields.CustomDataFieldsDefinition | | 1 |
custom_data_fields.Field | | 3 |
data_dictionary.CaseProperty | | 342 |
data_dictionary.CaseType | | 7 |
data_interfaces.AutomaticUpdateRule | | 43 |
data_interfaces.CaseRuleAction | | 43 |
data_interfaces.CaseRuleCriteria | | 20 |
data_interfaces.CaseRuleSubmission | | 19769 |
data_interfaces.CreateScheduleInstanceActionDefinition | | 30 |
data_interfaces.DomainCaseRuleRun | | 2930 |
data_interfaces.MatchPropertyDefinition | | 20 |
data_interfaces.UpdateCaseDefinition | | 13 |
DataSourceConfiguration | 1 | |
domain.AppReleaseModeSetting | | 1 |
domain.DomainAuditRecordEntry | | 1 |
domain.SuperuserProjectEntryRecord | | 432 |
ExportDataSchema | 0 | |
ExportInstance | 0 | |
fixtures.LookupTable | | 3 |
fixtures.LookupTableRow | | 306 |
form_processor.CaseTransaction | | 1267844 |
form_processor.CommCareCaseIndex | | 305777 |
form_processor.XFormOperation | | 139830 |
FormExportDataSchema | 20 | |
FormExportInstance | 22 | |
Group | 0 | |
LinkedApplication | 0 | |
MobileAuthKeyRecord | 135 | |
phonelog.DeviceReportEntry | | 617994 |
phonelog.ForceCloseEntry | | 16 |
phonelog.UserEntry | | 28796 |
phonelog.UserErrorEntry | | 51 |
Product | 0 | |
Program | 0 | |
ReportConfig | 0 | |
ReportConfiguration | 0 | |
ReportNotification | 0 | |
saved_reports.ScheduledReportsCheckpoint | | 67160 |
scheduling.AlertEvent | | 26 |
scheduling.AlertSchedule | | 26 |
scheduling.ImmediateBroadcast | | 3 |
scheduling.SMSContent | | 56 |
scheduling.TimedEvent | | 30 |
scheduling.TimedSchedule | | 7 |
scheduling_partitioned.AlertScheduleInstance | | 1 |
scheduling_partitioned.CaseTimedScheduleInstance | | 172262 |
sms.MessagingEvent | | 1319909 |
sms.MessagingSubEvent | | 1319914 |
sms.PhoneNumber | | 98499 |
sms.SMS | | 659303 |
translations.SMSTranslations | | 1 |
user_importer.UserUploadRecord | | 15 |
users.Invitation | | 6 |
users.RolePermission | | 38 |
users.UserRole | | 5 |
WebUser | 4 | | 4
XFormArchived | | 3652 | 3652
XFormDeprecated | | 54 | 54
XFormDuplicate | | 534 | 534
XFormInstance | | 390562 | 390560
XFormInstance-Deleted | | 154 |
Listing 1. Couch, SQL and appropriate Elastic Search all seem to have much more similar and aligned values. In the console, only the rows: CommcareUser and XFormInstance are now highlighted in red. It appears as though these are only slightly different.
I wonder whether it is worth adding a note at the end of the Ubuntu Upgrade guide changelog item or as part of the Elastic Search Section of the Backup and Restore guide how to do this (ie running commcare-cloud monolith django-manage ptop_preindex --reset)
Thanks again!
Dirk
