Select the search type
  • Site
  • Web
Search
You are here:  Support/Forums
Support

Bring2mind Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Forums Document Exchange Lite Extensions and addons
Last Post 08/25/2006 2:35 PM by  Greg Hill
DMX foreign key constraint error
 9 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Dan Peerenboom
New Member
New Member
Posts:14


--
08/22/2006 8:35 PM

    Hello Peter.

    We are getting a very odd error:

    INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DMX_Log_DMX_Entries'. The conflict occurred in database 'CDSN', table 'DMX_Entries', column 'EntryId'.

    How it happens: A user tries to upload a "New Item" (a pdf file) and after clicking "Update", get the error.

    We did some db debugging...

    1. We noticed that new records were being inserted into DMX_Entries, but not DMX_Log, which would account for the error.
    2. We turned off the constraint to see what would happen to the DMX_Log table after an upload without the constraint.
    3. Attempted another upload of a document. It was successful, in that a record was inserted in DMX_Entries with correct values, but...
    4. The DMX_Log table value for entId was 15, whereas the correct value should have been 230118 or something like that. I.e. The last record's EntryId in DMX_Entries table was 230118.
    5. We turned the constraint back to its orginal setting.
    6. We turned Identity off for EntryId in DMX_Entries so that we could change the EntryId of one of the new records to 15, so that we can avoid the foreign key constraint error and see what happens.
    7. We turned Identity back on for EntryId.
    8. Our upload test was successful in that the DMX_Entries table inserted values correctly, including EntryId being incremented to the next largest value, but...
    9. With a record in DMX_Entries (EntryId=15) to avoid the foreigh key constraint error, the file was uploaded, however, the value for EntId in DMX_Log was still 15!
    10. Repeated step 9. Oddly enough, not only does the DMX_Log still keep inserting a value of 15 for entId, but the file listing in the DMX module in the browser only shows the last file uploaded!
    11. More to report: When viewing the DMX module in DNN, clicking on icon of the disk or the original file name link reveals EntryId=15. How is that possible, considering the DMX_Entries.EntryId for that file was/is 230118 or something like that? Where is it generating that EntryId value of 15?

    I have no way of figuring out where it is getting/setting that value of 15 for DMX_Entries.EntryId or DMX_Log.EntId. Do you have any insight into this bizarre error?

    Regards,

    Dan
    Peter Donker
    Veteran Member
    Veteran Member
    Posts:4536


    --
    08/23/2006 12:12 AM
    Dan,
    Could you tell me which version of DMX this is? It sometimes saves a bunch of misunderstandings.
    Thanks,
    Peter
    Dan Peerenboom
    New Member
    New Member
    Posts:14


    --
    08/23/2006 5:55 PM
    Peter,

    It is DMX Pro 3.

    Dan
    Dan Peerenboom
    New Member
    New Member
    Posts:14


    --
    08/23/2006 6:00 PM
    Also, we are running DNN 3.2.1
    Peter Donker
    Veteran Member
    Veteran Member
    Posts:4536


    --
    08/23/2006 11:08 PM
    Dan,
    When you check on the Module Definitions page and click on the edit icon before 'Document Exchange Professional' you will see a version nr on screen. Much like the DNN version numbers. I need to know the exact DMX version nr as there have been tens of DMX 3 releases.
    Peter
    Peter Donker
    Veteran Member
    Veteran Member
    Posts:4536


    --
    08/23/2006 11:15 PM
    Just some more info about how DMX works regarding the above: when a new item is added there is a new entry in DMX_Entries with an identical EntryId and LastVersionId. This should also be the integer passed on to DMX_Log. If it does something else I'm very surprised. Then, when an existing item is updated (i.e. dring edit you upload a new document) a new version is created. What happens in the db is that the existing record in DMX_Entries is copied to the end of the table (i.e. it recieves a new EntryId) and the LastVersionId remains what it was before. Then the new data is written over the old record. This way I can assure that a link into DMX remains a link to the 'latest version'. What happens to the log during a new version? All DMX_Log corresponding to the existing record are rewired to stick to the newly created 'end of stack' record and we start from scratch with the overwritten record. This is how it should be.
    Dan Peerenboom
    New Member
    New Member
    Posts:14


    --
    08/23/2006 11:19 PM
    03.02.01
    Greg Hill
    New Member
    New Member
    Posts:2


    --
    08/24/2006 10:12 PM
    Hi Peter,

    I have some more information about the problem.

    I am trying to upload a new file to the intranet and receive the following error:


    A critical error has occurred.
    INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DMX_Log_DMX_Entries'. The conflict occurred in database 'CDSN', table 'DMX_Entries', column 'EntryId'

    When I look at the DMX_Entries table, I find the corresponding row.

    Software Admin 359 8/24/2006 2:35:47 PM 0 script 30; file_20060824_143547_DcQxun_0.txt 233198 File.Text 43009 1 0 233198 -1 script.txt 10 17 1 30;-1; 8/24/2006 2:35:47 PM 0

    The EntryId is 233198 but the LastVersionId is 0.

    This is the reason the file cannot be viewed. As one would expect, if I manually set the LastVersionId to 233198, I can view the file in DNN.

    Is the EntityId too large?

    How the the LastVersionId value determined? Is it a simple assignement in the code, or is it assigned in the database?
    Peter Donker
    Veteran Member
    Veteran Member
    Posts:4536


    --
    08/25/2006 11:41 AM
    Hi Greg/Dan,
    Firstoff, I have not seen this behavior before. There are many customers running large volumes of data in DMX and this has not occurred before. The mentioned EntryId is well within range for data type 'int' and should not give rise to any problems. It is puzzling that this happens. Having said that, you are on a quite old version of DMX. The latest version is 03.04.06. Your DNN is also not recent, so you might consider some upgrading.
    To help you I would need to be able to recreate the issue here, and I can't. There have been issues in the past with a constraint from the DMX_Entries to the Users table, but that has been resolved in later versions. This error where entryId and LastVersionId are not aigned has never occurred before.
    Does this also happen when creating a new version?
    Peter
    Greg Hill
    New Member
    New Member
    Posts:2


    --
    08/25/2006 2:35 PM
    We found the issue. A trigger was created on the DMX_Entries table. When the trigger was removed, the problem disappeared. The addition of a trigger is a likely suspect. If we had known about it we would have checked it sooner. Sorry about that!

    Thanks for your help. The discussion about the LastVersionId was helpful.

    - Greg
    You are not authorized to post a reply.

    Forums Document Exchange Lite Extensions and addons
    © 2024 Bring2mind Terms Of UsePrivacy Statement