Clarity

Hi All, 

I've quite an urgent issue which I'd like to ask for your help. 
I know the root-cause but have no clue where it's coming from. 

Here is my analysis (We are on Clarity 13.3):

When tried to update PM at project, we got an error:

When checked app logs, following error is it:

ERROR 2017-01-11 15:03:23,071 [http-bio-29082-exec-346] niku.xql2 (clarity:admin:86798863__3E6E8816-7A1F-42AA-A33C-545FBB7AC171:projmgr.projectPropertiesPost) Internal Processing exception
com.niku.union.persistence.PersistenceException:
SQL error code: 1422
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "NIKU.ODF_AUD_5003062_FCT", line 5
ORA-06512: at "NIKU.T_PROJECT_IINV_INVESTMENTS", line 56
ORA-04088: error during execution of trigger 'NIKU.T_PROJECT_IINV_INVESTMENTS'

Executed:
/*clarity\admin|projmgr.projectPropertiesPost|odf-pmd.project.update.map|odf-pmd.project.update.inv_investments*/
update inv_investments set schedule_start = decode(?, 2, schedule_start, ?), ......MANAGER_ID = decode(?, 2, MANAGER_ID, ?), RISK = decode(?, 2, RISK, ?) ....
.
.
.
Using input:
{forecast_mirr=, planned_cst_capital_total=.... manager_id=5295796.0, last_updated_by_p=5005745.0, forecast_cst_total=, last_updated_date=Tue Jan 10 17:03:23 ...... hen_project_hist_com=, hen_main_benef=, odf_pk=5752583.0}

The root-cause of this error is Manager_ID value which instead of standard number(integer) value has a decimal value instead! There ale also other decimal values instead of number (last_updated_by, odf_pk). 

Note: Trigger NIKU.T_PROJECT_IINV_INVESTMENTS where the log mentioned the error contained update of CMN_AUDITS table where Project Manager attribute is audited. It is executed function NIKU.ODF_AUD_5003062_FCT there behind which is a query which selects Project managers from cmn_sec_users table. IDs in cmn_sec_users table are standard type number and we haven't done any changes there of course

Have you ever seen that and do you know what source/setting this can come from? 

Thanks a lot for your hints!

Matej

The "exact fetch returns more than requested number of rows" Oracle error that is being raised is usually caused where you have some sort of SQL statement that says something like 

something = ( select something from somewhere where somecondition )

in that SQL is expecting the somecondition to get exactly one row, but it is returning more than one - so the exception is raised.

