The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PREVIEW_SESSION_ITEMS_SESSIONS"

Question

Given: trying to fix XPM in existing installation (means: configuring anew is not currently an option).

Clicking [Update Session Preview] after considerable timeout gives an error notification in CME:

Unable to update or add Binaries using OData Service.
An error occurred while processing this request.
[..]
Unable to read session data!

In cd_core*.log it corresponds to the following excerpt:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PREVIEW_SESSION_ITEMS_SESSIONS".
The conflict occurred in database "*broker_db_em*", table "dbo.PREVIEW_SESSIONS", column 'PREVIEW_SESSION_ID'.

Did so far:

1. Checked that a relevant configuration files match between website and webservice
2. Experimented with <Timeout> parameter of the Wrapper config
3. Found out that exactly the same error can be forced manually by trying to INSERT into PREVIEW_SESSION_ITEMS when parent record in PREVIEW_SESSIONS doesn't exist.

The last point gives a hint that somehow PREVIEW_SESSIONS table is cleared asynchronously while PREVIEW_SESSION_ITEMS is still being populated. But i wasn't able to find what causes the former table to be truncated.

Thus my question: what is the possible cause of this error?

My guesses:

1. Incorrect configuration
2. Products version mismatch
3. Something else environment-specific.

Any clue or possible direction? Thanks!

Answer 1

Based on the error you are getting

The INSERT statement conflicted with the FOREIGN KEY constraint

"FK_PREVIEW_SESSION_ITEMS_SESSIONS".
The conflict occurred in database "*broker_db_em*", table "dbo.PREVIEW_SESSIONS", column 'PREVIEW_SESSION_ID'.

As you already mention, is an inconsistency in the database

I would suggest to recreate the "broker_db_em" database if possible and monitor the tables PREVIEW_SESSION_ITEMS and PREVIEW_SESSIONS. That will be the faster to fix the issue.

A record should be deleted from Both tables should at the same time.

In the past I have faced similar inconsistency issues (not related to the XPM / Broker preview database), but with the Broker when for instance was pointed accidentally from a different environment, so I think the best is to start with a fresh "broker_db_em"