|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → Indicia Development → Recorders not being written to cache_samples_nonfunctional
Since my warehouse upgrade, some records are not having the 'recorders' field written to cache_samples_nonfunctional but are just showing NULL. There is nothing different about these except that they have a space in the username. In the previous version this was not a problem. How can I get around this, so that all records are presented with the correct Recorder?
Hi Alice
I don't think that having a space in the username should affect how it works. The code for calculating the stored recorder name is quite complex as there are lots of different approaches across different Indicia clients and apps. The code for working it out is at https://github.com/Indicia-Team/warehou … .php#L1120 - perhaps you could take a look at the queries to see if you can understand why none of them pick up the name. If you create a temporary table called needs_update_samples with an id column containing the IDs of the samples that have NULL recorders, you can run the queries to see what is going on.
Hi John,
Thank you for your reply.
Actually I was wrong with my identification of where the problem is. If I masquerade as a user which has spaces in the username and make a new record the record is written correctly into the cache_samples_nonfunctional table.
Hoewver most records in that table do not have 'recorders' filled in and I guess the problem must have occurred during the recent database upgrade. I have been trying to see in which tables the info is missing by comparing tables in my old and new database but I can't see any differences in the tables I would expect to. If I compare a record which shows with the recorder name in the old system and without a recorder name in the new system there is no obvious difference in the occurrences and samples tables, and the users and people tables seem the same.
I'm a bit stuck on how to resolve this.
Hi Alice,
I think my recommendation would be to request the work queue to rebuild all the entries in the occurrences and samples cache tables:
insert into work_queue(task, entity, record_id, cost_estimate, priority, created_on)
select 'task_cache_builder_update', 'occurrence', id, 100, 2, now()
from occurrences where deleted=false;
insert into work_queue(task, entity, record_id, cost_estimate, priority, created_on)
select 'task_cache_builder_update', 'sample', id, 100, 2, now()
from samples where deleted=false;Then make sure your warehouse scheduled tasks are running as they should be so all these entries get processed. If that doesn't work, you'll need to look at the queries in the link above to work out why none of them find a recorder name.
Best wishes
John
Thanks.
I've done the rebuild, and scheduled tasks (via /index.php/scheduled_tasks) and this hasn't helped. I will go through those queries again...
Edit - I see the scheduled_tasks didn't run properly, and I am getting an error when I try to run them.
And my server status says: Errors in work queue - There are errors in the work_queue table which need to be checked and fixed.
Running cache_builder
Last run at 2019-12-11 19:19:05
termlists_terms
# records affected
Total 6
Delete(s) 0
update(s) 6
insert(s) 0
taxa_taxon_lists
There was an SQL error: ERROR: syntax error at or near "WHERE" LINE 30: WHERE cttl.id=u.taxa_taxon_list_id ^ - UPDATE cache_taxa_taxon_lists u SET family_taxa_taxon_list_id=cttlf.id, family_taxon=cttlf.taxon, order_taxa_taxon_list_id=cttlo.id, order_taxon=cttlo.taxon, kingdom_taxa_taxon_list_id=cttlk.id, kingdom_taxon=cttlk.taxon FROM master_list_paths mlp JOIN descendants nu ON nu.id=mlp.id LEFT JOIN cache_taxa_taxon_lists cttlf ON cttlf.taxon_meaning_id=ANY(mlp.path) and cttlf.taxon_rank='Family' AND cttlf.taxon_list_id=0 AND cttlf.preferred=true AND cttlf.allow_data_entry=true LEFT JOIN cache_taxa_taxon_lists cttlo ON cttlo.taxon_meaning_id=ANY(mlp.path) and cttlo.taxon_rank='Order' AND cttlo.taxon_list_id=0 AND cttlo.preferred=true AND cttlo.allow_data_entry=true LEFT JOIN cache_taxa_taxon_lists cttlk ON cttlk.taxon_meaning_id=ANY(mlp.path) and cttlk.taxon_rank='Kingdom' AND cttlk.taxon_list_id=0 AND cttlk.preferred=true AND cttlk.allow_data_entry=true WHERE mlp.external_key=u.external_key AND (COALESCE(u.family_taxa_taxon_list_id, 0)<>COALESCE(cttlf.id, 0) OR COALESCE(u.family_taxon, '')<>COALESCE(cttlf.taxon, '') OR COALESCE(u.order_taxa_taxon_list_id, 0)<>COALESCE(cttlo.id, 0) OR COALESCE(u.order_taxon, '')<>COALESCE(cttlo.taxon, '') OR COALESCE(u.kingdom_taxa_taxon_list_id, 0)<>COALESCE(cttlk.id, 0) OR COALESCE(u.kingdom_taxon, '')<>COALESCE(cttlk.taxon, '') ); UPDATE cache_occurrences_functional u SET family_taxa_taxon_list_id=cttl.family_taxa_taxon_list_id, taxon_path=mlp.path FROM cache_taxa_taxon_lists cttl -- Ensure only changed taxon concepts are updated JOIN descendants nu ON nu.id=cttl.preferred_taxa_taxon_list_id JOIN master_list_paths mlp ON mlp.external_key=cttl.external_key; WHERE cttl.id=u.taxa_taxon_list_id AND (COALESCE(u.family_taxa_taxon_list_id, 0)<>COALESCE(cttl.family_taxa_taxon_list_id, 0) OR COALESCE(u.taxon_path, ARRAY[]::integer[])<>COALESCE(mlp.path, ARRAY[]::integer[]));
Unable to Complete Request
You can go to the home page or try again.
# records affected
Total 27
Delete(s) 9
update(s) 7
insert(s) 0
final update(s) for setup 0
final update(s) for Taxon paths 0So, I emptied the work_queue table and tried to run the scheduled tasks again, thinking I should do that first. But the SQL error prevents it from completing.
Okay, thanks to John for helping here. The work_queue problem was due to using a dev version of the code which had an error. The lack of recorders information in the cache_samples_nonfunctional table seemed to be the consequence of bulk importing data and having the created_by_id field set to '1'.
So - probably an issue that was unique to my set-up.
Forum → Indicia Development → Recorders not being written to cache_samples_nonfunctional
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.