Derby
  1. Derby
  2. DERBY-6136

Create a custom/optional tool for dumping the data in a corrupted database.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: 10.13.0.0
    • Component/s: Tools
    • Labels:
      None
    • Urgency:
      Normal

      Description

      It would be useful to have a tool for dumping the data in a corrupted database. This could start out as a custom tool. After we debug the tool and get some experience with it, we can consider promoting it to be a (possibly undocumented) optional tool which we ship with the product. I think the tool should have the following behavior:

      1) The tool should not subvert the security of the corrupted database. If the corrupted database is password-protected, then you would need to present its DBO's credentials in order to use the tool. Naturally, an encryption key would have to be presented in order to decode an encrypted database.

      2) The tool should not stop reading a table when it hits a corrupt record. Instead, the tool should soldier on and collect a list of warnings on bad records.

      Such a tool would be useful in situations where some part of a heap table is corrupt but the following heap conglomerates are intact:

      i) SYSSCHEMAS
      ii) SYSTABLES
      iii) SYSCONGLOMERATES
      iv) SYSCOLUMNS
      v) property conglomerate

      Such a tool would be useful for some situations where data can't be dumped even after you delete the log files in order to short-circuit recovery.

      1. DataFileVTI.java
        57 kB
        Rick Hillegas
      2. dataFileVTI.sql
        4 kB
        Rick Hillegas
      3. RawDBReader.java
        22 kB
        Rick Hillegas
      4. DataFileVTI.java
        58 kB
        Rick Hillegas
      5. RawDBReader.java
        22 kB
        Rick Hillegas
      6. DataFileVTI.java
        63 kB
        Rick Hillegas
      7. DataFileVTI.java
        59 kB
        Rick Hillegas
      8. DataFileVTI.java
        59 kB
        Rick Hillegas
      9. DerbyRecovery-0.0.1-SNAPSHOT.jar
        29 kB
        José Arcángel Salazar Delgado
      10. log2[1].dat
        1.14 MB
        John Judd
      11. log3[1].dat
        1.00 MB
        John Judd
      12. log4[1].dat
        1.00 MB
        John Judd
      13. log5[1].dat
        1024 kB
        John Judd
      14. log[1].ctrl
        0.0 kB
        John Judd
      15. logmirror[1].ctrl
        0.0 kB
        John Judd
      16. RawDBReader.java
        25 kB
        Rick Hillegas
      17. derby-6136-01-aa-productize.diff
        104 kB
        Rick Hillegas
      18. derby-6136-01-ab-teardownCorruptDB.diff
        105 kB
        Rick Hillegas
      19. derby-6136-02-aa-cleanup.diff
        22 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          ASF subversion and git services added a comment -

          Commit 1716908 from Rick Hillegas in branch 'code/trunk'
          [ https://svn.apache.org/r1716908 ]

          DERBY-6136: Cleanup RawDBReader; commit derby-6136-02-aa-cleanup.diff.

          Show
          ASF subversion and git services added a comment - Commit 1716908 from Rick Hillegas in branch 'code/trunk' [ https://svn.apache.org/r1716908 ] DERBY-6136 : Cleanup RawDBReader; commit derby-6136-02-aa-cleanup.diff.
          Hide
          Rick Hillegas added a comment -

          Tests ran cleanly for me except for a problem which I occasionally see:

          There were 2 failures:
          1) testPingWithWrongHost(org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest)junit.framework.AssertionFailedError: Could not find expectedString:Unable to find host in output:<STDOUT>Fri Nov 27 09:35:18 PST 2015 : Could not connect to Derby Network Server on host nothere.invalid, port 1527: Operation timed out
          <END STDOUT>
          <STDERR><END STDERR>
          
          	at org.apache.derbyTesting.junit.BaseTestCase.assertExecJavaCmdAsExpected(BaseTestCase.java:609)
          	at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.assertFailedPing(NetworkServerControlClientCommandTest.java:147)
          	at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.testPingWithWrongHost(NetworkServerControlClientCommandTest.java:112)
          	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          	at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:120)
          	at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:443)
          	at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:460)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          	at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:58)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          2) testInvalidLDAPServerConnectionError(org.apache.derbyTesting.functionTests.tests.jdbcapi.InvalidLDAPServerAuthenticationTest)junit.framework.AssertionFailedError
          	at org.apache.derbyTesting.functionTests.tests.jdbcapi.InvalidLDAPServerAuthenticationTest.testInvalidLDAPServerConnectionError(InvalidLDAPServerAuthenticationTest.java:126)
          	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          	at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:120)
          	at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:443)
          	at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:460)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          	at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:58)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          	at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          	at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          	at junit.extensions.TestSetup.run(TestSetup.java:25)
          
          FAILURES!!!
          Tests run: 13558,  Failures: 2,  Errors: 0
          
          Show
          Rick Hillegas added a comment - Tests ran cleanly for me except for a problem which I occasionally see: There were 2 failures: 1) testPingWithWrongHost(org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest)junit.framework.AssertionFailedError: Could not find expectedString:Unable to find host in output:<STDOUT>Fri Nov 27 09:35:18 PST 2015 : Could not connect to Derby Network Server on host nothere.invalid, port 1527: Operation timed out <END STDOUT> <STDERR><END STDERR> at org.apache.derbyTesting.junit.BaseTestCase.assertExecJavaCmdAsExpected(BaseTestCase.java:609) at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.assertFailedPing(NetworkServerControlClientCommandTest.java:147) at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.testPingWithWrongHost(NetworkServerControlClientCommandTest.java:112) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:120) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:443) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:460) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:58) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) 2) testInvalidLDAPServerConnectionError(org.apache.derbyTesting.functionTests.tests.jdbcapi.InvalidLDAPServerAuthenticationTest)junit.framework.AssertionFailedError at org.apache.derbyTesting.functionTests.tests.jdbcapi.InvalidLDAPServerAuthenticationTest.testInvalidLDAPServerConnectionError(InvalidLDAPServerAuthenticationTest.java:126) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:120) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:443) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:460) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:58) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) FAILURES!!! Tests run: 13558, Failures: 2, Errors: 0
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6136-02-aa-cleanup.diff. This patch cleans up some variable names and comments in order to improve the readability of RawDBReader. This patch also adds some more tests. I am running full regression tests now.

          Touches the following files:

          ----------------

          M java/optional/org/apache/derby/optional/dump/RawDBReader.java

          Cleaned up source code.

          ----------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/RawDBReaderTest.java

          Additional tests.

          Show
          Rick Hillegas added a comment - Attaching derby-6136-02-aa-cleanup.diff. This patch cleans up some variable names and comments in order to improve the readability of RawDBReader. This patch also adds some more tests. I am running full regression tests now. Touches the following files: ---------------- M java/optional/org/apache/derby/optional/dump/RawDBReader.java Cleaned up source code. ---------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/RawDBReaderTest.java Additional tests.
          Hide
          ASF subversion and git services added a comment -

          Commit 1716761 from Rick Hillegas in branch 'code/trunk'
          [ https://svn.apache.org/r1716761 ]

          DERBY-6136: Introduce the rawDBReader optional tool for last-chance retrieval of data from unbootable databases; commit derby-6136-01-ab-teardownCorruptDB.diff.

          Show
          ASF subversion and git services added a comment - Commit 1716761 from Rick Hillegas in branch 'code/trunk' [ https://svn.apache.org/r1716761 ] DERBY-6136 : Introduce the rawDBReader optional tool for last-chance retrieval of data from unbootable databases; commit derby-6136-01-ab-teardownCorruptDB.diff.
          Hide
          Rick Hillegas added a comment -

          Thanks for that feedback and for kicking the tires, Bryan. Attaching derby-6136-01-ab-teardownCorruptDB.diff. With this rev of the patch, the full tests run cleanly.

          A later test (DboPowersTest) was failing. This was because the SQL authorization decorator does not remove its database. RawDBReaderTest creates a test involving a lot of decorators: authentication, SQL authorization, and encryption. DboPowersTest tried to re-use the database but couldn't because DboPowersTest didn't know the boot password created by RawDBReaderTest. The fix was to have RawDBReaderTest delete the database so that it couldn't be re-used. There may be a more elegant way to accomplish this, say, by composing the decorators in a different order and by adding a drop-database decorator. I couldn't figure out how to do that, however. My solution was to do the following:

          1) Make RawDBReaderTest.tearDown() delete the database.

          2) That wasn't enough, however. After the database was deleted, the authentication decorator's tearDown() logic re-created the database in order to reset properties. So I had to tell the authentication decorator not to do that.

          Touches the same files as the previous rev.

          Show
          Rick Hillegas added a comment - Thanks for that feedback and for kicking the tires, Bryan. Attaching derby-6136-01-ab-teardownCorruptDB.diff. With this rev of the patch, the full tests run cleanly. A later test (DboPowersTest) was failing. This was because the SQL authorization decorator does not remove its database. RawDBReaderTest creates a test involving a lot of decorators: authentication, SQL authorization, and encryption. DboPowersTest tried to re-use the database but couldn't because DboPowersTest didn't know the boot password created by RawDBReaderTest. The fix was to have RawDBReaderTest delete the database so that it couldn't be re-used. There may be a more elegant way to accomplish this, say, by composing the decorators in a different order and by adding a drop-database decorator. I couldn't figure out how to do that, however. My solution was to do the following: 1) Make RawDBReaderTest.tearDown() delete the database. 2) That wasn't enough, however. After the database was deleted, the authentication decorator's tearDown() logic re-created the database in order to reset properties. So I had to tell the authentication decorator not to do that. Touches the same files as the previous rev.
          Hide
          Bryan Pendleton added a comment -

          FWIW, the "productize" patch built cleanly on my system,
          and the new RawDBReader test passed when I ran it.

          This seems like a useful tool to have in the distribution;
          thanks for taking the time to keep pushing it forward.

          Show
          Bryan Pendleton added a comment - FWIW, the "productize" patch built cleanly on my system, and the new RawDBReader test passed when I ran it. This seems like a useful tool to have in the distribution; thanks for taking the time to keep pushing it forward.
          Hide
          Rick Hillegas added a comment -

          I am seeing a test failure which is probably related to running the new test in a suite rather than standalone. I will investigate.

          Show
          Rick Hillegas added a comment - I am seeing a test failure which is probably related to running the new test in a suite rather than standalone. I will investigate.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6136-01-aa-productize.diff. This patch converts RawDBReader from a custom tool into a full-fledged optional tool which is bundled in derbyoptionaltools.jar. I am running tests now.

          The rawDBReader optional tool has the following signature:

          call syscs_util.syscs_register_tool
          (
          'rawDBReader',
          true,
          '$pathToRecoveryScript',
          '$controlSchema',
          '$shadowSchemaPrefix',
          '$pathToCorruptDatabase',
          '$encryptionAttributes',
          '$ownerOfCorruptDatabase',
          '$ownerPassword'
          );

          where

          1) The recovery script will be written to $pathToRecoveryScript

          2) The $controlSchema is where the tool creates views on the core catalogs of the corrupt database.

          3) The $shadowSchemaPrefix is a string which is prefixed to the names of all the user schemas observed in the corrupt database. Views on the corrupt tables are created in the good database to which the data will exported. These views are created in shadow schemas in the good database.

          4) $pathToCorruptDatabase is the location of the corrupt database.

          5) $encryptionAttributes is the list of encryption attributes needed to connect to the corrupt database.

          6) $ownerOfCorruptDatabase is the DBO of the corrupt database.

          7) $ownerPassword is the DBO's password.

          For instance,

          call syscs_util.syscs_register_tool
          (
            'rawDBReader',
            true,
            '/Users/rhillegas/sql/zrecovery.sql',
            'CONTROL11',
            'RAW11_',
            'tmpdbs/db0',
            'bootPassword=fooBarWibble',
            'TEST_DBO',
            'TEST_DBORRT'
          );
          

          Follow-on work will include user documentation and some cleanup of these classes.

          Touches the following files:

          --------------

          M java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents
          M java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyTesting.jar.lastcontents
          M java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyTesting.jar.lastcontents
          M java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents

          Build machinery.

          --------------

          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
          M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
          A java/optional/org/apache/derby/optional/dump
          A java/optional/org/apache/derby/optional/dump/RawDBReader.java
          A java/optional/org/apache/derby/optional/dump/DataFileVTI.java

          The new rawDBReader optional tool.

          --------------

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/RawDBReaderTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

          Tests.

          Show
          Rick Hillegas added a comment - Attaching derby-6136-01-aa-productize.diff. This patch converts RawDBReader from a custom tool into a full-fledged optional tool which is bundled in derbyoptionaltools.jar. I am running tests now. The rawDBReader optional tool has the following signature: call syscs_util.syscs_register_tool ( 'rawDBReader', true, '$pathToRecoveryScript', '$controlSchema', '$shadowSchemaPrefix', '$pathToCorruptDatabase', '$encryptionAttributes', '$ownerOfCorruptDatabase', '$ownerPassword' ); where 1) The recovery script will be written to $pathToRecoveryScript 2) The $controlSchema is where the tool creates views on the core catalogs of the corrupt database. 3) The $shadowSchemaPrefix is a string which is prefixed to the names of all the user schemas observed in the corrupt database. Views on the corrupt tables are created in the good database to which the data will exported. These views are created in shadow schemas in the good database. 4) $pathToCorruptDatabase is the location of the corrupt database. 5) $encryptionAttributes is the list of encryption attributes needed to connect to the corrupt database. 6) $ownerOfCorruptDatabase is the DBO of the corrupt database. 7) $ownerPassword is the DBO's password. For instance, call syscs_util.syscs_register_tool ( 'rawDBReader', true, '/Users/rhillegas/sql/zrecovery.sql', 'CONTROL11', 'RAW11_', 'tmpdbs/db0', 'bootPassword=fooBarWibble', 'TEST_DBO', 'TEST_DBORRT' ); Follow-on work will include user documentation and some cleanup of these classes. Touches the following files: -------------- M java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents M java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyTesting.jar.lastcontents M java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyTesting.jar.lastcontents M java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents Build machinery. -------------- M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java A java/optional/org/apache/derby/optional/dump A java/optional/org/apache/derby/optional/dump/RawDBReader.java A java/optional/org/apache/derby/optional/dump/DataFileVTI.java The new rawDBReader optional tool. -------------- A java/testing/org/apache/derbyTesting/functionTests/tests/lang/RawDBReaderTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Tests.
          Hide
          Rick Hillegas added a comment -

          Attaching a new version of RawDBReader.java. This version writes out a recovery script as it creates the table functions and views needed to read the corrupt database. The recovery script recreates all of the user schemas and tables in the new database and siphons the data out of the corrupt tables into the new tables. The recovery script includes comments which help an administrator identify which seg0 files correspond to which tables.

          So, for instance, if the corrupt database has this schema:

          schema1.t1
          schema1.t2
          schema2.t1
          schema2.t2
          

          ...then running the following statements...

          connect 'jdbc:derby:tmpdbs/db1;create=true';
          
          call syscs_util.syscs_register_tool
          (
            'customTool',
            true,
            'RawDBReader',
            'CONTROL11',
            'RAW11_',
            'tmpdbs/db0',
            null,
            'APP',
            null
          );
          

          ...would create a file named recovery.sql in the current directory with the following contents...

          connect 'jdbc:derby:tmpdbs/db1';
          
          create schema "SCHEMA1";
          
          -- siphon data out of c490.dat
          create table "SCHEMA1"."T1" as select * from "RAW11_SCHEMA1"."T1" with no data;
          insert into "SCHEMA1"."T1" select * from "RAW11_SCHEMA1"."T1";
          
          create schema "SCHEMA2";
          
          -- siphon data out of c4a0.dat
          create table "SCHEMA1"."T2" as select * from "RAW11_SCHEMA1"."T2" with no data;
          insert into "SCHEMA1"."T2" select * from "RAW11_SCHEMA1"."T2";
          
          -- siphon data out of c4b0.dat
          create table "SCHEMA2"."T1" as select * from "RAW11_SCHEMA2"."T1" with no data;
          insert into "SCHEMA2"."T1" select * from "RAW11_SCHEMA2"."T1";
          
          -- siphon data out of c4c0.dat
          create table "SCHEMA2"."T2" as select * from "RAW11_SCHEMA2"."T2" with no data;
          insert into "SCHEMA2"."T2" select * from "RAW11_SCHEMA2"."T2";
          
          Show
          Rick Hillegas added a comment - Attaching a new version of RawDBReader.java. This version writes out a recovery script as it creates the table functions and views needed to read the corrupt database. The recovery script recreates all of the user schemas and tables in the new database and siphons the data out of the corrupt tables into the new tables. The recovery script includes comments which help an administrator identify which seg0 files correspond to which tables. So, for instance, if the corrupt database has this schema: schema1.t1 schema1.t2 schema2.t1 schema2.t2 ...then running the following statements... connect 'jdbc:derby:tmpdbs/db1;create=true'; call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL11', 'RAW11_', 'tmpdbs/db0', null, 'APP', null ); ...would create a file named recovery.sql in the current directory with the following contents... connect 'jdbc:derby:tmpdbs/db1'; create schema "SCHEMA1"; -- siphon data out of c490.dat create table "SCHEMA1"."T1" as select * from "RAW11_SCHEMA1"."T1" with no data; insert into "SCHEMA1"."T1" select * from "RAW11_SCHEMA1"."T1"; create schema "SCHEMA2"; -- siphon data out of c4a0.dat create table "SCHEMA1"."T2" as select * from "RAW11_SCHEMA1"."T2" with no data; insert into "SCHEMA1"."T2" select * from "RAW11_SCHEMA1"."T2"; -- siphon data out of c4b0.dat create table "SCHEMA2"."T1" as select * from "RAW11_SCHEMA2"."T1" with no data; insert into "SCHEMA2"."T1" select * from "RAW11_SCHEMA2"."T1"; -- siphon data out of c4c0.dat create table "SCHEMA2"."T2" as select * from "RAW11_SCHEMA2"."T2" with no data; insert into "SCHEMA2"."T2" select * from "RAW11_SCHEMA2"."T2";
          Hide
          John Judd added a comment - - edited

          Hello Rick, These are the .dat files saved in the \db\ehourDb\log directory. Thanks. John

          Show
          John Judd added a comment - - edited Hello Rick, These are the .dat files saved in the \db\ehourDb\log directory. Thanks. John
          Hide
          Rick Hillegas added a comment -

          The visibility of various Derby classes changed in 10.12 so RawDBReader and DataFileVTI no longer compile. For the moment, you will need to use 10.11 in order to compile and use these tools.

          Show
          Rick Hillegas added a comment - The visibility of various Derby classes changed in 10.12 so RawDBReader and DataFileVTI no longer compile. For the moment, you will need to use 10.11 in order to compile and use these tools.
          Hide
          Rick Hillegas added a comment -

          Thanks, José.

          Show
          Rick Hillegas added a comment - Thanks, José.
          Hide
          José Arcángel Salazar Delgado added a comment -

          I create DerbyRecovery-0.0.1-SNAPSHOT.jar file to simplify the process.

          1.-Download lastest derby bin.
          2.- Add DerbyRecovery-0.0.1-SNAPSHOT.jar to the ij classpath
          3.- Run dataFileVTI.sql (remember to change the route to the database in the connect string)
          3.- enter ij and connect to the database using upgrade=true
          4.- use call SYSCS_UTIL.SYSCS_REGISTER_TOOL('customTool',true,'RawDBReader', 'CONTROL','RAW_','c:\db','','APP',null); -> change the db route, password, bootpassword and schema
          5.- check with select * from raw_app.table1;

          Show
          José Arcángel Salazar Delgado added a comment - I create DerbyRecovery-0.0.1-SNAPSHOT.jar file to simplify the process. 1.-Download lastest derby bin. 2.- Add DerbyRecovery-0.0.1-SNAPSHOT.jar to the ij classpath 3.- Run dataFileVTI.sql (remember to change the route to the database in the connect string) 3.- enter ij and connect to the database using upgrade=true 4.- use call SYSCS_UTIL.SYSCS_REGISTER_TOOL('customTool',true,'RawDBReader', 'CONTROL','RAW_','c:\db','','APP',null); -> change the db route, password, bootpassword and schema 5.- check with select * from raw_app.table1;
          Hide
          Rick Hillegas added a comment -

          Attaching a new version of DataFileVTI which preserves triggering Throwables in the SQLWarnings passed back to the user.

          Show
          Rick Hillegas added a comment - Attaching a new version of DataFileVTI which preserves triggering Throwables in the SQLWarnings passed back to the user.
          Hide
          Rick Hillegas added a comment -

          Attaching a new version of DataFileVTI. This version makes the following changes:

          1) Skips records which are marked as deleted.

          2) Turns errors into SQLWarnings so that problems will still be reported as reading continues. The warnings percolate up to the user only if you are using a codeline improved by derby-6151-01-aa-passBackWarnings.diff.

          3) Removed dead code and reorganized to improve readability.

          I have run DataFileVTI successfully against corrupt heaps. That is, against heaps which spew hex dumps when you try to select from them directly. Here is an ij example of using DataFileVTI to read a corrupt heap:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          true,
          'RawDBReader',
          'CONTROL11',
          'RAW11_',
          '/Users/rh161140/derby/mainline/corruptEncryptedDB',
          'bootPassword=mysecretpassword',
          'APP',
          null
          );
          0 rows inserted/updated/deleted
          ij(CONNECTION2)> select * from raw11_app.t1 where a > 37 and a < 50;
          A |B |C
          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          38 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&
          39 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&
          48 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&
          WARNING (no SQLState): Error reading field data. Offset = 366, length = 290, datatype = VARCHAR(1000): Field 2 in record 0 on page 5 in file c490.dat: java.io.UTFDataFormatException: null
          WARNING (no SQLState): Error reading field data. Offset = 671, length = 1823514738, datatype = VARCHAR(1000): Field 1 in record 1 on page 5 in file c490.dat: java.io.IOException: Negative seek offset
          WARNING (no SQLState): Error reading field data. Offset = 685, length = 36, datatype = VARCHAR(1000): Field 2 in record 1 on page 5 in file c490.dat: java.io.EOFException: null
          WARNING (no SQLState): java.lang.ArrayIndexOutOfBoundsException: null
          49 |abcdefghijklmnopqrstuvwxyz0123456789 |abcdefghijklmnopqrstuvwxyz0123456789

          4 rows selected

          I think that these tools are ready for experimental use by others. Please tell me about the problems you find, so that I can improve these tools. Thanks.

          Show
          Rick Hillegas added a comment - Attaching a new version of DataFileVTI. This version makes the following changes: 1) Skips records which are marked as deleted. 2) Turns errors into SQLWarnings so that problems will still be reported as reading continues. The warnings percolate up to the user only if you are using a codeline improved by derby-6151-01-aa-passBackWarnings.diff. 3) Removed dead code and reorganized to improve readability. I have run DataFileVTI successfully against corrupt heaps. That is, against heaps which spew hex dumps when you try to select from them directly. Here is an ij example of using DataFileVTI to read a corrupt heap: call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL11', 'RAW11_', '/Users/rh161140/derby/mainline/corruptEncryptedDB', 'bootPassword=mysecretpassword', 'APP', null ); 0 rows inserted/updated/deleted ij(CONNECTION2)> select * from raw11_app.t1 where a > 37 and a < 50; A |B |C ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 38 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs& 39 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs& 48 |abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs&|abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrstuvwxyz0123456789abcdefghijklmnopqrs& WARNING (no SQLState): Error reading field data. Offset = 366, length = 290, datatype = VARCHAR(1000): Field 2 in record 0 on page 5 in file c490.dat: java.io.UTFDataFormatException: null WARNING (no SQLState): Error reading field data. Offset = 671, length = 1823514738, datatype = VARCHAR(1000): Field 1 in record 1 on page 5 in file c490.dat: java.io.IOException: Negative seek offset WARNING (no SQLState): Error reading field data. Offset = 685, length = 36, datatype = VARCHAR(1000): Field 2 in record 1 on page 5 in file c490.dat: java.io.EOFException: null WARNING (no SQLState): java.lang.ArrayIndexOutOfBoundsException: null 49 |abcdefghijklmnopqrstuvwxyz0123456789 |abcdefghijklmnopqrstuvwxyz0123456789 4 rows selected I think that these tools are ready for experimental use by others. Please tell me about the problems you find, so that I can improve these tools. Thanks.
          Hide
          Rick Hillegas added a comment -

          Attaching a new version of DataFileVTI. This version reads heaps with long rows which spill across multiple pages.

          Show
          Rick Hillegas added a comment - Attaching a new version of DataFileVTI. This version reads heaps with long rows which spill across multiple pages.
          Hide
          Rick Hillegas added a comment -

          Attaching new versions of DataFileVTI and RawDBReader. These versions change the bootPassword argument into a more general encryptionAttributes argument. I have tested these new versions with a database encrypted with an explicit encryption key and with a database encrypted with a bootpassword. Here are examples of both usages:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          true,
          'RawDBReader',
          'CONTROL6',
          'RAW6_',
          '/Users/rh161140/derby/mainline/encryptedDB',
          'encryptionKey=abcd1234efab5678',
          'APP',
          null
          );

          call syscs_util.syscs_register_tool
          (
          'customTool',
          true,
          'RawDBReader',
          'CONTROL7',
          'RAW7_',
          '/Users/rh161140/derby/mainline/bootpasswordDB',
          'bootPassword=mysecretpassword',
          'APP',
          null
          );

          Show
          Rick Hillegas added a comment - Attaching new versions of DataFileVTI and RawDBReader. These versions change the bootPassword argument into a more general encryptionAttributes argument. I have tested these new versions with a database encrypted with an explicit encryption key and with a database encrypted with a bootpassword. Here are examples of both usages: call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL6', 'RAW6_', '/Users/rh161140/derby/mainline/encryptedDB', 'encryptionKey=abcd1234efab5678', 'APP', null ); call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL7', 'RAW7_', '/Users/rh161140/derby/mainline/bootpasswordDB', 'bootPassword=mysecretpassword', 'APP', null );
          Hide
          Rick Hillegas added a comment -

          Attaching the first rev of RawDBReader, a custom tool for dumping the data in a corrupt database. This tool makes use of DataFileVTI. The tool creates schemas, table functions, and wrapper views for reading all user tables in a corrupt database. To use the tool you must specify the bootpassword (if applicable) and the credentials of the owner of the corrupt database. The tool takes the following arguments:

          o The name of a control schema to create in your local database. The control schema will hold views against the core catalogs in t e corrupt database.

          o A prefix to prepend to the names of user schemas. For each schema in the corrupt database, the tool creates a corresponding lo al schema with this prefix prepended to its name.

          o The location of the corrupt database directory. That is, the directory which holds its service.properties file.

          o The bootpassword of the corrupt database.

          o The username of the owner of the corrupt database.

          o The password of the owner of the corrupt database.

          You load the tool like this:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          true,
          'RawDBReader',
          'CONTROL',
          'RAW_',
          '/Users/corruptdbs/badDB',
          null,
          'FRED_THE_DBO',
          'fredspassword'
          );

          If the corrupt database has a table named T1 in schema S1, you can dump its data like this:

          select * from RAW_S1.T1;

          When you are done dumping the data, you can unload the tool as follows. This will drop all of the schema objects which were created when the tool was loaded:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          false,
          'RawDBReader',
          'CONTROL',
          'RAW_'
          );

          Show
          Rick Hillegas added a comment - Attaching the first rev of RawDBReader, a custom tool for dumping the data in a corrupt database. This tool makes use of DataFileVTI. The tool creates schemas, table functions, and wrapper views for reading all user tables in a corrupt database. To use the tool you must specify the bootpassword (if applicable) and the credentials of the owner of the corrupt database. The tool takes the following arguments: o The name of a control schema to create in your local database. The control schema will hold views against the core catalogs in t e corrupt database. o A prefix to prepend to the names of user schemas. For each schema in the corrupt database, the tool creates a corresponding lo al schema with this prefix prepended to its name. o The location of the corrupt database directory. That is, the directory which holds its service.properties file. o The bootpassword of the corrupt database. o The username of the owner of the corrupt database. o The password of the owner of the corrupt database. You load the tool like this: call syscs_util.syscs_register_tool ( 'customTool', true, 'RawDBReader', 'CONTROL', 'RAW_', '/Users/corruptdbs/badDB', null, 'FRED_THE_DBO', 'fredspassword' ); If the corrupt database has a table named T1 in schema S1, you can dump its data like this: select * from RAW_S1.T1; When you are done dumping the data, you can unload the tool as follows. This will drop all of the schema objects which were created when the tool was loaded: call syscs_util.syscs_register_tool ( 'customTool', false, 'RawDBReader', 'CONTROL', 'RAW_' );
          Hide
          Rick Hillegas added a comment -

          Attaching the first rev of DataFileVTI, a table function which reads a heap conglomerate. Also attaching dataFileVTI.sql, a script which shows how to use this table function to read the contents of SYSSCHEMAS, SYSTABLES, SYSCONGLOMERATES, and SYSCOLUMNS. There is more work to be done on this table function, but I think that it's worth getting community feedback on this early version.

          The table function takes the following arguments:

          (
          databaseDirectoryName varchar( 32672 ),
          heapConglomerateFileName varchar( 32672 ),
          tableSignature varchar( 32672 ),
          bootpassword varchar( 32672 ),
          dboName varchar( 32672 ),
          dboPassword varchar( 32672 )
          )

          ...and requires that you declare the shape of the rows in the target heap. Here, for instance, is how you would declare a table function to read SYS.SYSSCHEMAS in a corrupt database:

          create function sysschemas
          (
          databaseDirectoryName varchar( 32672 ),
          dataFileName varchar( 32672 ),
          tableSignature varchar( 32672 ),
          bootpassword varchar( 32672 ),
          userName varchar( 32672 ),
          password varchar( 32672 )
          )
          returns table
          ( schemaID char(36), schemaname varchar(128), authorizationid varchar(128) )
          language java
          parameter style derby_jdbc_result_set
          no sql
          external name 'DataFileVTI.dataFileVTI';

          Typically, I think that you would wrap the table function in a view in order to hide the messy arguments:

          create view sysschemas
          as select * from table
          (
          sysschemas
          (
          '/Users/rh161140/derby/mainline/db1',
          'cc0.dat',
          '( schemaID char(36), schemaname varchar(128), authorizationid varchar(128) )',
          null,
          'APP',
          null
          )
          ) t;

          Then you would dump the data in the heap by selecting from the view:

          select * from sysschemas;

          You will get an error if the credentials or bootpassword are invalid or if the user name is not the name of the owner of the corrupt database. I have tested this logic when the following authentication schemes are specified by database-only properties in the corrupt database:

          a) NATIVE
          b) BUILTIN
          c) LDAP
          d) custom

          I am aware of the following defects:

          I) The tool will probably fail to run against really old databases which use BUILTIN authentication or password substitution.

          II) The tool will fail to read rows which spill across multiple heap pages.

          III) I have not stressed the unencryption argument yet. The logic is merely based on what's done in the DataFileReader program attached to DERBY-5201. I'm sure it doesn't handle the difference between bootpasswords and encryption keys.

          Show
          Rick Hillegas added a comment - Attaching the first rev of DataFileVTI, a table function which reads a heap conglomerate. Also attaching dataFileVTI.sql, a script which shows how to use this table function to read the contents of SYSSCHEMAS, SYSTABLES, SYSCONGLOMERATES, and SYSCOLUMNS. There is more work to be done on this table function, but I think that it's worth getting community feedback on this early version. The table function takes the following arguments: ( databaseDirectoryName varchar( 32672 ), heapConglomerateFileName varchar( 32672 ), tableSignature varchar( 32672 ), bootpassword varchar( 32672 ), dboName varchar( 32672 ), dboPassword varchar( 32672 ) ) ...and requires that you declare the shape of the rows in the target heap. Here, for instance, is how you would declare a table function to read SYS.SYSSCHEMAS in a corrupt database: create function sysschemas ( databaseDirectoryName varchar( 32672 ), dataFileName varchar( 32672 ), tableSignature varchar( 32672 ), bootpassword varchar( 32672 ), userName varchar( 32672 ), password varchar( 32672 ) ) returns table ( schemaID char(36), schemaname varchar(128), authorizationid varchar(128) ) language java parameter style derby_jdbc_result_set no sql external name 'DataFileVTI.dataFileVTI'; Typically, I think that you would wrap the table function in a view in order to hide the messy arguments: create view sysschemas as select * from table ( sysschemas ( '/Users/rh161140/derby/mainline/db1', 'cc0.dat', '( schemaID char(36), schemaname varchar(128), authorizationid varchar(128) )', null, 'APP', null ) ) t; Then you would dump the data in the heap by selecting from the view: select * from sysschemas; You will get an error if the credentials or bootpassword are invalid or if the user name is not the name of the owner of the corrupt database. I have tested this logic when the following authentication schemes are specified by database-only properties in the corrupt database: a) NATIVE b) BUILTIN c) LDAP d) custom I am aware of the following defects: I) The tool will probably fail to run against really old databases which use BUILTIN authentication or password substitution. II) The tool will fail to read rows which spill across multiple heap pages. III) I have not stressed the unencryption argument yet. The logic is merely based on what's done in the DataFileReader program attached to DERBY-5201 . I'm sure it doesn't handle the difference between bootpasswords and encryption keys.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development