So I plan to go through some of the stagingprop problems I’ve seen – especially the more common ones. This is probably the most common of all, especially if the java developers you’re working with have not used Commerce and particularly stagingprop before.
What this problem looks like:
The following appears in the stagingprop log. The location and name of the stagingprop log can vary, but is most frequently on the application server where the stagingprop is run from, in /opt/IBM/WebSphere/CommerceServer60/logs (for Commerce 6). The application server where stagingprop runs may be any server where commerce runs and has the databases involved cataloged. Stagingprop may run on a server that is not normally related to either of the database servers involved.
<Error> The SQL batch update failed.
<Error> The command failed to propagate the change related to the STAGLOG record 10787217.
10787217: INSERT into table policycmd - policy_id1000001, businesscmdclasscom.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl <scope merchant>
<SQLException> [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "WSCOMUSR.POLICYCMD" from having duplicate rows for those columns. SQLSTATE=23505
SQLState: 23505
Vendor Error Code: -803
Chained SQLException #1: [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "WSCOMUSR.POLICYCMD" from having duplicate rows for those columns. SQLSTATE=23505
Stack trace:
java.sql.BatchUpdateException: [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "WSCOMUSR.POLICYCMD" from having duplicate rows for those columns. SQLSTATE=23505
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:270)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(SQLExceptionGenerator.java:839)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.java:5118)
at com.ibm.commerce.staging.StagingProp.executeUpdate(StagingProp.java(Compiled Code))
at com.ibm.commerce.staging.StagingProp.propagateRows(StagingProp.java(Compiled Code))
at com.ibm.commerce.staging.StagingProp.transactRows(StagingProp.java:525)
at com.ibm.commerce.staging.StagingProp.propagate(StagingProp.java:467)
at com.ibm.commerce.staging.StagingProp.main(StagingProp.java:265)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
at java.lang.reflect.Method.invoke(Method.java:391)
at com.ibm.ws.bootstrap.WSLauncher.run(WSLauncher.java:219)
at java.lang.Thread.run(Thread.java:571)
<Error> The command will not retry due to the nature of the error.
<Error> The command failed to complete the propagation job.
This error message indicates that stagingprop has failed and has stopped processing. It indicates that the failure is caused by SQL0803 – a violation of a unique constraint on the prod or prod role server. That unique constraint may be the Primary Key or it may be some other constraint on the table.
The most frequent cause of this problem is that data was inserted directly into production in addition to being inserted on staging. That data in production may either be identical data to the data inserted into stage, or it may be data that it only has the same primary key. Data should never be inserted, changed, or deleted in the stagingproped tables on production. Let me say that again. Data should never be inserted, changed, or deleted in the stagingproped tables on production.
Determining Root Cause:
Query the row in both Stage and Prod and determine if it is identical or not:
Staging-role server:
$ db2 "select * from WSCOMUSR.POLICYCMD where policy_id=1000001 and businesscmdclass='com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl' with ur"
POLICY_ID BUSINESSCMDCLASS PROPERTIES OPTCOUNTER
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
1000001 com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl - -
1 record(s) selected.
Prod-role server:
$ db2 "select * from WSCOMUSR.POLICYCMD where policy_id=1000001 and businesscmdclass='com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl' with ur"
POLICY_ID BUSINESSCMDCLASS PROPERTIES OPTCOUNTER
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
1000001 com.ibm.commerce.payment.actions.commands.EditPaymentInstructionPolicyCmdImpl - -
1 record(s) selected.
If the rows are exactly identical, it indicates that the same data got inserted both to prod and to stage. You need to educate anyone with insert/update/delete access on the catalog tables to not insert or change data on the prod. We’re actually moving towards restricting access for developers on these tables.
If the rows are not exactly identical in every value, it indicates that some other unique constraint may be violated. The SQL0803 error message will tell you the specific index involved. Just query syscat.indexes for the tabschema, tabname, and iid specified in the SQL0803 error message.
Resolution:
In either case, the resolution must come from the developers. Once the situation has been described to them, they may ask that a row be eliminated from this stagingprop. To do that, first determine an unused positve value for stgprocessed that is greater than 1:
$ db2 "select stgprocessed, count(*) from wscomusr.staglog group by stgprocessed with ur"
STGPROCESSED 2
------------ -----------
0 244
1 654648
9 11
19 407
39 1049290
49 5
59 5
99 5
101 466
129 8
10 record(s) selected.
In this case, we’ll pick 139. Then update staglog for that particular row:
db2 "update staglog set stgprocessed=101 where stgrfnbr=10787217"
Setting stgprocessed to a positive value greater than 1 essentially tells stagingprop to skip this row, and yet retains the row in case we need it in the future. If you are properly pruning staglog, the row will be deleted once it is past your retention period. Do not skip rows in this manner until you have determined the cause of the failure.
In some scenarios, more complicated action may be needed. That may include a stagingcopy or restore to get the staging-role and the production-role databases back in sync. Move individual tables only if you know what you’re doing – there are a lot of details that can cause problems otherwise.
[…] went through the most common cause of SQL0803 back in a previous post. There’s a rarer cause that can look very similar, and I thought I’d list […]
[…] went through the most common cause of SQL0803 back in a previous post. There’s a rarer cause that can look very similar, and I thought I’d detail […]