Obviously this is stock audit code that is raising the exception (so BUG maybe?) but perhaps that might help identify the cause (if you were willing to try to start debugging CA's code / your data that is!) - just trying to suggest that I would not be convinced that the "decimal" issue is the problem (so it might be nothing to do with the manager id either - are you auditing any strange custom fields (lookups) perhaps?).

Hi Dave, 

Yes I know the error explanation but still I think I'm right in my investigation. I've enabled trace log to proove it. 

Full trace is attached. After executed my shortened trace I got the same error because inv_investments table cannot be updated with manager_id

with decimal number:

We are auditing almost all attributes at project object but haven't encountered any error with auditing.

Matej 

Need to see what is happening at line 5 of the ODF_AUD_5003062_FCT (triggered by the update) on the database - (this is what I mean by debugging CA's code  )

Hello Dave,

thanks for quick reply.

As you have mentioned issue was in this function which returned more than one row and it has nothing to do with decimal number (this is probbably related to some parameters settings in trace or log ).

Function call this select:

select unique_name into V_RETURN_VALUE from (Select
u.id user_id,
u.last_name||','||' '||u.first_name unique_name,
u.first_name first_name,
u.last_name last_name,
u.user_name user_name
from cmn_sec_users u
inner join srm_resources r on r.user_id = u.id
inner join cmn_sec_user_groups ug on u.id = uG.user_id
inner join cmn_sec_groups g on G.ID = ug.group_id
where 1=1) l where user_id=P_VALUE;
RETURN V_RETURN_VALUE;
Tricky thing is that function is auto generated by clarity when is set audit trail on attribute manager. And I have no idea yet why are there joined other tables there, which caused multiple rows output.

Marian

^ indeed, that SQL above would not return unique values if a parameter (P_VALUE) user were a member of more than one access group ; and that is clearly a common situation.

I would wonder if that audit logic is actually auditing a custom field with a custom NSQL-lookup with that strange non-unique SQL being part of that NSQL-lookup ; (this is why I raised the earlier point about auditing any "strange custom fields").

(GUESSing obviously though)

well this is related to audit of OOB attribute "manager". 

Frankly, if you are just going to argue with me, I'll stop making suggestions on how to do some investigation!

Perhaps someone has customised the manager lookup?

The audit trigger in my local system for "manager_id" does not contain the same SQL as above. Perhaps turn auditing off for manager_id, then turn it back on again - see if the trigger SQL changes (will probably be in a different function then though so you'd have to trace which one by looking at the triggers on investment (T_PROJECT_IINV_INVESTMENTS I think).

I'm sorry, I did not mentioned it as argue, I just wanted to say that it is related to audit of "manager" attribute.

I've turned off audit of manager, renamed related function, and turned audit on. New generated function has the same (wrong) query.

As a workaround I've manually changed the query manually on test and it works.

So the issue is not that urgent now.

Unfortunately we have highly customized Clarity implementation here so I'm afraid that you will not be able to reproduce this issue.

Last manager_id is logged in CMD_AUDITS on 9-Jan (and same is the created date of ODF_AUD_5003062_FCT ) so I hope we will find what was changed meantime.

Thanks for your support.

We'll keep the community informed about the root cause.

Marian.

Ok good - think I'd now be looking at any customisation of the stock 'Browse Resource' lookup to see if that is where the bad SQL is coming from.

Hi Dave, 

Sorry for replying so late, I was out of office. If we can get back to our issue, we detected that it's causing auditing of Manager attribute of project as Marian reported. As you properly assumed we have custom lookup behind this attribute. Yes we know it's not proper way of customization of Clarity but unfortunately we did not managed that trick. We just took over the maintenance of Clarity a few years ago and keep all customization untouched. All new changes in Clarite are done only by configuration or by standard processes/jobs etc. As this lookup is there for a few years without any change, it has worked properly before (till now). Our question was only how it's possible that the SQL code behind it has changed? (Changes seen in the background execution of Clarity and also in logs). This we not understand. 

The lookup code (query) behind Manager attribute is almost the same as the mentioned function.

Plus is a group code browsed for (manager - Line 12):

Select
@select:u.id:user_id@,
@Select:u.last_name||','||' '||u.first_name:unique_name@,
@Select:u.first_name:first_name@,
@Select:u.last_name:last_name@,
@Select:u.user_name:user_name@
from cmn_sec_users u
inner join srm_resources r on r.user_id = u.id
inner join cmn_sec_user_groups ug on u.id = uG.user_id
inner join cmn_sec_groups g on G.ID = ug.group_id
where @FILTER@
@BROWSE-ONLY:
and g.group_code = 'hen_project_manager'
:BROWSE-ONLY@

Thanks a lot, 

Matej

So yes, this "customisation" has broken the audit functionality.

You need to either turn audit off for that attribute, or fix the customisation, or fix the audit code (a further customisation!).

I would want to put back in place the stock lookup myself. Not really sure how someone managed to change the system lookup though - perhaps through XOG, perhaps by updating some of the installation files as part of an install or upgrade?

Good luck! 

Hi Dave, 

Yes you are right, but In spite of the fact this is a customisation, It worked well for a years....So I'd say something happened which affects that customization, not the customization itself is "broken".... We've of course turned off audit of this field but it's only a temporary workaround. 

How this lookup has been updated by the 3rd company (which customized and maintained Clarity before a years) I don't know, but well, there is some way how to do  - see our case. If you would like, send me a private message and you can ask them directly. I'm sure you would know that company  

Nevertheless, I'd rather get back to my original question: How is it possible that the system puts decimal IDs of number/integer values and where it could come from??

I'm really sure, there should be only integer values in IDs. I've just found some old (some months ago) app-niku log on our Test server/instance:

P.S. Maybe also nick_darlington could post his hints on that?

Thanks a lot, 

Matej

The "decimal places" issue is irrelevant/meaningless I think - thats just the way that whatever code is outputting that error condition to the error handler is rendering a numeric value.

Why you audit problem might only suddenly arise recently could be to do with data (i.e. its the first time that it has tried to audit some data that throws up the duplicate value condition), or possibly the audit code has recently been re-generated (not sure when the application does this, perhaps if you have added a new attribute to your list of audited ones).