dhis2-devs team mailing list archive

[Timothy Harding <hardingt@xxxxxxxxx>]

Hello Jason,

Thanks for the speedy response! I just checked and yes, these two tables
are very different from ours and a vanilla 2.17.

Correct:
  programtrackedentityattributeid integer NOT NULL,
  trackedentityattributeid integer,

Current:
  programattributeid integer NOT NULL,
  attributeid integer,

I tried running the 2.14-2.15 update script:
https://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql
because some of the operations looks like they were missed, such as:

ALTER TABLE program_attributes RENAME COLUMN programattributeid TO
programtrackedentityattributeid;
ALTER TABLE program_attributes RENAME COLUMN attributeid TO
trackedentityattributeid;

but it ended up making a mess of the db (a test db only).
So what should look like:
CREATE TABLE program_attributes
(
  programtrackedentityattributeid integer NOT NULL,
  trackedentityattributeid integer,
  displayinlist boolean,
  mandatory boolean,
  allowfuturedate boolean,
  programid integer,
  sort_order integer,
  CONSTRAINT program_attributes_pkey PRIMARY KEY
(programtrackedentityattributeid),
  CONSTRAINT fk751765f2a3100c9f FOREIGN KEY (programid)
      REFERENCES program (programid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_program_attributeid FOREIGN KEY (trackedentityattributeid)
      REFERENCES trackedentityattribute (trackedentityattributeid) MATCH
SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Now looks like:
CREATE TABLE program_attributes
(
  programattributeid integer NOT NULL,
  attributeid integer,
  sort_order integer,
  displayinlist boolean,
  programid integer,
  programtrackedentityattributeid integer NOT NULL,
  trackedentityattributeid integer,
  mandatory boolean,
  allowfuturedate boolean,
  CONSTRAINT program_attributes_pkey PRIMARY KEY (programattributeid),
  CONSTRAINT fk751765f2a3100c9f FOREIGN KEY (programid)
      REFERENCES program (programid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_program_attributeid FOREIGN KEY (attributeid)
      REFERENCES patientattribute (patientattributeid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Not quite sure what to do from here.


Timothy Harding
RPCV Vanuatu
Skype: hardingt@xxxxxxxxx
+1 (541) 632-6623

On Wed, Jul 22, 2015 at 11:20 AM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Tim,
> I think the problem here is that the key was not updated properly in your
> version.
>
>            Table "public.program_attributes"
>              Column              |  Type   | Modifiers
> ---------------------------------+---------+-----------
>  programattributeid              | integer | not null
>  attributeid                     | integer |
>  sort_order                      | integer |
>  displayinlist                   | boolean |
>  programid                       | integer |
>  programtrackedentityattributeid | integer | not null
>
> I am pretty sure that the column "programattributeid" has been superceded
> by " programtrackedentityattributeid " and should have been handled by an
> upgrade script.
>
> Are you  (or perhaps me) sure you followed the upgrade procedure properly?
>
> Maybe Abyot can comment on whether this column can be safely deleted.
>
> Regards,
> Jason
>
>
> On Wed, Jul 22, 2015 at 7:57 PM, Timothy Harding <hardingt@xxxxxxxxx>
> wrote:
>
>> Hello!
>>
>> I'm seeing an exception when trying to create or edit programs with
>> registration attributes.
>>
>> The system I'm using is running 2.17:
>> [image: Inline image 1]
>>
>> I've created several attributes in the attribute menu:
>>
>> [image: Inline image 2]
>>
>> When I go to create a program to utilize these:
>>
>> [image: Inline image 3]
>>
>> I get the exception alert:
>> [image: Inline image 4]
>>
>> CATALINA.OUT:
>> * INFO  2015-07-22 10:42:29,903 'tharding' create
>> org.hisp.dhis.program.Program, name: testprogram, uid: l29CWe5j4LA
>> (AuditLogUtil.java [http-bio-8080-exec-2])
>> * WARN  2015-07-22 10:42:29,912 SQL Error: 0, SQLState: 23502
>> (SqlExceptionHelper.java [http-bio-8080-exec-2])
>> * ERROR 2015-07-22 10:42:29,912 ERROR: null value in column
>> "programattributeid" violates not-null constraint
>>   Detail: Failing row contains (null, null, null, t, null, 117704,
>> 117700, f, f). (SqlExceptionHelper.java [http-bio-8080-exec-2])
>> * ERROR 2015-07-22 10:42:29,913 Error while executing action
>> (ExceptionInterceptor.java [http-bio-8080-exec-2])
>> org.springframework.dao.DataIntegrityViolationException: could not
>> execute statement; SQL [n/a]; constraint [programattributeid]; nested
>> exception is org.hibernate.exception.ConstraintViolationException: could
>> not execute statement
>> (...)
>>
>> As a result, we now have a program created, but with no attributes. Any
>> attempt to add an attribute (via editing the created program) yields the
>> same result and same error.
>>
>> If the above query is referring to the program_attributes table (just a
>> guess) it likes it is missing a few FKs (attributeid, programid), and
>> for some reason it isn't grabbing a new sequence (programattributeid).
>>
>>  null
>>
>> programattributeid integer NOT NULL,
>>
>> null
>>
>>   attributeid integer,
>>
>> null
>>
>>   sort_order integer,
>>
>> t
>>
>>   displayinlist boolean,
>>
>> null
>>
>>   programid integer,
>>
>> 117704
>>
>>   programtrackedentityattributeid integer NOT NULL,
>>
>> 117700
>>
>>   trackedentityattributeid integer,
>>
>> f
>>
>>   mandatory boolean,
>>
>> f
>>
>>   allowfuturedate boolean,
>>
>> Please advise, and thanks!
>>
>>
>> Timothy Harding
>> RPCV Vanuatu
>> Skype: hardingt@xxxxxxxxx
>> +1 (541) 632-6623
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>