0

I have been using strategy=GenerationType.TABLE for my project entities for some years. This works really well and it is not very common to get errors. But from time to time, some procedure has been unable to insert in the database and gets MySQLIntegrityConstraintViolationException: Duplicate entry error with low concurrency. The big issue is when somehow that table remains bugged and fails every try, trying to insert using the same ID number all the time.

In the end, I have to restart the server to solve the issue.

By default, eclipselink uses allocationSize=50. I use the default, but however it doesnt seem to be just a concurrency problem because the server has not high concurrency, and later tries should work.

@Id
@TableGenerator(name="TABLE_GEN_TABLE1", table="sequence", pkColumnName="SEQ_NAME", valueColumnName="SEQ_COUNT", pkColumnValue="table1")
@GeneratedValue(strategy=GenerationType.TABLE, generator="TABLE_GEN_TABLE1")
@Column(name = "id")
long id;

I also have sequence connection pool activated in persistance.xml

<property name="eclipselink.jdbc.sequence-connection-pool" value="true"/>
vrivon
  • 65
  • 8
  • You will want to check the values in the database when you see this issue, and turn on SQL logging (with parameters) to see what EclipseLink gets from the sequence and is using in its inserts. I don't know of any issues with concurrency in EclipseLink, but make sure you have specified a sequencing connection pool if you are using JTA (see https://www.eclipse.org/eclipselink/documentation/2.6/concepts/data_access006.htm ), as the table sequence updates may rollback in your JTA transaction and cause this. – Chris Nov 21 '22 at 16:08
  • Hi Chris. I have the sequencing connection pool. What I've got is a situation that happens "eventually" so I cannt activate SQL logging. Otherwise, I would get a terabyte log file before this error happens again. What I have checked it that when the error happens, it is the same table all the time. Might be something related to the database instead JPA? – vrivon Nov 22 '22 at 10:23
  • Check the state of the table and sequence table when it happens, and the error itself should indicate the ID value attempted to be used. You might be able to figure out when the record with that ID already in the DB was inserted if it has any timestamps or something identifiable you can use to track the process that might have inserted it to figure out how it got in and yet JPA is using the same sequence value again. Could it be something else inserting outside of this JPA application, or a different config using that sequence table for inserts as well? – Chris Nov 22 '22 at 15:35
  • Figuring out why it happens and resolving the root cause might be separate from just correcting the problem once it occurs though. For the later, you may need an exception handler to catch the insert problem, make sure the constraint is the ID and that the ID value used is still the next one in the range to be assigned from EclipseLink (vs a legitimate application attempt to insert something that exists), and get the next value from it. See https://www.eclipse.org/forums/index.php/t/446947/ and https://stackoverflow.com/a/43399390/496099 which might also be useful for debugging. – Chris Nov 22 '22 at 15:47

0 Answers0