More info. It appears there is corruption on one page of the conglomerate. Using the tool, I see:
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 WARNING (no SQLState): Error reading field data. Offset = 491, length = 4, datatype = VARCHAR(64): Field 0 in record 24 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 507, length = 4, datatype = VARCHAR(64): Field 0 in record 25 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 523, length = 4, datatype = VARCHAR(64): Field 0 in record 26 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 539, length = 4, datatype = VARCHAR(64): Field 0 in record 27 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 555, length = 4, datatype = VARCHAR(64): Field 0 in record 28 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 571, length = 4, datatype = VARCHAR(64): Field 0 in record 29 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 587, length = 4, datatype = VARCHAR(64): Field 0 in record 30 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 603, length = 4, datatype = VARCHAR(64): Field 0 in record 31 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 619, length = 4, datatype = VARCHAR(64): Field 0 in record 32 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 635, length = 4, datatype = VARCHAR(64): Field 0 in record 33 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 651, length = 4, datatype = VARCHAR(64): Field 0 in record 34 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 667, length = 4, datatype = VARCHAR(64): Field 0 in record 35 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 683, length = 4, datatype = VARCHAR(64): Field 0 in record 36 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 699, length = 4, datatype = VARCHAR(64): Field 0 in record 37 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 715, length = 4, datatype = VARCHAR(64): Field 0 in record 38 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 731, length = 4, datatype = VARCHAR(64): Field 0 in record 39 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 747, length = 4, datatype = VARCHAR(64): Field 0 in record 40 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 763, length = 4, datatype = VARCHAR(64): Field 0 in record 41 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 779, length = 4, datatype = VARCHAR(64): Field 0 in record 42 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 795, length = 4, datatype = VARCHAR(64): Field 0 in record 43 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 811, length = 4, datatype = VARCHAR(64): Field 0 in record 44 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 827, length = 4, datatype = VARCHAR(64): Field 0 in record 45 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 843, length = 4, datatype = VARCHAR(64): Field 0 in record 46 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 859, length = 4, datatype = VARCHAR(64): Field 0 in record 47 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 875, length = 4, datatype = VARCHAR(64): Field 0 in record 48 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 891, length = 4, datatype = VARCHAR(64): Field 0 in record 49 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 907, length = 4, datatype = VARCHAR(64): Field 0 in record 50 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 923, length = 4, datatype = VARCHAR(64): Field 0 in record 51 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 939, length = 4, datatype = VARCHAR(64): Field 0 in record 52 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 955, length = 4, datatype = VARCHAR(64): Field 0 in record 53 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 971, length = 4, datatype = VARCHAR(64): Field 0 in record 54 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 987, length = 4, datatype = VARCHAR(64): Field 0 in record 55 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1003, length = 4, datatype = VARCHAR(64): Field 0 in record 56 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1019, length = 4, datatype = VARCHAR(64): Field 0 in record 57 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1036, length = 4, datatype = VARCHAR(64): Field 0 in record 58 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1053, length = 4, datatype = VARCHAR(64): Field 0 in record 59 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1070, length = 4, datatype = VARCHAR(64): Field 0 in record 60 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1087, length = 4, datatype = VARCHAR(64): Field 0 in record 61 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1104, length = 4, datatype = VARCHAR(64): Field 0 in record 62 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1121, length = 4, datatype = VARCHAR(64): Field 0 in record 63 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1138, length = 4, datatype = VARCHAR(64): Field 0 in record 64 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1155, length = 4, datatype = VARCHAR(64): Field 0 in record 65 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1172, length = 4, datatype = VARCHAR(64): Field 0 in record 66 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1189, length = 4, datatype = VARCHAR(64): Field 0 in record 67 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1206, length = 4, datatype = VARCHAR(64): Field 0 in record 68 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1223, length = 4, datatype = VARCHAR(64): Field 0 in record 69 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1240, length = 4, datatype = VARCHAR(64): Field 0 in record 70 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1257, length = 4, datatype = VARCHAR(64): Field 0 in record 71 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1274, length = 4, datatype = VARCHAR(64): Field 0 in record 72 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1291, length = 4, datatype = VARCHAR(64): Field 0 in record 73 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1308, length = 4, datatype = VARCHAR(64): Field 0 in record 74 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1325, length = 4, datatype = VARCHAR(64): Field 0 in record 75 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1342, length = 4, datatype = VARCHAR(64): Field 0 in record 76 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1359, length = 4, datatype = VARCHAR(64): Field 0 in record 77 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1376, length = 4, datatype = VARCHAR(64): Field 0 in record 78 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1393, length = 4, datatype = VARCHAR(64): Field 0 in record 79 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1410, length = 4, datatype = VARCHAR(64): Field 0 in record 80 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1427, length = 4, datatype = VARCHAR(64): Field 0 in record 81 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1444, length = 4, datatype = VARCHAR(64): Field 0 in record 82 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1461, length = 4, datatype = VARCHAR(64): Field 0 in record 83 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1478, length = 4, datatype = VARCHAR(64): Field 0 in record 84 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1495, length = 4, datatype = VARCHAR(64): Field 0 in record 85 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1512, length = 4, datatype = VARCHAR(64): Field 0 in record 86 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1529, length = 4, datatype = VARCHAR(64): Field 0 in record 87 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1546, length = 4, datatype = VARCHAR(64): Field 0 in record 88 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1563, length = 4, datatype = VARCHAR(64): Field 0 in record 89 on page 32325 in file cb340.dat: java.io.EOFException: null WARNING (no SQLState): Error reading field data. Offset = 1580, length = 4, datatype = VARCHAR(64): Field 0 in record 90 on page 32325 in file cb340.dat: java.io.EOFException: null >From looking at the data that comes before and after these errors, it appears >that this is a pretty old page. Not sure how it got corrupted like this and >at this point I am not sure how to fix it. I am going to look at the tool in https://issues.apache.org/jira/browse/DERBY-5201 To see if I can read the page from an older database and see if that sheds any light. -----Original Message----- From: Bergquist, Brett [mailto:[email protected]] Sent: Wednesday, July 27, 2016 2:18 PM To: [email protected] Subject: RE: Need some help in trying to figure out a corrupt database More info. I looked to see what filenames are for a newly created database and 'c470.dat' corresponds to SYSUSERS. The database that I am trying to run the 'RawDBReader' on is an old database that has been upgrade many times, probably starting back at Derby 10.2 or so. Looking at old backups of this database from as far back as 2014, I don't find 'c470.dat' on any old backup either. So on a whim, I copied 'c470.dat' from the newly created database to my old database that I am trying to recover. Now I can run the 'RawDBReader' tool and seem to be able to access the table that I am having issues with. So it appears the tool is looking for a 'SYSUSERS' table, that does not exist when the database is old and upgraded many times. -----Original Message----- From: Bergquist, Brett [mailto:[email protected]] Sent: Wednesday, July 27, 2016 1:57 PM To: [email protected] Subject: RE: Need some help in trying to figure out a corrupt database I will try to get that Brian. Unfortunately, the database is 470GB so moving it around and accessing it is not trivial. I also tried to use the tool from https://issues.apache.org/jira/browse/DERBY-6136 When I run this tool I get an error that looks like: ij> connect 'jdbc:derby:memory:db;create=true'; ij> create type serializable external name 'java.io.Serializable' ij> language java; 0 rows inserted/updated/deleted ij> call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL', 'RAW_', '/opt/canoga/canogaview/glassfish/databases/csemdb', null, 'CSEM', 'CSEM' ); ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.sql.SQLException: /opt/canoga/canogaview/glassfish/databases/csemdb/seg0/c470.dat (No such file or directory)' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.sql.SQLException: /opt/canoga/canogaview/glassfish/databases/csemdb/seg0/c470.dat (No such file or directory)' was thrown while evaluating an expression. ERROR (no SQLState): /opt/canoga/canogaview/glassfish/databases/csemdb/seg0/c470.dat (No such file or directory) ij> It is true that this is no file '/opt/canoga/canogaview/glassfish/databases/csemdb/seg0/c470.dat'. Using the instructions on the wiki page https://wiki.apache.org/db-derby/ListFileNamesOfTables to list the filenames for the derby tables, I can find no reference to ' c470.dat' however which is puzzling. Here is what I used to query for all including the indexes and the system tables: CREATE FUNCTION bigintToHexString(hexString bigint) RETURNS VARCHAR(16) PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'java.lang.Long.toHexString'; select CAST(TABLENAME as varchar(32)) as DB_table, CAST('c' || bigintToHexString (CONGLOMERATENUMBER) || '.dat'as varchar(12)) as file , isindex from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b where a.TABLEID = b.TABLEID order by file; There is no 'c470.dat', so I don't know where this is being referenced from. -----Original Message----- From: Bryan Pendleton [mailto:[email protected]] Sent: Wednesday, July 27, 2016 10:34 AM To: [email protected] Subject: Re: Need some help in trying to figure out a corrupt database > I can query each of the columns in the table individually like: > > > 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'. Perhaps the table is not corrupt; perhaps instead there is a bug in the query execution? Can you post the full and complete exception trace? Ideally, from a debug build with source file line numbers? bryan 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). 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).
