Troubleshooting: Error message in the database tier migration log

Database migration logs an error after running an SQL statement for member roles.

Problem

During database tier migration, the migration script runs the following SQL statement:


INSERT INTO MBRROLE ( MEMBER_ID, ROLE_ID, ORGENTITY_ID) SELECT USERS_ID,
(SELECT ROLE_ID FROM ROLE WHERE NAME = 'Registered Customer'),
-2001 FROM USERS WHERE REGISTERTYPE = 'R'

When this SQL statement is run, you receive errors similar to the following ones in the database tier migration log file:

DB2

SQL0407N Assignment of a NULL value to a NOT NULL column
"TBSPACEID=2,
TABLEID=266, COLNO=1" is not allowed. SQLSTATE=23502
Oracle


... cannot insert NULL into ("xxxx"."MBRROLE"."ROLE_ID")
...

Solution

Check the messages.txt file in directory WC_installdir/logs.

If you see a message similar to the following, it indicates that a unique constraint was violated on the PROFILE table:


... on record [profile:
[PROFILE_ID=13][STORE_ID=0][DEVICEFMT_ID=-3]
[TRANSPORT_ID=1][MSGTYPE_ID=125][USERSVIEW=N][LOWPRIORITY=0][HIGHPRIORITY=0]
[ARCHIVEMSG=0]].

This can occur if you have deleted and recreated a message type configuration using the Administration Console. For example, the following message indicates that you recreated a message type configuration:

Messages for a authorized order.

To correct this problem, complete the following steps:

  1. Determine the profile ID that is in conflict with the WebSphere Commerce Version 6.0 bootstrap data using the query:
    
    select profile_id from profile where store_id=0 and transport_id=1
    and msgtype_id=125
    
    Note: Your msgtype_id value could be different than is shown in this example.
  2. Delete the profile that is returned from this query.