Troubleshooting: Error message in the database tier migration log

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:

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

... 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.