240 Could not delete a row
A user on Informix 11.7 with a non-logged, year-partitioned archive table (~1.2 billion rows) got error 240 "could not delete a row" on a DELETE, with assert failures and oncheck -cI reporting index corruption; dropping and recreating the index didn't help, and the error recurred. Suggestions included locking the table (didn't help), dropping indexes before deleting (impractical with 10 indexes), and RAW tables for faster reloads. The likely cause was corruption from a dbspace that had filled up and was marked back up by support. The user rebuilt the table/indexes from scratch, after which the deletes worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have a big problem with an Informix 11.7 nologing database.
I have an Etl job wich run every day for archiving the online datas to the
archive database, the job extract the online datas from a table in order to
load this datas to a huge partionned archive table, but before loading, I
check if these datas are already charged in the archive table, so if yes I run
a delete from the archive table before charging (delete from mvt_fin where
datemouv=date_to_archive), but the delete run a few seconds and then it throw
the error 240 could not delete a row and no accompagning Isam error, I have
check the log file, and I found that there is an assert fails and informix
told me to run oncheck -cI on an index of this table, I have run the oncheck
and I found errors on the index for a partition of 2015, I drop and recreate
the index and rerun the job again but one more time, I got the error, but now
in the log file, no error message and no assert fail, I run oncheck again, and
informix told me this time "please drop and recreate the index xxxx", I do it
again, and rerun the job and again same error and no error in the log, I want
to know what's hapenning, why the index is corrupted after every delete I
launch.
for info: the table is huge annuel partionned table,partionned by a field
"datemouv" and it has more than 1 200 000 000 rows, maybe the size causin
problem, now I have modify the job, in order to not delete the existing datas
before loading, I have activate the violation on this table in order to make
things work, but I'm wondering what is the solution to this problem.
Also I can add that the select is working fine using the same weher clause of
the delete.
thanks a lot for answering
Can you try locking the table while running the delete? = Maybe you
are running out of
locks. If you are the only person on= the system locking the table
will be faster anyway.
John F. Miller = III
STSM, Lead Architect
[1]miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server = (IDS)
[2]-----ids-bounces@iiug.org wrote: ----= -
>To: [3]ids@iiug.org
>From: "CHALLENGER212 ABDERRAFI"
>Sent by: <= a target=3D"=5Fblank"
href=3D"mailto:ids-bounces@iiug.org">ids-bounces@iiug= .org
>Date: 12/05/2015 12:36AM
>Subject: 240 Could not dele= te a row [36167]
>
>Hi, I have a big problem with an Informix = 11.7 nologing database.
>I have an Etl job wich run every day for ar= chiving the online datas
>to the
>archive database, the job ex= tract the online datas from a table in
>order to
>load this da= tas to a huge partionned archive table, but before
>loading, I
&g= t;check if these datas are already charged in the archive table,
so if
&= gt;yes I run
>a delete from the archive table before charging (delet= e from
mvt=5Ffin
>where
>datemouv=3Ddate=5Fto=5Farchive), but = the delete run a few seconds
and then
>it throw
>the error 240= could not delete a row and no accompagning Isam error,
>I have
&= gt;check the log file, and I found that there is an assert fails
and
>= ;informix
>told me to run oncheck -cI on an index of this table, I h= ave run
the
>oncheck
>and I found errors on the index for a pa= rtition of 2015, I drop and
>recreate
>the index and rerun the= job again but one more time, I got the
error,
>but now
>in th= e log file, no error message and no assert fail, I run oncheck
>again= , and
>informix told me this time "please drop and recreate the inde= x
xxxx",
>I do it
>again, and rerun the job and again same err= or and no error in the
>log, I want
>to know what's hapenning,= why the index is corrupted after every
>delete I
>launch.
>for info: the table is huge annuel partionned table,partionned by a
>field
>"datemouv" and it has more than 1 200 000 000 rows, mayb= e the size
>causin
>problem, now I have modify the job, in ord= er to not delete the
>existing datas
>before loading, I have a= ctivate the violation on this table in
order
>to make
>things = work, but I'm wondering what is the solution to this
problem.
>Also = I can add that the select is working fine using the same weher
>claus= e of
>the delete.
>
>thanks a lot for answering
>= ;
>
>**********************************************************=
***********
>**********
> Forum Note: Use "Reply" to post a r= esponse in the discussion
forum.
>
>
>
References
1. 3D"mailto:miller3@us.ibm.=
2. file://localhost/tmp/3D"=
3. 3D"mailto:ids@iiug.org"=
Hi, I'm am the only person who is working on this table, and yes I have try locking in exclusive mode and it's not working. also I'm the only person working on this database.
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Why not drop the index, do the deletes, and then recreate the index? Sent from Yahoo Mail for iPad On Saturday, December 5, 2015, 4:07 AM, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com> wrote: Hi, I'm am the only person who is working on this table, and yes I have try locking in exclusive mode and it's not working. also I'm the only person working on this database. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
No, it would not work, I have 10 indexes on this table and all of them use field (datemouv) wich is used in my where clause, I want to add that I had a problem on the dbspace of this table, he goes down because we run out of space when inseting datas last week, we have called the support who has connected and mark this dbspace as up, maybe this is the problem, I will try to remove the partition for year 2015, and recreate it again and reload datas of 2015. Maybe the problem is that this partition has been corrupted because of the last week problem.
Looks like a strong possibility. Usually after support "fix" something they recommend an export/import, or a recreation of the objects etc. Regards. On Sat, Dec 5, 2015 at 1:46 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > No, it would not work, I have 10 indexes on this table and all of them use > field (datemouv) wich is used in my where clause, I want to add that I had > a > problem on the dbspace of this table, he goes down because we run out of > space > when inseting datas last week, we have called the support who has connected > and mark this dbspace as up, maybe this is the problem, I will try to > remove > the partition for year 2015, and recreate it again and reload datas of > 2015. > > Maybe the problem is that this partition has been corrupted because of the > last week problem. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113fe51eaf7ccd052626fb2d
Ok, I decide to drop the table, the dbspace, all chunks and recreate from scratch, I have already an export of this table. the problem is time, to import this table I would use External tables and I will work without indexes, when imported, I would create indexes, for 10 indexes maybe it would take 4 days to import and recreate the indexes.
When importing are you creating/altering the table to Be a RAW tables? If no= t that could shave a significant amount of time off the table load.=20 Sent from my iPhone > On Dec 5, 2015, at 8:16 AM, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.co= m> wrote: >=20 > Ok, I decide to drop the table, the dbspace, all chunks and recreate from=20= > scratch, I have already an export of this table.=20 >=20 > the problem is time, to import this table I would use External tables and I= =20 > will work without indexes, when imported, I would create indexes, for 10=20= > indexes maybe it would take 4 days to import and recreate the indexes.=20 >=20 >=20 > **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20
Non logged database Mark... I doubt it would make a difference... unless without raw it disables express mode... would need to check. On Sat, Dec 5, 2015 at 2:53 PM, Mark Jamison <majp51@me.com> wrote: > When importing are you creating/altering the table to Be a RAW tables? If > no= > t that could shave a significant amount of time off the table load.=20 > > Sent from my iPhone > > > On Dec 5, 2015, at 8:16 AM, CHALLENGER212 ABDERRAFI < > abderrafi212@gmail.co= > m> wrote: > >=20 > > Ok, I decide to drop the table, the dbspace, all chunks and recreate > from=20= > > > scratch, I have already an export of this table.=20 > >=20 > > the problem is time, to import this table I would use External tables > and I= > =20 > > will work without indexes, when imported, I would create indexes, for > 10=20= > > > indexes maybe it would take 4 days to import and recreate the indexes.=20 > >=20 > >=20 > > > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bdc1bd048de420526280cb7
Yes, its non logged database, its not necessary to put table in raw type, it should not have indexes and it goes fast in loading. Wich me good luck its huge work for me, and its a production database, to be inaccessible for days, I should convince the users to be patient. Regards
hi, All the rebuild of indexes has worked thanks to god and thank you for your support, now I can launch delete on that table. regards