Forums » Help »

Problem with copying from existing workflow

Added by Matthew Rupert about 2 years ago

I created a new workflow this morning but ran into a problem when I went to copy it from an existing workflow. It seems that I was getting an error because it was trying to insert a null value as the TRACKER_ID, and I'm guessing this is because the tracker wasn't yet created and yet it was trying to copy the workflow anyway.

So this query raises an error:
INSERT INTO workflows (tracker_id, role_id, old_status_id, new_status_id)
SELECT 10004, 10002, old_status_id, new_status_id
FROM workflows WHERE tracker_id = 10003 AND role_id = 10002

Or maybe its just that ID wasn't created? I'm not sure, but I got the error:

Error: ORA-01400: cannot insert NULL into ("xxxxxx"."WORKFLOWS"."ID"): INSERT INTO workflows (tracker_id, role_id, old_status_id, new_status_id) SELECT 10004, 10002, old_status_id, new_status_id FROM workflows WHERE tracker_id = 10003 AND role_id = 10002

Replies (5)

RE: Problem with copying from existing workflow - RESOLVED - Added by Matthew Rupert about 2 years ago

I'm using Oracle, which isn't supported, but still, this may be of help to some people. For whatever reason there was no trigger created (no triggers were created). I don't know enough about databases, but perhaps MySQL and Postgres don't need triggers to auto increment a sequence?

Anyway, I created a trigger and this resolved my problem:

create trigger workflows_insert_trigger
before insert on workflows for each row
begin
select workflows_seq.nextval into :new.id from dual;
end;
commit;

RE: Problem with copying from existing workflow - Added by Etienne Massip about 2 years ago

Could you add this to the Howtos page, that could be very useful ?

RE: Problem with copying from existing workflow - Added by Matthew Rupert about 2 years ago

Yep, will do. Also, I changed the trigger:

CREATE OR REPLACE TRIGGER workflows_before_insert
BEFORE INSERT
ON WORKFLOWS
FOR EACH ROW
BEGIN
if :new.id is null then
SELECT workflows_seq.nextval INTO :new.ID FROM dual;
end if;
END;
/

RE: Problem with copying from existing workflow - Added by e tec over 1 year ago

Hi,

I am also trying to get Redmine working with an Oracle Enterprise DB (11.2.0.2.0 - 64bit). I've been applying the fixes from your blog and from another users patch posted somewhere on this site. Thank you for all your tips.

When I try the above sql statement, I am getting:

Warning: Trigger created with compilation errors.

I am entering the query into sqlplus.

How would I go about troubleshooting this?

When I restart apache I am still getting an error trying to copy over a workflow.

Thanks for any help!

(1-5/5)