Re: Index problems
Re: Index problems
- Subject: Re: Index problems
- From: Calven Eggert <email@hidden>
- Date: Fri, 10 Dec 2010 10:59:17 -0500
(Hopefully others will save time from the result of this exercise)
The problem was not with the application but with a table's sequence.
I was creating a record in a table (let's call it Table A) that had an attachment. Attachments are kept in another table (Table B) so that I don't load them every time I want to load the main record from table A. The error message I was given was pointing to the record in Table A but the sequence for Table B was messed up. After this:
drop sequence tableB
create sequence tableB start with <insert number larger than current index value here>
All started working properly again. Geesh, I hate computers. :-)
turns out I'm not going crazy! (or am I?)
Calven
On 2010-12-08, at 6:08 PM, Chuck Hill wrote:
> On Dec 8, 2010, at 11:10 AM, Calven Eggert wrote:
>> On 2010-12-08, at 1:44 PM, Chuck Hill wrote:
>>> On Dec 8, 2010, at 10:41 AM, Calven Eggert wrote:
>>>
>>>> I still don't follow what you are saying. actually, I want to insert two records at a time. I have a page that allows the user to insert records, but I don't actually do the insert until the user clicks on the save button.
>>>
>>> As in ec.insertObject()? That is violating EOF commandments for sure.
>> then how? (I've read the commandments and I don't see your comment above in there)
>>
>> Essentially this is what is done...
>> EOClassDescription cd = EOClassDescription.classDescriptionForEntityName(entityName);
>> EOEnterpriseObject eo = (EOEnterpriseObject)cd.createInstanceWithEditingContext(ec, null);
>
> Save some typing:
> MyClass myClass = (MyClass)EOUtilities.createAndInsertInstance(ec, entityName);
>
> Wonder templates make it even easier.
>
>> ec.insertObject(eo);
>> eo.takeValueForKey(noteDescription, "notesdescription");
>
> That is OK, provided that noteDescription is a local ivar and not on an EO. Touching an attribute of an EO that has not been inserted (which is what I thought you were describing) is a Bad Thing (tm) to do.
>
>
>> ...
>> ec.saveChanges();
>
> If that is exactly what you are doing, then ec is already trashed by the time you get here or is not locked. I would suspect the former. Tracking this problem down to the code causing it can be very hard as the cause maybe far away in code and appear totally innocent.
>
>
> Chuck
>
>
>>>> that's when I do the saving/inserting into the database. but then the sequence numbers are incorrect for the second one.
>>>>
>>>> what is the other insert you are talking about?
>>>>
>>>> Still confused on what you are suggesting I do...
>>>
>>> What I am suggesting is that you DON'T violate EOF commandments. You are doing something that is corrupting the internal EOF state. Don't do that and it will work.
>>>
>>>
>>> Chuck
>>>
>>>
>>>
>>>>
>>>>
>>>> On 2010-12-08, at 1:24 PM, Chuck Hill wrote:
>>>>
>>>>> On Dec 8, 2010, at 10:20 AM, Calven Eggert wrote:
>>>>>
>>>>>> the second record is valid. I am indeed wanting to insert two records. so why would the retrieving of the next sequence number return me a number not in sequence?
>>>>>
>>>>> It is not. I am pretty sure that it is doing what I said. There is another (later) insert that you don't see due to the DB error.
>>>>>
>>>>>
>>>>> Chuck
>>>>>
>>>>>
>>>>>
>>>>>> On 2010-12-08, at 12:24 PM, Chuck Hill wrote:
>>>>>>
>>>>>>>
>>>>>>> On Dec 8, 2010, at 8:28 AM, Calven Eggert wrote:
>>>>>>>
>>>>>>>> Recently a WO app has been getting the unique constraint error when creating a record. This application has been running for years and so I'm surprised at this 'just' showing up. here is the problem: you'll see in the following lines that an index is retrieved for jobs_notes_seq twice because I'm inserting two records. next you'll see the insert statements. look at the notesid field.
>>>>>>>>
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL" withBindings: >
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> 1 row(s) processed
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Commit Internal Transaction
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL" withBindings: >
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> 1 row(s) processed
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Commit Internal Transaction
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:17(submitteddate), 5:"gg"(notesdescription), 6:922(notesid), 7:"Overview.graffle"(attachmentname)>
>>>>>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:23(submitteddate), 5:"hh"(notesdescription), 6:56(notesid), 7:"Overview.graffle"(attachmentname)>
>>>>>>>> [2010-12-8 10:54:33 EST] <WorkerThread1> === Rollback Internal Transaction
>>>>>>>>
>>>>>>>> see how one is 922 and another is 56? 922 is correct but the 56 should be 923.
>>>>>>>
>>>>>>> I don't think that is the problem. I think the problem is that the second was supposed to be an UPDATE or DELETE statement. This happens when you violate EOF commandments (not locking properly for example). EOF gets confused between one operation and another.
>>>>>>>
>>>>>>> Go forth and sin no more!
>>>>>>>
>>>>>>> Chuck
>>>>>>>
>>>>>>>
>>>>>>>> very strange. then when the records are committed, sometimes I get the dreaded error message:
>>>>>>>>
>>>>>>>> com.webobjects.eoaccess.EOGeneralAdaptorException: EvaluateExpression failed: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:23(submitteddate), 5:"hh"(notesdescription), 6:56(notesid), 7:"Overview.graffle"(attachmentname)>
>>>>>>>> Next exception:SQL State:23000 -- error code: 1 -- msg: ORA-00001: unique constraint (COREDEV3.SYS_C0031657) violated
>>>>>>>>
>>>>>>>> Obviously this is a problem because number 56 was already a record in the table. Sometimes, there is no error message and the record is not even created. And sometimes, when the index is correct the record is created successfully. argh!
>>>>>>>>
>>>>>>>> Anyone have this problem before or know what I can do to solve this mystery? (I've dropped the sequences and recreated them a couple of times. but it doesn't solve the problem)
>>>>>>>>
>>>>>>>> Thanks for your time.
>>>>>>>>
>>>>>>>> Calven
>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Do not post admin requests to the list. They will be ignored.
>>>>>>>> Webobjects-dev mailing list (email@hidden)
>>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>>
>>>>>>>> This email sent to email@hidden
>>>>>>>
>>>>>>> --
>>>>>>> Chuck Hill Senior Consultant / VP Development
>>>>>>>
>>>>>>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>>>>>>> http://www.global-village.net/products/practical_webobjects
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> Chuck Hill Senior Consultant / VP Development
>>>>>
>>>>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>>>>> http://www.global-village.net/products/practical_webobjects
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>> --
>>> Chuck Hill Senior Consultant / VP Development
>>>
>>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>>> http://www.global-village.net/products/practical_webobjects
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>
> --
> Chuck Hill Senior Consultant / VP Development
>
> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
> http://www.global-village.net/products/practical_webobjects
>
>
>
>
>
>
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden