I received an issue report from a customer this morning where some very simple content that she has been making changes to on a frequent basis suddenly became ‘corrupted’. I love cryptic issue reports from customers =/.
Taking a quick look at the content item in question I was greeted by one of my favorite complaints about the system, the very un-friendly error messages which are displayed to the user and not captured (to the best of my searching) anywhere helpful both when browsing to the published content and when trying to edit it through the CMS workarea.
Add failed. Duplicate key value supplied. At Microsoft.VisualBasic.Collection.Add(Object Item, String Key, Object Before, Object After) at Ektron.Cms.DataIO.EkContentRW.GetApprovalState(Int32 cId) at Ektron.Cms.EkException.ThrowException.Cms.EkException.ThrowException(Exception ex, EventLogEntryType EventType) at Ekron.Cms.EkException.ThrowException(Exception ex) at Ektron.Cms.DataIO.EkContentRW.GetApprovalState(Int32 cId) at Ektron.Cms.Content.EkContent.CanIv2_0(Int32 cId, String RequestType) at Ektron.Cms.EkException.ThrowException(exception ex, EventLogEntryType EventType) at Ektron.Cms.EkException.ThrowException(Exception ex) at Ektron.Cms.Content.EkContent.CanIv2_0(Int32 cId, String RequestType) at Ektron.Cms.ContentAPI.LoadPermissions(Int32 Id, String RequestType, PermissionResultType Type)
Some experimenting gave me very interesting behavior. Since I couldn’t modify or delete the record through the CMS interface I decided to jump into the production database (which is something I abhor doing and avoid at almost any cost) and quickly was greeted by 120 some tables.
A side note: at the very least Ektron gives you the ability to poke around in the database and doesn’t try to hijack your data.
I found the [content] table and the pesky ‘corrupted record’ and changed its content_status field to A (for approved). I went back into the CMS workarea and amazingly enough when I refreshed the listing page the status change had been picked up.
I still couldn’t edit the content item so next I tried to delete the record through the CMS application. Big surprise when I got another error only this time it was a ‘friendly IE error’.
Gee I was sure that I had my settings in IE set to NOT show that kind of error and looking at my settings I was puzzled to see that they were as expected.
Perplexed I tried to delete the content again only to find that it had been checked out to me and there was no way to check it back in to the system through the CMS. I went back to the database to change the status and immediately found that the content_id of 45 was no longer in the content table, dashing my hopes of just changing the status again. But why does that record (content_id 45) still show up in the CMS when its not in the DB?
I looked around in the CMS for someway to get myself out of the mess I had created on a production system and found another piece of ‘corrupted content’ (content_id = 46, very interesting)
I tried the same steps as above, got to the point where I used the CMS to delete the 2nd corrupted content item, received the same ‘friendly error message’ and found something totally different. Content_id 46 was still in the content table. Hmmm puzzling.
I tried to delete content_id 46 again and still it was in the database.
It was time to cut my losses at this point so I tried to delete content_id 46 from the database and was greeted with a foreign key constraint. More interestingly when I reformed my query specifying content_id 45 (the one that was gone a second ago) magically it was back!
The data must be cached somewhere right? So I close out of Management Studio and try it again… the record was still there. Amusingly enough when I did a query for all records where content_id is under 100 it wasn’t returned…
Neat right?
So I started to wonder and when I pointed my browser to id=45 and amazingly the content which was showing a nasty error when the user went to it was there… hmm what happens if I right click and try to edit it… you guessed it, I amazingly could see the content and make edits to it through the GUI…
But when I tried to save it… yea, that not so helpful .NET error popped up again:
Add failed. Duplicate key value supplied. At Microsoft.VisualBasic.Collection.Add(Object Item, String Key, Object Before, Object After) at blah blah blah.
Oh well, back to cutting my losses, let’s delete those records and use the CMS to recreate the content for the user… I first went through the CMS and created 2 records to replace the records I was going to delete, updated the primary navigation in the template to point to the newly created content items (another issue I have with the system is that there doesn’t seem to be a simple way of setting a value that will enable the template system to change links to content in the primary navigation which is stored in a CF page with hard coded links).
Time to get rid of that foreign key constraint, looking in the approval_status_tbl table I find two duplicate records, 1 set for content_id 45 and 1 set for content_id 46. When I tried to delete them I got a very helpful error message from Management Studio.
I should note that this is most likely the cause of the initial “Add failed. Duplicate key value supplied.” error. Note to self: if I have this problem again before I go though all the above headaches I’m going to check the approval_status_tbl for duplicate entries and take care of them.
I had to go through and delete a couple more records in other tables which were creating foreign key constraints in the following tables:
- library
- save_tbl
- content_edit
Once that was done I was able to delete content_id 45 and content_id 46 from the content table
And amazingly the records were gone from the CMS!
It’s an awfully long-winded explanation (but hey, I got stuck for an extra 2 hours at work on a Friday afternoon so there ;) ) for basically the following nugget of information: When confronted with an error containing “Duplicate key value supplied” while editing content look in the approval_status_tbl table for duplicate records and remove them.
I’m not sure how exactly these problems could have arisen in the first place; approval_status_tbl doesn’t have a primary key defined but it should have thrown a foreign key constraint error and prevented any duplicates from being generated… for kicks I tried to manually enter it and oh guess what? I get a FK constraint error… additionally from a programming standpoint, I make it a point anytime that I’m inserting data into more than a single table to catch any errors from an insert, roll back the transaction and then inform the user in a polite manner that there has been a problem (oh and I make sure that I log the error details and inform the development team that is maintain the application that something has gone wrong).
As far as I can tell the issues that I was experiencing with content_id 45 and content_id 46 were identical and yet when performing the same actions and getting the same displayed errors, different things appeared to be taking place in the database. (When I deleted 45 from the CMS the associated record in the content table was deleted but when I deleted 46 through the CMS that record remained)
The other interesting/troubling behavior that I saw was from the database queries; if a record in a database table can be queried directly (select * from [content] where content_id = 45) it should always be included when asking for the entirety of the table (select * from [content] where 0 = 0).
I found it to be an interesting little puzzle and I’ll be very interested to see what Ektron’s technical support has to say about it.
No comments:
Post a Comment