Unable to find duplicate System Properties to solve a java.sq.BatchUpdateException

Sebastien NUSS · 3 weeks ago

Hi,

I'm having a strange issue with the sys_properties table.
Following the cloning from our PROD to our TEST and DEV instance in late October.

I've tried to reimport 3 missing System Properties on our TEST instance.

When I tried to import the records from an XML export I ended up with the following messages.

java.sql.BatchUpdateException: (conn=3462) Duplicate entry '<sys_id>1' for key 'PRIMARY'
java.sql.BatchUpdateException: (conn=3470) Duplicate entry '<sys_id>2'for key 'PRIMARY'
java.sql.BatchUpdateException: (conn=3476) Duplicate entry '<sys_id>3' for key 'PRIMARY'

I searched for those 3 IDs with a GlideRecord Query in a BG script.
No records found.

I tried to create locally one of the Sys Prop. I got the same error.
java.sql.BatchUpdateException: (conn=2771) Duplicate entry '<sys_prop_name> for key 'name'.

I searched for the name of this property, and the 2 others in the table filter.
No records found.

I tried to import an update set with the 3 properties hoping it would allow me to manage the sys_id conflict or name conflit. Preview was OK. When I tried to commit the Update Set, I recieved an error and in the Update Set Log. I have the following error.

java.sql.BatchUpdateException: (conn=3608) Duplicate entry '<sys_id>3' for key 'PRIMARY'

When I try to read in list view the 500 records it's just an empty screen. Nothing, like a Business Rule hiding some data. Am I forgetting a Business Rule that hide System Properties to the admin role ?

Other interesting behaviours. The table row count is wildly different. 3467 row on TEST, 2977 on DEV. If I do a count of next(), in a BG script on both instances. I only have a difference of 16. 2960 for TEST, 2976 for DEV.

I've tried everything I can think of in the time I can use for this issue. I suppose I'm probably missing something.

Can anyone help me ?

Brad Bowman · 3 weeks ago

You could try the same scripts and actions in an out of box PDI to see if it's something with your instance. Look for a before Query business rule on the sys_properties table.

Sebastien NUSS · 2 weeks ago

No issue importing the data on a PDI.
No Before Query rule on sys_properties out of the box.
So yeah, like I expected, it's an instance issue. But I have to find a way to fix this or at least find a workaround.

Brad Bowman · 2 weeks ago

To clarify, the before Query business rule would be in your instance, and not out of box. You should also check your sys_properties.* ACLs for the read and query_range Operations vs a PDI, or in Prod if they are still showing there. You could also re-try the clone, after checking data preservers in the target instance and exclusion tables in the source.