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.
···
On Wed, Jul 22, 2015 at 11:20 AM, Jason Pickering jason.p.pickering@gmail.com 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
Timothy Harding
RPCV Vanuatu
Skype: hardingt@gmail.com
+1 (541) 632-6623
On Wed, Jul 22, 2015 at 7:57 PM, Timothy Harding hardingt@gmail.com 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:
I’ve created several attributes in the attribute menu:
When I go to create a program to utilize these:
I get the exception alert:
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@gmail.com
+1 (541) 632-6623
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–