Troubleshoot: 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") ...
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 WebSphere Commerce Administration Console. For example, the following message indicates that you recreated a message type configuration:
Messages for a authorized order.
To correct this problem...
select profile_id from profile where store_id=0 and transport_id=1 and msgtype_id=125
Your msgtype_id value could be different than is shown in this example.
Related tasks
Migrate the WebSphere Commerce database
Migrate the development environment database
Migrate the WebSphere Commerce database using the migration script