Hi Brett,
I'm glad to hear that this tool was useful on an old database. Some
comments inline...
On 8/7/16, 5:33 PM, Bergquist, Brett wrote:
Mike, Rick, Bryan, others. There was definite corruption in one page for one
table:
421677 |HARDWARE |1
|3 |NULL
|NULL
|Console Port
|421672
NULL |NULL |0
|-1 |^@^@^@^@~$
|^@^@^@^@
^@^@
|^@^@^@^@^@^@
|NULL
WARNING (no SQLState): Error reading field data. Offset = 65, length = 2,
datatype = VARCHAR(64): Field 0 in record 0 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 123, length = 4,
datatype = VARCHAR(64): Field 0 in record 1 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 139, length = 4,
datatype = VARCHAR(64): Field 0 in record 2 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 155, length = 4,
datatype = VARCHAR(64): Field 0 in record 3 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 171, length = 4,
datatype = VARCHAR(64): Field 0 in record 4 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 187, length = 4,
datatype = VARCHAR(64): Field 0 in record 5 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 203, length = 4,
datatype = VARCHAR(64): Field 0 in record 6 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 219, length = 4,
datatype = VARCHAR(64): Field 0 in record 7 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 235, length = 4,
datatype = VARCHAR(64): Field 0 in record 8 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 251, length = 4,
datatype = VARCHAR(64): Field 0 in record 9 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 267, length = 4,
datatype = VARCHAR(64): Field 0 in record 10 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 283, length = 4,
datatype = VARCHAR(64): Field 0 in record 11 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 299, length = 4,
datatype = VARCHAR(64): Field 0 in record 12 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 315, length = 4,
datatype = VARCHAR(64): Field 0 in record 13 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 331, length = 4,
datatype = VARCHAR(64): Field 0 in record 14 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 347, length = 4,
datatype = VARCHAR(64): Field 0 in record 15 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 363, length = 4,
datatype = VARCHAR(64): Field 0 in record 16 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 379, length = 4,
datatype = VARCHAR(64): Field 0 in record 17 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 395, length = 4,
datatype = VARCHAR(64): Field 0 in record 18 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 411, length = 4,
datatype = VARCHAR(64): Field 0 in record 19 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 427, length = 4,
datatype = VARCHAR(64): Field 0 in record 20 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 443, length = 4,
datatype = VARCHAR(64): Field 0 in record 21 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 459, length = 4,
datatype = VARCHAR(64): Field 0 in record 22 on page 32325 in file cb340.dat:
java.io.EOFException: null
WARNING (no SQLState): Error reading field data. Offset = 475, length = 4,
datatype = VARCHAR(64): Field 0 in record 23 on page 32325 in file cb340.dat:
java.io.EOFException: null
…
This was extracted using the tool from:
https://issues.apache.org/jira/browse/DERBY-6136
Mike, I believe your idea about retrieving all from the index when selecting
individually is probably true.
I believe the corrupt occurred when bug
https://issues.apache.org/jira/browse/DERBY-6879
was hit and one or more transaction log files were removed. Although it is
pointed out that doing so can and probably will cause corruption, when you have
a 500+ GB database with 18K transaction log files to process at startup which
will take multiple days to complete and the most recent backup also backed up
this condition, it was unavoidable ;)
Using the tool from
https://issues.apache.org/jira/browse/DERBY-6136
I was able to dump the data, destroy the table, re-format and re-import the
data with SYSCS_UTIL.SYSCS_IMPORT_TABLE. From that I was able to determine
what appeared to been in progress and what the corruption was related to
(deletion of various rows) and was able to manually cleanup references to those
rows.
So the tool was a life saver in this case. I did run into an issue with it in
that it tries to access conglomerate “c470.dat” this did not exist and this
mapped to SYSUSERS in other databases. This is old database that has been
through many upgrades of Derby on it. I was able to copy that conglomerate
from another newly created database just to let the tool continue which it did.
Some effort was put into making the tool respect the existing security
mechanisms of the database. It should be easy to relax this particular
check. We could read the database version from the property conglomerate
and skip this hurdle if the version is before 10.9, the release which
introduced NATIVE authentication and the SYSUSERS table.
A possible enhancement of the tool would be to output the data in a format that
can be imported by SYSCS_UTIL.SYSCS_IMPORT_TABLE. I was able to use Perl to
reformat the data for import but not having to do so would be beneficial.
What is needed beyond chaining the tool together with the
SYSCS_UTIL.SYSCS_EXPORT_QUERY procedure (see
http://db.apache.org/derby/docs/10.12/ref/rrefexportselectionproc.html)?
Thanks,
-Rick
I truly appreciate all of the help and suggestions!
On Aug 5, 2016, at 10:34 AM, mike matrigali<[email protected]> wrote:
I agree with bryan's first suggestions, more info from derby.log would help.
If it is possible just posting
it currently might help (one from debug with line numbers even better). i would
suggest filing a JIRA
and posting info and moving discussion there.
Some other questions/suggestions.
o can you tell us more about the ddl, i have not used describe much. What i am
looking mostly for is if there are any unique keys.
o what exactly are the indexes that you have on this table? what i am looking
for is what columns are in the indexes.
o look at the query plans for the queries that you are running, both the ones
that are working and the
ones that are not. what i am looking for is if the optmizer has been able to run
some queries as "covering" which means it gets
all the values from the index and never goes to the base table.
o if indexes are being used you might try using the optimizer hints to force
index or force not index to better understand where
corruption is.
on the ordering question, it depends on the answers to the above. If indexes
are used the ordering is going to be different
for each. A base table scan should theoretically give same order for each
scan as long as there is no updates happening.
does anyone know if describe prints the columns in the order they are actually
stored in the db?
if you have unique keys and the indexes are good, there may be some ways to
better get at the data.
/mikem
On 7/27/2016 7:16 AM, Bergquist, Brett wrote:
I wrote a tool to call SYSCS_UTIL.SYSCS_CHECK_TABLE on each table in the
database and not just stop on error.
I have a database returns the error:
Checking CORE_V1.DEVICE_ENTITY failed with exception: Restore of a
serializable or SQLData object of class , attempted to read more data than was
originally stored
The table has 1261157 rows in it. I can query each of the columns in the
table individually like:
SELECT ID FROM CORE_V1.DEVICE_ENTITY;
SELECT DTYPE FROM CORE_V1.DEVICE_ENTITY;
...
SELECT DESCRIPTION FROM CORE_V1.DEVICE_ENTITY;
These all return 1261157 rows for each select result.
If I select multiple columns such as
SELECT ID, DTYPE FROM CORE_V1.DEVICE_ENTITY;
Then I get:
ERROR XSDA7: Restore of a serializable or SQLData object of class , attempted
to read more data than was originally stored
ERROR XJ001: Java exception: ': java.io.EOFException'.
ij>
The table schema looks like:
ij> describe core_v1.device_entity;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
DTYPE |VARCHAR |NULL|NULL|64 |NULL |128 |YES
OPLOCK |INTEGER |0 |10 |10 |0 |NULL |NO
RELATIVE_POSITION |SMALLINT |0 |10 |5 |NULL |NULL |YES
ALIAS_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
DESCRIPTION |VARCHAR |NULL|NULL|255 |NULL |510 |YES
DEVICE_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
PARENTENTITY_ID |INTEGER |0 |10 |10 |NULL |NULL |YES
ID |INTEGER |0 |10 |10 |NULL |NULL |NO
8 rows selected
I am trying to figure out how to recover and correct this table. Once thought
is to query each column separately and then join the output externally back
together and re-import the data. I wonder however if I query each column if
the order of the rows returned will be the same. This will be necessary
because I cannot select the ID with the other column to identify the rows.
Any thoughts will be most welcome.
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is intended only
for the review of the party to whom it is addressed. If you have received this
transmission in error, please notify the sender immediately and discard the
original message and any attachment(s).
.
--
email: Mike Matrigali - [email protected]
linkedin: https://www.linkedin.com/in/MikeMatrigali
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is intended only
for the review of the party to whom it is addressed. If you have received this
transmission in error, please notify the sender immediately and discard the
original message and any attachment(s).
