1

Topic: Indicia warehouse initial database issue

Hi,

as a hobby entomologist I am working on a small scale monitoring project together with some friends. To get all the data together I decided to try and program/setup a website. Searching the web for a while I came across Indicia and I really like what I have seen so far. Seems to suite our needs perfectly. So this is my first contact with Indicia, hope you don't mind my beginner questions.

After reading the docs and trying for a while I managed to get things up to an extend but still fail with the warehouse. This is where I'd like to ask advice on what I am doing wrong.

Specs:
Debian 9
Apache 2.4.25
PHP 7.0.19
PostgreSQL 9.6.6
PostGIS 2.3.1

So webserver and database are up and prepared. Opening the warehouse setup all things are shown as green (worked through all that before) and the setup does install the database part correctly as it tells me "Congratulations". Then as the manual says I login as admin, reset the password and then instead of the welcome page I end up with the error below:

2018-01-07 19:43:15 +00:00 --- error: Uncaught Kohana_Database_Exception: There was an SQL error: ERROR:  Column »last_scheduled_task_check« does not exist 
LINE 1: SELECT sum(case when last_scheduled_task_check > now()-'1 da... 
                             ^ - SELECT sum(case when last_scheduled_task_check > now()-'1 day'::interval then 1 else 0 end) as new, sum(case when last_scheduled_task_check <= now()-'1 day'::interval then 1 else 0 end) as old 
FROM system 
WHERE last_scheduled_task_check is not null in file system/libraries/drivers/Database/Pgsql.php on line 342

Tested different versions (1.49.0, 1.53.0, master, develop) but keep ending up there. Looking through the code I figured that the database was populated with the very initial stuff but is missing a lot of updates. After some time I figured that upgrading the DB can be done by opening the following URI: .../warehouse/index.php/home/upgrade (I am not allowed to post URLs)

This took a couple of seconds and then failed with:

2018-01-07 20:02:35 +00:00 --- error: Error in ..../warehouse/modules/cache_builder/db/version_1_15_0/201611 
121607_cache_occurrences_id_diff_data.sql 
2018-01-07 20:02:35 +00:00 --- error: There was an SQL error: ERROR:  Relation »verification_rule_data« does not exist 
LINE 5: from verification_rule_data vrd 
             ^ - -- #slow script# 
 
update cache_occurrences_functional o 
  set identification_difficulty=vrd.value::integer 
from verification_rule_data vrd 
join verification_rules vr on vr.id=vrd.verification_rule_id and vr.deleted=false 
    and vr.test_type='IdentificationDifficulty' 
where vrd.header_name='Data' and upper(vrd.key)=o.taxa_taxon_list_external_key and vrd.deleted=false; 

Couldn't help myself other than commenting that SQL code in the sql file and trying to upgrade again via URI .../warehouse/index.php/home/upgrade

This time I got:

2018-01-07 20:13:41 +00:00 --- error: Error in ..../warehouse/modules/cache_builder/db/version_1_49_0/201711071410_cache_occurrences_view.sql 
2018-01-07 20:13:41 +00:00 --- error: There was an SQL error: ERROR:  Column o.confidential does not exist 
LINE 71:   o.confidential 
           ^ - CREATE OR REPLACE VIEW cache_occurrences AS 
SELECT o.id, 
  o.record_status, 
... 
  o.confidential 
  FROM cache_occurrences_functional o 
  JOIN cache_occurrences_nonfunctional onf on onf.id=o.id 
  JOIN cache_samples_nonfunctional snf on snf.id=o.sample_id 
  JOIN cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id; 

To me it feels like there is something that I am missing right from the start.

Thank you very much in advance!

Sebastian

2 (edited by Jim Bacon 10-01-2018 12:17:56)

Re: Indicia warehouse initial database issue

Hi Sebastian

Welcome to Indicia. Congratulations on getting this far. It sounds like you have been following the installation instructions at http://indicia-docs.readthedocs.io/en/l … ation.html. The notes offered by Paul Barrington at https://forums.nbn.org.uk/viewtopic.php … 805#p27805 will be very interesting for you if you haven't already seen them. See page 22 in particular. It is not clear to me whether he completed the database updates successfully (and note that he probably also failed to get scheduled tasks running). I haven't done a fresh installation recently so have not encountered these problems.

The set up process should install the basic database tables and then all the database updates need to be applied. It is often the case that one update will have a dependency on a previous one so they are applied in order by virtue of the file naming convention. Indicia has a set of core tables plus a suite of optional modules which also add tables. The errors you are getting relate to updates to the cache_builder module.

The first one occurs when the cache_builder module refers to the verification_rule_data table which is provided by the data_cleaner module. This cross-talk between modules is dangerous since an optional module may not be enabled. Looking at the default modules suggested by https://github.com/Indicia-Team/warehou … hp.example I see that data_cleaner is there. Do you have it in your config/config.php file? Can you see whether the table exists in your database?

The second error you get is because the confidential field is not found in the cache_occurrences_functional table. Again you can check your database to see if this is truly the case. The field should have been added by https://github.com/Indicia-Team/warehou … ential.sql. If the field is not there we have to question how that update was missed. You can see the history of database updates in the application/log files. For an example of how it should look, see if you can get in to the log of a recent automated build at https://travis-ci.org/Indicia-Team/ware … /326468849.

You might want to give yourself a clean start by dropping the database and going through the installation steps again. Any feedback you can provide to help us improve the installation notes and iron out installation bugs would be gratefully received.

Jim Bacon.

Edit: You may need to set $config['log_threshold'] = 4; in application/config/config.php to get useful data in the log files.

3 (edited by Sebastian 10-01-2018 20:18:45)

Re: Indicia warehouse initial database issue

Hi Jim,

thank you so much for your fast and extensive answer. Though I don't have the time to try it all out right now I am sure the information will greatly help in setting things up. I didn't know about Paul Barrington's notes yet. That will definitely help a lot. Much appreciated!

I am working for another open source project and so I kind of know how things go. Can't promise too much but I am keen to give feedback and possibly even send in pull requests on github to help improve Indicia.

Will get back on the weekend at the latest.

Sebastian

4

Re: Indicia warehouse initial database issue

Hi Sebastian,

Hope the information helps you make some progress. It is good to have someone with your experience trying it out. Be aware that speed of response fluctuates! I'm usually the contributor who responds first on the forum but I am away from the computer next week.

Jim Bacon

5

Re: Indicia warehouse initial database issue

Hi Jim,

I am pretty sure I can make this work with all the new information at hand. Thanks for letting me know about not being able to respond next week. Have a good one!

Sebastian

6

Re: Indicia warehouse initial database issue

Hi Jim,

thanks to the information you posted I could make my installation work now. I was able to build up and fix the database stuff by hand. Though I didn't have the time to really look into what exactly is broken in the scripts and how to fix this. I am fairly sure it's because the database changes for the different modules are sort of interdependent and need to be run in just the right order to work. So far I haven't looked into the PHP code on how this is done in Indicia.

I need to push my hobby project a bit forward before I can get back to those details. So I'll freeze this for now but will get back to it when I have more time.

Sebastian