Appreciations accepted

Vladlen Litvinov, the author: If you have some job offer for me, I'm ready to discuss it. View Vladlen Litvinov's profile on LinkedIn

Password

Thursday, November 28, 2013

ORA-01407: cannot update ("BPMDB"."LSW_SNAPSHOT"."LAST_MODIFIED_BY_USER_ID") to NULL

Something else about a bugtracking in BPM. I have thought about an issue recently.
After transfering of PA from one Process Center to another and its starting we got such error:


[11/21/13 11:50:00:464 MSK] 0000020d wle_engine    E com.lombardisoftware.server.ejb.bpd.BPDEngineServicesCore doStartBPD doStartBPD on project XXXXXXXX    com.ibm.bpm.pal.PALException: Failed to execute advanced transition, Error: PreparedStatementCallback; uncategorized SQLException for SQL [update LSW_SNAPSHOT set CREATED_ON = ?,NAME = ?,ACRONYM = ?,DESCRIPTION = ?,SEQ_NUM = ?,ERROR_COUNTER = ?,ORIG_CREATED_ON = ?,ACTIVATED_ON = ?,STATUS = ?,ARCHIVED_ON = ?,LAST_MODIFIED = ?,REPOSITORY_BRANCH_ID = ?,CREATED_BY_USER_ID = ?,BRANCH_ID = ?,PROJECT_ID = ?,LAST_MODIFIED_BY_USER_ID = ?,CHANGE_DATA = ?,IS_ACTIVE = ?,IS_BLA_ACTIVE = ?,IS_STARTED = ?,IS_INSTALLED = ?,IS_BLA_INSTALLED = ?,IS_TIP_DEPLOYED = ?,IS_DEFAULT = ?,TIP_DEPLOYMENT_IN_PROGRESS = ?,IS_ARCHIVED = ? where SNAPSHOT_ID = ?]; SQL state [72000]; error code [1407]; ORA-01407: cannot update ("BPMDB"."LSW_SNAPSHOT"."LAST_MODIFIED_BY_USER_ID") to NULL
; nested exception is java.sql.SQLException: ORA-01407: cannot update ("BPMDB"."LSW_SNAPSHOT"."LAST_MODIFIED_BY_USER_ID") to NULL


Certainly the PA did not work.
I traced SQL using WAS trace. It showed: before the SQL query

update LSW_SNAPSHOT set CREATED_ON = ?,NAME = ?,ACRONYM    


a previous SQL query is executed for select one USER_ID from LSW_USR_XREF. Something like this:


select USER_ID from LSW_USR_XREF where USER_NAME = ..... Tra-ta-ta-tam .... 'server:myhostNode01Cell_myhostNode01_server1'


What's it? Only IBM knows. Naturally, there was no this user in our repository and we get the error when UPDATE query attempts to insert NULL in not-NULL column.
I created this user (format of its name - server:<CellName>_<NodeName>_<ServerName>) in the internal repository and started our PA. It started fine.

3 comments:

  1. Hi... we are facing a similar problem with BPM 7.2 when patch is applied to upgrade it to 7.2.0.5.
    This has become a mission critical issue for us with a deadline to meet. Deeply appreciate much needed help from you in advance !

    It is not able to update environment variables from a BPD Process Box (Lombardi Process) because of NULL value for LSW_ENV_VAR_VAL (table).LAST_MODIFIED_BY_USER_ID (column).

    I am getting user name messages in logs:

    E:\IBM\WLE720\AppServer\profiles\Lombardi\logs\ffdc\twprocsvr_2430243_15.05.09_13.53.37.94630915018623720540.txt (2 hits)
    Line 596: 6c9d6c9d PSTMT: select user_name from lsw_usr_xref where user_id = ? 1003 1007 0 0 0
    Line 707: 1c511c51 PSTMT: select t0.USER_ID,t0.USER_NAME,t0.FULL_NAME,t0.PROVIDER from LSW_USR_XREF t0 where UPPER(USER_NAME) = ? 1003 1007 0 0 0

    Q1. Where do I find the username which I need to add internally ?

    Q2. How do I add it internally ? Do I need to inser that user id using Process Admin, Add User ? Do I need to assign it to any group by the way in Process Admin ?
    OR, do I need to write insert script to enter the user name in lsw_usr_xref table ?

    Best Regards,
    Somnath
    som.engg@gmail.com

    ReplyDelete
  2. Hello Somnath,
    unfortunately we don not use WLE/BPM 7.2 because it is not supported more. In BPM 7.5 and higher you can find the name using *=info: org.springframework.jdbc.core.*=all trace.
    But if you use Oracle I can advice you the best way. Switch on the trace of BPM sessions with saving of BIND-information. Oracle logs all queries with their parameters. You can do it using Enterprise Manager console.

    ReplyDelete
  3. If I am not mistaken the log of SQL in WLE could be switched on by

    category name="org.springframework.jdbc.core.JdbcTemplate" additivity="false"
    priority value="debug"

    in log4j.xml

    If you could find the username you can create the user using Process Admin or direct query.

    ReplyDelete