db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Need some help in trying to figure out a corrupt database
Date Mon, 08 Aug 2016 15:16:17 GMT
I don't know if we need to relax the security restrictions on the tool or not or just document
the issue and the work around.   It was disconcerting when trying to get the tool to run as
I thought I had another corrupt issue.   The work around was pretty easy to do once I know
what the issue was.

As for using the "SYSCS_UTIL.SYSCS_EXPORT_QUERY" procedure, I had not even thought of that.
  I was under a time crunch to figure out if it would be possible to recover from the corruption
and I was pretty much blindly following along with the comments and instructions in the https://issues.apache.org/jira/browse/DERBY-6136
tool.  I even used the prebuilt JAR that was attached to the issue as I really did not know
how to go about building this tool.

I still don't know how to go about building the tool so maybe some documentation on how to
do that would be useful.

Here is what my existing IJ script looked like, so I was using just a "select" to get the
data.  I did not even realize I could call that procedure on the "RAW_" schema.

bash-3.2# less extractdata.sql

connect 'jdbc:derby:memory:db;create=true';

create type serializable external name 'java.io.Serializable' language java;

call syscs_util.syscs_register_tool
(
'customTool',
true,
'RawDBReader',
'CONTROL',
'RAW_',
'/opt/canoga/canogaview/glassfish/databases/csemdb',
null,
'CSEM',
'CSEM'
);

select ID, DTYPE, OPLOCK, RELATIVE_POSITION, ALIAS_NAME, DESCRIPTION, DEVICE_NAME, PARENTENTITY_ID
FROM RAW_CORE_V1.DEVICE_ENTITY;


extractdata.sql (END)

-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@gmail.com]
Sent: Monday, August 08, 2016 10:21 AM
To: derby-dev@db.apache.org
Subject: Re: Need some help in trying to figure out a corrupt database

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<mikemapp1@gmail.com>  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 - mikemapp1@gmail.com
>> 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).
>


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).

Mime
View raw message