It's interesting you mentioned that the error is from an index, as from the last boot which took place at: 2010-08-01 07:12:20.107 GMT up until the error at 2010-08-14 07:46:26.047 GMT, the derby log was littered (approx 134,000 lines) with the following warning:

WARNING: While deleting a row from a table the index row for base table row (622,176) was not found in index with conglomerate id 145,345.  This problem has automatically been corrected as part of the delete operation.

The base table and conglomerate id vary over all of the warning messages. Is there any way to interpret which index it is from the conglomerate id? I am faily sure we've run compression on the tables since the error, but I'll double check.

Upgrading to 10.6 may not be feasible in the short term, however if after compression does not resolve the issue, would dropping and reapplying the index help?

Thanks for the info Mike.

On Fri, Aug 27, 2010 at 5:27 PM, Mike Matrigali <mikem_app@sbcglobal.net> wrote:
That is strange, if the table or index was actually bad I would
expect check table to find this kind of problem.

>From your description it has the feel of something affecting the I/O
system in memory and causing the later I/O to fail.  You included
a snip of the derby.log.  Is there anything of interest that happened
between the last boot and when you got this error?  Sometimes derby
gets errors it considers system level and it basically shuts down
the I/O system to prevent db corruption until the system is rebooted.

The error you got is from an index.  You can rebuild all the indexes
on this table by running the offline compress system procedure on table.

This is a very old version of the software, if possible you might
consider upgrading.  10.6 is the latest release at this point.



Brandon L. Duncan wrote:
Hi all - I was wondering if anyone has any insight on to the exception below. I preformed a SYSCS_CHECK_TABLE on all tables after restarting Derby and all returned a 1. I also preformed disk checking on the physical disk which houses the database which was clean as well. This query has run successfully after the restart as well, and I'm looking to see what may have caused this and how to possibly prevent a similar occurrence in the future. This is version 10.2.2.0.

Thanks

2010-08-14 07:46:26.047 GMT Thread[DRDAConnThread_13346,5,derby.daemons] (XID = 79141229328), (SESSIONID = 12194), (DATABASE = ../../database/xdb1), (DRDAID = NF000001.P02E-953354465271340036{12196}), Cleanup action starting
2010-08-14 07:46:26.047 GMT Thread[DRDAConnThread_13346,5,derby.daemons] (XID = 79141229328), (SESSIONID = 12194), (DATABASE = ../../database/xdb1), (DRDAID = NF000001.P02E-953354465271340036{12196}), Failed Statement is: SELECT * FROM "EBI"."BPScriptCategories" WHERE "ID" = ?
ERROR XSDG0: Page Page(1,Container(0, 1601)) could not be read from disk.
   at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
   at org.apache.derby.impl.store.raw.data.CachedPage.readPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.CachedPage.setIdentity(Unknown Source)
   at org.apache.derby.impl.services.cache.CachedItem.takeOnIdentity(Unknown Source)
   at org.apache.derby.impl.services.cache.Clock.addEntry(Unknown Source)
   at org.apache.derby.impl.services.cache.Clock.find(Unknown Source)
   at org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.FileContainer.getPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.BaseContainerHandle.getPage(Unknown Source)
   at org.apache.derby.impl.store.access.btree.ControlRow.Get(Unknown Source)
   at org.apache.derby.impl.store.access.btree.ControlRow.Get(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNextGroup(Unknown Source)
   at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown Source)
   at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown Source)
   at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.writeFDODTA(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.writeQRYDTA(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
============= begin nested exception, level (1) ===========
java.io.IOException: Illegal seek
   at java.io.RandomAccessFile.seek(Native Method)
   at org.apache.derby.impl.store.raw.data.RAFContainer.readPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.CachedPage.readPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.CachedPage.setIdentity(Unknown Source)
   at org.apache.derby.impl.services.cache.CachedItem.takeOnIdentity(Unknown Source)
   at org.apache.derby.impl.services.cache.Clock.addEntry(Unknown Source)
   at org.apache.derby.impl.services.cache.Clock.find(Unknown Source)
   at org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.FileContainer.getPage(Unknown Source)
   at org.apache.derby.impl.store.raw.data.BaseContainerHandle.getPage(Unknown Source)
   at org.apache.derby.impl.store.access.btree.ControlRow.Get(Unknown Source)
   at org.apache.derby.impl.store.access.btree.ControlRow.Get(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)
   at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNextGroup(Unknown Source)
   at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown Source)
   at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown Source)
   at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.writeFDODTA(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.writeQRYDTA(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
   at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
============= end nested exception, level (1) ===========