0

I reproduced ordinal issue with foreign key constraint violation. I worked though this thread and I found it doesn't help me. What helped me is this one, reseeding the index

DBCC CHECKIDENT ('schema.customer', RESEED, 0);

After index was reseed, exactly the same junit test started to work.
I noticed that issue starts to reproduce after an exception thrown. In my case I'm developing a test, making some mistake that causes test to stop and unwind a thread after parent record was already inserted and child not. I fix the test, but it doesn't work because of constraint violation. Deletion of all data in parent and child tables do not help.

Junit test is spring integration test that creates test spring context with connection to MS SQL Server, mybatis mappers are autowired and used in the test. No explicit transaction management. Dummy record insertion and selection and data integrity validation.

Could you please explain me what happens here? I scare to reproduce the same in prod. I would not reseed index and delete all data there. The issue is intermittent, I came to the same 'infinite fail loop' several times. After index manipulation it start to work again.

When running several tests with insertions / removals issue started to reproduce 100% of times. I end-up with FK removal for all tables which fixed the issue permanently

Mike
  • 20,010
  • 25
  • 97
  • 140
  • It is normal for gaps in IDENTITY values so the output of `DBCC CHECKIDENT` does not indicate a problem. I suggest you run a SQL trace to see the actual SQL statements generated by mybatis. It should include a `SELECT SCOPE_IDENITY()` after the parent insert followed by an insert into the child table with that value. – Dan Guzman Apr 29 '21 at 09:49
  • I did, I can confirm that insert to the child table was populated with correct FK. which I was looking with my eyes on a sql developer while standing on a breakpoint in java code. This is exactly what the problem is. – Mike Apr 29 '21 at 13:45
  • Seems to me that the unit test (or the INSERT persistence) is not coded correctly. You should always populate the auto-generated ID value from the database, to account for any gaps in the number sequence. Databases produce gaps in the sequence and that is completely normal [and inexorable]. – The Impaler Apr 29 '21 at 16:42

0 Answers0