Re: Corrupt indexes on a system catalog
Jake,
Not to add to your dilemma, but there was a bug with oncheck -cI and
building indexes in 7.10.xx. It was fixed in 7.11.UD1. The bug number is
42264. If a table had multiple extents in the same chunk you would get
the errors you saw when running oncheck. Ofcourse exporting and importing
could sometimes correct the problem id the table became a single extent.
I think I was running 7.10.UC3 at the time.
Good luck
Steve Berg
Neuberger & Berman LLC
spb@masada.nb.com
On Tue, 6 Jan 1998, Jacob Salomon wrote:
} AIX 3.1.4
} OnLine 7.11.UC1
} Level: Not for the faint of heart.
}
} Hi Family.
}
} I have a biiig problem with a client system down. I am bcc'ing the tech
} support person but I think he needs some help.
}
} Several times a day, the alarm program kicks in with a complaint about a
} corrupt index and the af file tells me to run the appropriate oncheck
} command. When this succeeds, it looks OK. But it fails alot. One of the
} af messages got me suspicious and decided to run oncheck against
} systables, syscolumns and sysindexes. Here is a sample session:
}
} 43p-v3$ oncheck -cDI garpacv2:systables
}
} Validating indexes for garpacv2:informix.systables...
} Index tabname
} Index tabid
} ERROR:Key value mismatch between data row and btree item
} Rowid 0xa21 contains key value:
} Key: 4463:
} Btree item contains rowid 0xa21, key value:
} Key: 4399:
}
} Index tabid is bad. OK to repair it? y
} Error recreating index.
} ISAM error: non-exclusive access.
}
} TBLspace data check for garpacv2:informix.systables
}
}
} Now, this non-exlusive access is strange - I (informix) am the only one
} on the system. I even got this when I ran oncheck in quiescent mode.
}
} In another window I ran onstat -k before the oncheck and while the
} oncheck is awaiting my "y". The lock list before the oncheck is, of
} course, empty - my control. Here is the result when I ran it while the
} prompt was up:
}
} 43p:/home/garpac/jake[234]> onstat -k
}
} INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:03:53 -- 26616
} Kbytes
}
} Locks
} address wtlist owner lklist type tblsnum rowid key#/bsiz
} 30257f60 0 4000dae8 0 HDR+S 100002 209 0
} 3025887c 0 4000dae8 30257f60 HDR+S 300002 0 0
} 2 active, 80000 total, 32768 hash buckets
}
} As I recall, tblspace 100002 is table sysdatabases and rowid 209 refers
} to the sysdatabases entry for my database (garpacv2). tblspace 300002 is
} systables from my database - I checked.
}
} So, I am holding a lock on a table (systables), yet when I go to do
} something to the table I have locked I seem to be blocked my my own
} lock.
}
} 43p:/home/garpac/jake[235]> onstat -u
}
} INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:08:38 -- 26616
} Kbytes
}
} Userthreads
} address flags sessid user tty wait tout locks nreads nwrites
} 4000a010 ---P--D 0 informix - 0 0 0 129 283
} 4000a444 ---P--F 0 informix - 0 0 0 0 0
} 4000a878 ---P--F 0 informix - 0 0 0 0 0
} 4000acac ---P--B 8 informix - 0 0 0 0 0
} 4000b0e0 ---P--D 0 informix - 0 0 0 0 0
} 4000dae8 ---P--- 145 informix 4 0 0 2 0 0
} 4000ebb8 Y------ 145 informix 4 40173a48 0 2 0 0
} 7 active, 128 total, 19 maximum concurrent
}
} Those last 2 sessions are my oncheck. So what does my session look
} like? Sorry you asked...
}
} 43p:/home/garpac/jake[236]> onstat -g ses 145
}
} INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:09:48 -- 26616
} Kbytes
}
} session #RSAM total used
} id user tty pid hostname threads memory memory
} 145 informix 4 194282 43p 2 81920 41636
}
} tid name rstcb flags curstk status
} 321 oncheck 4000dae8 ---P--- 3064 sleeping(Forever)
} 322 oncheckm 4000ebb8 Y------ 5068 cond wait(sm_read)
}
} Memory pools count 1
} name class addr totalsize freesize #allocfrag #freefrag
} 145 V 40326010 81920 40284 132 11
}
} name free used name free used
} overhead 0 100 scb 48 40
} opentable 0 6856 filetable 28 1348
} misc 728 376 log 32768 8336
} temprec 0 2444 blob 0 392
} ralloc 0 4096 gentcb 0 376
} ostcb 0 2268 net 68 3788
} tbutility 0 32 sqscb 0 7392
} rdahead 0 344 scan_desc 544 0
} hashfiletab 0 552 osenv 48 772
} oncheck 6052 68 sqtcb 0 1848
} fragman 0 208
}
} Sess SQL Current Iso Lock SQL ISAM F.E.
} Id Stmt type Database Lvl Mode ERR ERR Vers
} 145 UNKNOWN garpacv2 CR Not Wait 0 0 7.11
}
} Obviously no SQL statament - I can't run SQL in quiescent mode. What
} other info can I include here? Oh yes, the transaction list, such as it
} can be under quiescent mode:
}
} 43p:/home/garpac/jake[238]> onstat -x
}
} INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:20:56 -- 26616
} Kbytes
}
} Transactions
} address flags userthread locks log begin isolation retrys coordinator
} 4002c010 A---- 4000a010 0 0 COMMIT 0
} 4002c134 A---- 4000a444 0 0 COMMIT 0
} 4002c258 A---- 4000a878 0 0 COMMIT 0
} 4002c37c A---- 4000acac 0 0 COMMIT 0
} 4002c4a0 A---- 4000b0e0 0 0 COMMIT 0
} 4002ca54 A---- 4000dae8 2 0 COMMIT 0
} 6 active, 128 total, 15 maximum concurrent
}
} Note the last transaction in the list - the userthread corresponds to
} the next-to-last thread listed in onstat -u.
}
} What else can I look at?
}
} Bottom line: HOW CAN I CORRECT a CORRUPTED INDEX ON S SYSTEM CATALOG?
}
} Thanks for any help you can offer. (If you even read this you are quite
} a person!)
} --
} -- Jake (Never yelled "CROWDED THEATER!" during a fire)
} +------------------------------------------------------------+
} | The expedient performance of a task with excessive concern |
} | regarding its duration-to-completion engenders a virtual |
} | certainty of diminished benefit therefrom. |
} | -- Benjamin Franklin (but he said it in 3 words) |
} +------------------------------------------------------------+
}