Derby
  1. Derby
  2. DERBY-2354

Unable to perform select query using DISTINCT on a read-only database

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached

      Description

      It is not possible to perform queries using DISTINCT on a read-only database packaged in a zip file. This generates the following error:

      ERROR 40XD1: Container was opened in read-only mode.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.store.raw.data.BaseContainer.use(Unknown Source)
      at org.apache.derby.impl.store.raw.data.BaseContainerHandle.useContainer(Unknown Source)
      at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown Source)
      at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown Source)
      at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Unknown Source)
      at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.addContainer(Unknown Source)
      at org.apache.derby.impl.store.raw.xact.Xact.addContainer(Unknown Source)
      at org.apache.derby.impl.store.access.heap.Heap.create(Unknown Source)
      at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown Source)
      at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown Source)
      at org.apache.derby.iapi.store.access.DiskHashtable.<init>(Unknown Source)
      at org.apache.derby.iapi.store.access.BackingStoreHashtable.spillToDisk(Unknown Source)
      at org.apache.derby.iapi.store.access.BackingStoreHashtable.add_row_to_hash_table(Unknown Source)
      at org.apache.derby.iapi.store.access.BackingStoreHashtable.put(Unknown Source)
      at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)
      at org.apache.derby.impl.store.access.btree.BTreeScan.fetchSet(Unknown Source)
      at org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(Unknown Source)
      at org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(Unknown Source)
      at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
      at DerbyTest.main(DerbyTest.java:29)

      The problem can be reproduced using the attached java program and the following database file:
      http://ftp2.bigcat.unimaas.nl/~thomas.kelder/derbytest/testdb.zip.
      Both the 'derby.storage.tempDirectory' and 'derby.stream.error.file' properties are set to writable locations, as advised in the help file.

      Also see derby-user mailing list thread: http://article.gmane.org/gmane.comp.apache.db.derby.user/6123

      "This appears to be a bug, possibly a regression. When I converted your
      DB to10.0 everything worked fine even when I did NOT set the properties
      for tempDirectory and error.file (hmmm..). When I switched to using the
      10.1 or 10.2 jars and accessed the very same database the 40XD1 ERROR
      happened." (Stanley Bradbury)

      1. derby-2354-1c.diff
        13 kB
        Knut Anders Hatlen
      2. derby-2354-1b.diff
        13 kB
        Knut Anders Hatlen
      3. derby-2354-1a.diff
        7 kB
        Knut Anders Hatlen
      4. d2354-repro.sql
        0.1 kB
        Knut Anders Hatlen
      5. d2354-createdb.sql
        0.4 kB
        Knut Anders Hatlen
      6. DerbyTest.java
        1.0 kB
        Thomas Kelder

        Activity

        Hide
        Thomas Kelder added a comment -

        Small java program that reproduces this problem. Use this database file:
        http://ftp2.bigcat.unimaas.nl/~thomas.kelder/derbytest/testdb.zip

        Show
        Thomas Kelder added a comment - Small java program that reproduces this problem. Use this database file: http://ftp2.bigcat.unimaas.nl/~thomas.kelder/derbytest/testdb.zip
        Hide
        Øystein Grøvlen added a comment -

        The stack trace indicates that this happens when a BackingStoreHashtable gets too large and needs to be spilled to disk. Since the DB is read-only, it is not permitted to create a container for this Hashtable. I guess the Hashtable is used to find the distinct records in the result set, and that Derby needs to allow such files to be created.

        Show
        Øystein Grøvlen added a comment - The stack trace indicates that this happens when a BackingStoreHashtable gets too large and needs to be spilled to disk. Since the DB is read-only, it is not permitted to create a container for this Hashtable. I guess the Hashtable is used to find the distinct records in the result set, and that Derby needs to allow such files to be created.
        Hide
        Kathey Marsden added a comment -

        Do you have this problem if you set derby.storage.tempDirectory per
        http://db.apache.org/derby/docs/dev/devguide/tdevdeploy26887.html ?

        Show
        Kathey Marsden added a comment - Do you have this problem if you set derby.storage.tempDirectory per http://db.apache.org/derby/docs/dev/devguide/tdevdeploy26887.html ?
        Hide
        Venkateswaran Iyer added a comment -

        Setting the derby.storage.tempDirectory does not get past this problem.

        Show
        Venkateswaran Iyer added a comment - Setting the derby.storage.tempDirectory does not get past this problem.
        Hide
        Rick Hillegas added a comment -

        Triaged for 10.5.2: assigned normal urgency and noted that a repro is attached.

        Show
        Rick Hillegas added a comment - Triaged for 10.5.2: assigned normal urgency and noted that a repro is attached.
        Hide
        Knut Anders Hatlen added a comment -

        It looks like the test db can no longer be downloaded, so I'm uploading a new repro for the bug. Follow these steps to reproduce:

        1) Create a database by running the d2354-createdb.sql script in ij:

        java -jar derbyrun.jar ij d2354-createdb.sql

        2) Put the database in a zip file:

        zip -r testdb.zip db

        3) Run the d2354-repro.sql script in ij. Set maximum heap size to make sure the hash table will have to spill to disk:

        java -Xmx16M -jar derbyrun.jar ij d2354-repro.sql

        Show
        Knut Anders Hatlen added a comment - It looks like the test db can no longer be downloaded, so I'm uploading a new repro for the bug. Follow these steps to reproduce: 1) Create a database by running the d2354-createdb.sql script in ij: java -jar derbyrun.jar ij d2354-createdb.sql 2) Put the database in a zip file: zip -r testdb.zip db 3) Run the d2354-repro.sql script in ij. Set maximum heap size to make sure the hash table will have to spill to disk: java -Xmx16M -jar derbyrun.jar ij d2354-repro.sql
        Hide
        Knut Anders Hatlen added a comment -

        Note that the error message changed in Derby 10.3. In 10.2 the following error was raised (same as in the original report):

        ij version 10.2
        ij> connect 'jdbc:derby:jar:(testdb.zip)db';
        ij> select distinct id from t;
        ERROR 40XD1: Container was opened in read-only mode.

        Derby 10.3 and later instead fails like this:

        ij version 10.9
        ij> connect 'jdbc:derby:jar:(testdb.zip)db';
        ij> select distinct id from t;
        ERROR XSAI3: Feature not implemented.

        Stack trace from the latest development sources:

        ERROR XSAI3: Feature not implemented.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:276)
        at org.apache.derby.impl.store.raw.log.ReadOnly.checkVersion(ReadOnly.java:458)
        at org.apache.derby.impl.store.raw.RawStore.checkVersion(RawStore.java:2255)
        at org.apache.derby.impl.store.access.RAMTransaction.checkVersion(RAMTransaction.java:2166)
        at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(HeapConglomerateFactory.java:175)
        at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(RAMTransaction.java:823)
        at org.apache.derby.iapi.store.access.DiskHashtable.<init>(DiskHashtable.java:123)
        at org.apache.derby.iapi.store.access.BackingStoreHashtable.spillToDisk(BackingStoreHashtable.java:483)
        at org.apache.derby.iapi.store.access.BackingStoreHashtable.add_row_to_hash_table(BackingStoreHashtable.java:400)
        at org.apache.derby.iapi.store.access.BackingStoreHashtable.putRow(BackingStoreHashtable.java:728)
        at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:803)
        at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchSet(GenericScanController.java:1268)
        at org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(BackingStoreHashTableFromScan.java:117)
        at org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(RAMTransaction.java:1355)
        at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(HashScanResultSet.java:262)
        at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:255)
        at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:630)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:559)
        at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
        at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
        at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363)
        at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
        at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
        at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
        at org.apache.derby.tools.ij.main(ij.java:59)
        at org.apache.derby.iapi.tools.run.main(run.java:53)

        (The message changed after DERBY-2537.)

        Show
        Knut Anders Hatlen added a comment - Note that the error message changed in Derby 10.3. In 10.2 the following error was raised (same as in the original report): ij version 10.2 ij> connect 'jdbc:derby:jar:(testdb.zip)db'; ij> select distinct id from t; ERROR 40XD1: Container was opened in read-only mode. Derby 10.3 and later instead fails like this: ij version 10.9 ij> connect 'jdbc:derby:jar:(testdb.zip)db'; ij> select distinct id from t; ERROR XSAI3: Feature not implemented. Stack trace from the latest development sources: ERROR XSAI3: Feature not implemented. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:276) at org.apache.derby.impl.store.raw.log.ReadOnly.checkVersion(ReadOnly.java:458) at org.apache.derby.impl.store.raw.RawStore.checkVersion(RawStore.java:2255) at org.apache.derby.impl.store.access.RAMTransaction.checkVersion(RAMTransaction.java:2166) at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(HeapConglomerateFactory.java:175) at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(RAMTransaction.java:823) at org.apache.derby.iapi.store.access.DiskHashtable.<init>(DiskHashtable.java:123) at org.apache.derby.iapi.store.access.BackingStoreHashtable.spillToDisk(BackingStoreHashtable.java:483) at org.apache.derby.iapi.store.access.BackingStoreHashtable.add_row_to_hash_table(BackingStoreHashtable.java:400) at org.apache.derby.iapi.store.access.BackingStoreHashtable.putRow(BackingStoreHashtable.java:728) at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:803) at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchSet(GenericScanController.java:1268) at org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(BackingStoreHashTableFromScan.java:117) at org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(RAMTransaction.java:1355) at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(HashScanResultSet.java:262) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:255) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:630) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:559) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261) at org.apache.derby.impl.tools.ij.Main.go(Main.java:229) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) at org.apache.derby.tools.ij.main(ij.java:59) at org.apache.derby.iapi.tools.run.main(run.java:53) (The message changed after DERBY-2537 .)
        Hide
        Knut Anders Hatlen added a comment -

        Attaching a patch that may fix the problem (derby-2354-1a.diff). It makes the repro pass in my environment, but it's not tested in any other way yet.

        It makes the following changes:

        M java/engine/org/apache/derby/impl/store/access/btree/index/B2IFactory.java
        M java/engine/org/apache/derby/impl/store/access/heap/HeapConglomerateFactory.java

        Short-circuit the checkVersion() calls if it's a read-only database trying to create a temporary conglomerate. This was needed in order to get rid of the "feature not supported" errors seen in 10.3 and later.

        M java/engine/org/apache/derby/impl/store/raw/data/InputStreamContainer.java

        When reusing a container cache entry that contains an InputStreamContainer (a read-only container), replace it with a TempRAFContainer if the new conglomerate is a temporary conglomerate.

        M java/engine/org/apache/derby/impl/store/raw/data/TempRAFContainer.java

        Use factory methods instead of creating RAFContainer objects directly when replacing a TempRAFContainer with a non-temporary container object. This allows switching back from TempRAFContainer to InputStreamContainer for read-only/zip databases. (The opposite of the situation handled by the change in InputStreamContainer.)

        M java/engine/org/apache/derby/impl/store/raw/data/FileContainer.java

        Remove implementations of setIdentity() and createIdentity() since they are now implemented by all of FileContainer's sub-classes. (It would probably make more sense to move the implementations in RAFContainer and InputStreamContainer back into FileContainer, since they're essentially identical now.)

        Show
        Knut Anders Hatlen added a comment - Attaching a patch that may fix the problem (derby-2354-1a.diff). It makes the repro pass in my environment, but it's not tested in any other way yet. It makes the following changes: M java/engine/org/apache/derby/impl/store/access/btree/index/B2IFactory.java M java/engine/org/apache/derby/impl/store/access/heap/HeapConglomerateFactory.java Short-circuit the checkVersion() calls if it's a read-only database trying to create a temporary conglomerate. This was needed in order to get rid of the "feature not supported" errors seen in 10.3 and later. M java/engine/org/apache/derby/impl/store/raw/data/InputStreamContainer.java When reusing a container cache entry that contains an InputStreamContainer (a read-only container), replace it with a TempRAFContainer if the new conglomerate is a temporary conglomerate. M java/engine/org/apache/derby/impl/store/raw/data/TempRAFContainer.java Use factory methods instead of creating RAFContainer objects directly when replacing a TempRAFContainer with a non-temporary container object. This allows switching back from TempRAFContainer to InputStreamContainer for read-only/zip databases. (The opposite of the situation handled by the change in InputStreamContainer.) M java/engine/org/apache/derby/impl/store/raw/data/FileContainer.java Remove implementations of setIdentity() and createIdentity() since they are now implemented by all of FileContainer's sub-classes. (It would probably make more sense to move the implementations in RAFContainer and InputStreamContainer back into FileContainer, since they're essentially identical now.)
        Hide
        Knut Anders Hatlen added a comment -

        All the regression tests passed with the 1a patch.

        I'm uploading a new patch (1b) with the following changes:

        • Added a test case to DBInJarTest. The new test case performs a SELECT DISTINCT and a hash join in a read-only database contained in a jar file. It fails without the fix.
        • Added some comments to the new code.
        • Moved setIdentity() and createIdentity() from RAFContainer to FileContainer so that they are inherited by InputStreamContainer.
        • Removed the changes in InputStreamContainer since the functionality is now provided by the base class (FileContainer).

        I've started a new test run with the updated patch.

        Show
        Knut Anders Hatlen added a comment - All the regression tests passed with the 1a patch. I'm uploading a new patch (1b) with the following changes: Added a test case to DBInJarTest. The new test case performs a SELECT DISTINCT and a hash join in a read-only database contained in a jar file. It fails without the fix. Added some comments to the new code. Moved setIdentity() and createIdentity() from RAFContainer to FileContainer so that they are inherited by InputStreamContainer. Removed the changes in InputStreamContainer since the functionality is now provided by the base class (FileContainer). I've started a new test run with the updated patch.
        Hide
        Dag H. Wanvik added a comment -

        As far as I remember, scrollable, updatable result sets may also spill to disk. This begs the question, is read-only database an issue for that feature, too? And if so, does this help patch help for that as well?

        Show
        Dag H. Wanvik added a comment - As far as I remember, scrollable, updatable result sets may also spill to disk. This begs the question, is read-only database an issue for that feature, too? And if so, does this help patch help for that as well?
        Hide
        Knut Anders Hatlen added a comment -

        Scrollable updatable result sets would run into problems on read-only database when trying to update the database. Scrollable read-only result sets are supposed to work, but as you suspected, they have the same problem with hash tables spilling to disk. In fact, there was a thread on derby-user about it a while ago: http://mail-archives.apache.org/mod_mbox/db-derby-user/200812.mbox/%3C20957328.post@talk.nabble.com%3E

        The good news is that the patch seems to fix that problem too. I've added a test for it and uploaded a new patch (1c).

        All the regression tests passed with the 1c patch.

        Show
        Knut Anders Hatlen added a comment - Scrollable updatable result sets would run into problems on read-only database when trying to update the database. Scrollable read-only result sets are supposed to work, but as you suspected, they have the same problem with hash tables spilling to disk. In fact, there was a thread on derby-user about it a while ago: http://mail-archives.apache.org/mod_mbox/db-derby-user/200812.mbox/%3C20957328.post@talk.nabble.com%3E The good news is that the patch seems to fix that problem too. I've added a test for it and uploaded a new patch (1c). All the regression tests passed with the 1c patch.
        Hide
        Bryan Pendleton added a comment -

        Sorry Knut Anders, I haven't been paying attention: does your patch fix the problem by preventing
        in-memory data structures from spilling to disk, returning an error in that case? Or does your
        patch fix the problem by allowing in-memory data structure to spill to disk, arranging to use a
        temporary writable disk location rather than the read-only location of the database itself?

        Show
        Bryan Pendleton added a comment - Sorry Knut Anders, I haven't been paying attention: does your patch fix the problem by preventing in-memory data structures from spilling to disk, returning an error in that case? Or does your patch fix the problem by allowing in-memory data structure to spill to disk, arranging to use a temporary writable disk location rather than the read-only location of the database itself?
        Hide
        Knut Anders Hatlen added a comment -

        Hi Bryan,

        The patch fixes the problem by spilling to disk on a writable location. It uses the derby.storage.tempDirectory if it's set. Otherwise, some system-defined location is used. On my system, the temporary files end up in directories under /var/tmp.

        One other thing that's fixed by the patch, is a regression after DERBY-2537. Read-only databases that were not stored in a jar or a zip used to work even when the hash tables spilled to disk (with one caveat: you had to set derby.storage.tempDirectory or make the database's tmp directory writable, since it would not use the system temp dir automatically). In 10.3, it started raising this "feature not implemented" error because of the checkVersion() calls in B2IFactory and HeapConglomerateFactory. Since the patch makes read-only databases stop calling checkVersion() in those factories, these databases will get their pre-10.3 behaviour back.

        Show
        Knut Anders Hatlen added a comment - Hi Bryan, The patch fixes the problem by spilling to disk on a writable location. It uses the derby.storage.tempDirectory if it's set. Otherwise, some system-defined location is used. On my system, the temporary files end up in directories under /var/tmp. One other thing that's fixed by the patch, is a regression after DERBY-2537 . Read-only databases that were not stored in a jar or a zip used to work even when the hash tables spilled to disk (with one caveat: you had to set derby.storage.tempDirectory or make the database's tmp directory writable, since it would not use the system temp dir automatically). In 10.3, it started raising this "feature not implemented" error because of the checkVersion() calls in B2IFactory and HeapConglomerateFactory. Since the patch makes read-only databases stop calling checkVersion() in those factories, these databases will get their pre-10.3 behaviour back.
        Hide
        Bryan Pendleton added a comment -

        Good, that sounds like a good approach.

        I looked through the patch and it looks fine to me. Thanks for adding the comments
        near the checkVersion calls; they make sense and are clear.

        +1

        Show
        Bryan Pendleton added a comment - Good, that sounds like a good approach. I looked through the patch and it looks fine to me. Thanks for adding the comments near the checkVersion calls; they make sense and are clear. +1
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Bryan. Committed revision 1092067.

        Show
        Knut Anders Hatlen added a comment - Thanks, Bryan. Committed revision 1092067.
        Hide
        Knut Anders Hatlen added a comment -

        Merged the fix to the 10.8 branch and committed revision 1092649.

        Show
        Knut Anders Hatlen added a comment - Merged the fix to the 10.8 branch and committed revision 1092649.
        Hide
        Kathey Marsden added a comment -

        Reopen to mark affects version 10.5 for possible backport.

        Show
        Kathey Marsden added a comment - Reopen to mark affects version 10.5 for possible backport.
        Hide
        Mamta A. Satoor added a comment -

        I will work on backporting this

        Show
        Mamta A. Satoor added a comment - I will work on backporting this
        Hide
        Mamta A. Satoor added a comment -

        Backported into 10.7 with revision 1389677

        Show
        Mamta A. Satoor added a comment - Backported into 10.7 with revision 1389677
        Hide
        Mamta A. Satoor added a comment -

        Backported into 10.6 with revision 1389924

        Show
        Mamta A. Satoor added a comment - Backported into 10.6 with revision 1389924
        Hide
        Mamta A. Satoor added a comment -

        Backported into 10.5 with revision 1390271

        Show
        Mamta A. Satoor added a comment - Backported into 10.5 with revision 1390271

          People

          • Assignee:
            Knut Anders Hatlen
            Reporter:
            Thomas Kelder
          • Votes:
            4 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development