Re: WHY index can corrupted???`

Art, I think there is a mistake in your argument. You forgot the effects of the phys log and the rollback of open transactions: Before the fast recovery starts all pages from the phys log are put back into their original location. This sets everything back to the state of the last checkpoint (I'm not talking about fuzzy checkpoints, which complicate things). If your transaction was not open at checkpoint time, this undoes your index and row update completely. Since open transactions are rolled back at the end of fast recovery and data and index updates are always in a (maybe implicit) tx, your changes might get lost with buffered logging altogether. But there should be no chance for inconsistencies between data and indexes. If I am wrong and if you have a reproduction I could run I am willing to investigate some time. Michael Art S. Kagel wrote: > [This is an email copy of a Usenet post to "comp.databases.informix"] > > On Thu, 28 Aug 2003 09:51:41 -0400, Michael Mueller wrote: > > >>Hi Art and all, > > > There's nothing that can be fixed in IDS to get rid of the risk in BUFFERED LOG > databases. Here's a typical scenario: > > 1-Transaction updates a row implying an index page in the buffer cache must be > updated. > > 2-The preimage of the page is written to the physical log buffer. > > 3-Record of the update itself is written to the logical log buffer. > > 4-Data and index pages are updated in the buffer cache. > > 5-An LRU containing the index page or the data page but not both is flushed to > disk because that LRU has reached its LRU_MAX_DIRTY level. > > 6-System crash or improper shutdown. > > 7-System/Engine restart - BOOM index corruption! > > Cause: The logical and physical log buffers have never been flushed to disk > (database is BUFFERED LOG right?) but the data or the index page has been > flushed! So the index reflects a key value that is not reflected in the row or > indicates a row that's been deleted or that should have been inserted but was > not or omits a row that's been inserted or that should have been deleted but > wasn't. On restart fast recovery cannot correct the problem because the > physical and/or logical log pages needed to undo this part of the partial > transaction were never flushed! While this is apparently an odd set of > circumstances, I've seen enough corrupted indexes to know that it does indeed > happen in the real world. > > If this scenario happens in an UNBUFFERED LOG database instance the logical log > buffers are immediately flushed as soon as ANY transaction on the server > completes so the window of risk in this case is miniscule even compared to the > very small window in a BUFFERED LOG environment. > > The only way to protect yourself if you use BUFFERED LOG is to use a very high > value for LRU_MAX_DIRTY and a relatively short checkpoint interval so all buffer > flushes occur at checkpoint time. Since logical and physical log buffers are > flushed at the beginning of the checkpoint and so before any dirty data/index > pages are flushed then the risk is eliminated unless FG writes occur. > > Art S. Kagel > > >>I don't doubt that some of you have observed index corruption in logged >>databases after system crashes. If this was not caused by faulty hardware or >>os software that corrupts disk contents directly (index and data pages, log >>space, etc.) it should be considered an Informix fast recovery bug and should >>be fixed if possible. This should also apply to buffered logging. >> >>Michael >> >>Art S. Kagel wrote: >> >>>On Tue, 12 Aug 2003 22:01:38 -0400, miyaki wrote: >>> >>>There are several sources of index corruption generally in a few classes: >>> >>>Caused by bugs: >>>There was a known BTREE-CLEANER bug that would occassionally corrupt indexes >>>but I no longer remember what release(s) are affected. >>> >>>Caused by crashes: >>>If you use caching controllers and/or an intelligent disk farm there is a >>>real risk that a system crash can leave data in the cache. This may or may >>>not be flushed to disk eventually and you may or may not want it so flushed. >>>Especially prevalent after a controller failure. >>> >>>Caused by improper shutdown: >>>Often I've found corruption similar to what one normally finds after a system >>>crash following a supposedly graceful system shutdown/restart. This is >>>caused by the rc files sending a shutdown to the database and returning >>>immediately without waiting for the server to actually shutdown. The delayed >>>shutdown can be caused by a large number of dirty buffers not yet flushed or >>>applications in critical sections for which the server must wait before >>>starting the checkpoint preparatory to a shutdown. >>> >>>Caused by database logging mode: >>>UNLOGGED databases are at the highest risk for index corruption as the engine >>>has no way to recover a partially written index update after a crash or >>>improper shutdown. These are most likely to suffer corruption caused by the >>>other issues. >>> >>>BUFFERED LOG databases are still at some risk for corruption, especially if >>>there are no active UNBUFFERED LOG databases running in the same instance. >>>This is because a BUFFERED LOG database does not force the logical log buffer >>>to flush when committing a transaction. UNBUFFERED LOG databases always >>>force a logical log buffer flush immediately upon writing a COMMIT record to >>>the logical log buffer and so are rarely found to have a corrupted index. If >>>you have a mixture of BUFFERED and UNBUFFERED databases you may be OK if the >>>UNBUFFERED databases are rather active since the BUFFERED database records >>>will be caught up in the flushes forced by the UNBUFFERED database activity. >>> >>>Note that having databases that are UNBUFFERED LOG databases offers the >>>additional risk that the engine will not be able to restart after a crash >>>without tech support intervention. This can be caused when a commit requires >>>multiple writes to the logical logs and the commit is partially written to >>>one buffer which fills and is flushed, then the final COMMIT is written to >>>the next logical log buffer which is not immediately flushed before the >>>system crashes. On restart the engine will hang when it encounters the end of >>>the logical log in the middle of a partially written commit. >>> >>>Art S. Kagel >>> >>> >>> >>> >>>>Hi all, >>>> >>>>OS : Sun Solaris 2.7 >>>>IDS : 7.31.UD5 >>>> >>>>Recently my database engine was crashed due to the index corruption. The >>>>engine was back up after repairing the index by using the oncheck command. >>>>I'm wondering why the index can be corrupted? In fact, I just performed the >>>>database re-org by using the dbexport-import at 3 months ago. >>>> >>>>Anybody knows the reason that may cause the index corruption??? >>>>@@NL