When a user create a list report on a table and runs the report few times, the below error shows up on the report.
"java.sql.BatchUpdateException: Duplicate entry 'rpt-temp1987616cdbb333005cf2f9361d961919_testuserwithaaverylongu' for key 'name'"
Release or Environment
Related to PRB1360735.
1. Issue can occur for any user with user_id longer than or equal to 40 characters.
2. A record is inserted into the sys_ui_view table when an user creates a list report on a table for the first time.
3. The name of the sys_ui_view record will be created as "rpt-temp<sys_id of the table's collection dictionary record (32 char)>_<userid of the user>".
4. Out-of-box, the max_length of the name field on sys_ui_view is "80".
5. Hence, on the 1st attempt, it tries to insert the record for the user. As size of the name exceeds the column size, the name is truncated and saved as "rpt-temp<32 char sys_id>_<39 chars userid>".
6. Next time, when the user tries to create a report on the same table, it checks to see if there is an existing record with the syntax "rpt-temp<32 char sys_id>_<user_id>".
7. As it cannot find one, it will try inserting the record. But, as the size of the column is 80, it truncates it as "rpt-temp<32 char sys_id>_<39 chars userid>" and tries to insert it resulting in the error being thrown.
As a workaround,
1. Update the max length of "name" field on sys_ui_view table to a higher value (eg: 200).
2. Delete any existing sys_ui_view records with name that contains a truncated user_id of the user. New sys_ui_view record with correct name will be created again when user creates a new report.
Note: We recommend testing this in sub-prod before implementing in Production.