Friday 16 November 2012

Misleading "The record already exists" message

The error message "The record already exists" is quite common in AX especially during development.
Point 1 - 3 is quite common, the reason for this post was Point 4, which is new to me and we just hit that error yesterday.

These are the few area/direction that we usually start investigating:

  1. Unique index
    This is the most common reason, one of the unique index field has duplicated value.
    This can easily be checked by browsing the record with table browser.
     
  2. Uncommitted record
    If the error happen during a process, it is possible that the record has been inserted once, but due to it hasn't been commited yet, if you browse the data with table browser in another AX session, you won't see it.

    One of the method is to select the data from SQL with the option
    WITH(NOLOCK).
    Eg.
    SELECT * FROM TableA WITH(NOLOCK) WHERE FIELD1 = 'abcd'

    This has been quite useful during debugging large process. Eg. Inventory posting, movement, etc.
     
  3. Duplicated RedId
    This usually won't happen, but during development with a team of developers, you might not know if someone has done something directly from SQL (Eg. moving records between environment, DTC from one place to another)

    After AOS is started up, when the first time it insert record into a table, it will first retrieve the value from SystemSequences.NextVal, then update it by add 250 to it. So the AOS cached 250 RecId for this table and use it for subsequent insert. When this cached RecId is finished, it will then read and do the same thing again.

    This duplicated RecId happens when the SystemSequences.NextVal is lower than the current max(RecId) of the table where the record is going to be inserted (target table). If there're gap between the RecId, the error might not occur immediately, but eventually it will.

    Eg.
    SystemSequences.NextVal = 5678900001
    AOS cached the RecId between 5678900001 - 5678900250, then updated SystemSequences.NextVal = 5678900251
    The max(RecId) in the target table is 5678900747

    If the RecId in the target table doesn't have gap, it will hit error immediately when it try to insert (5678900001, 5678900002, 5678900003, and so on from the 250 allocated/cached RecId).
    If there're gap, depends on the RecId allocated from AOS and the existing RecId in target table, it eventually will hit the error.

    But the error message in AX will be misleading when the error occur.
    AX tends to show the record already exist with the value from TitleField1 and TitleField2.

    To fix this, update the SystemSequences.NextVal = Target table's max(RecId) + 1, then restart the AOS. Beware of multi-AOS environment, each AOS cache their own set of 250 RecId, you'll have to restart all AOS.

    *CAUTION: Only update the SystemSequences record where the field TABID = <Table Id>. Only one record.
     
  4. Default value in table definition has been altered
    Unfortunately I can't replicate the error message, but I'll try to explain what we hit yesterday.


    We got an error message of "The record already exists", so the first thing is to check the above 3 reasons, but none of that is the main cause.

    We then check the Event log and seen an error message complaining cannot insert NULL value into CREATEDDATETIME field. So we got an error message in AX that's different than the one showing up in Event log.
     

    Eg. 
    Object Server 02: The database reported (session 3 (PChan)): [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'CREATEDDATETIME', table '<dbname .dbo.tablename=".dbo.tablename">'; column does not allow nulls. INSERT fails.

    When the "CreatedDateTime" property of the table in AX is set to Yes, AX automatically create the field in SQL with a default value definition. But somehow, a mystery person tampered with the table (maybe by copy table between environment directly through SQL) and forgotten this, the default value definition is missing and that has cause the error. The screenshot below, on the left, is the tempared table where the default definition is missing, on the right is the correct version.




2 comments:

  1. #1 was the issue for me. Our vendor has set index PartyIdx to not allow duplicates.
    I simply corrected this and issue vanished.

    Thank you.

    ReplyDelete
  2. Great post .. it resolved my issue in production...

    ReplyDelete