db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Problems with SQL-Queries if the database is stored in a jar
Date Mon, 11 Apr 2011 09:33:27 GMT
"Hans Müller" <hansmueller461@gmx.de> writes:

> Hello,
>
> i have a problem with a database which is stored a jar.
>
> The SQL-Queries work well, when the database in not inside a jar.
> But if i store the database in a jar, then some SQL-Queries do not work and throw an
exception.
>
> I have set the properties for the derby.storage.tempDirectory, befor i am loading the
EmbeddedDriver of Derby.
>
> The database is large. It contains some lists with more than 1500, 3400, 3900, 5500 and
8000 lines.
>
> This are the Exceptions:
>
> java.sql.SQLException: Ein Feature ist nicht implementiert.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(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 Datenbankanbindung.mAbfrageElementSonstiges(Datenbankanbindung.java:8758)
> ...
>
> and
>
> Caused by: java.sql.SQLException: Ein Feature ist nicht implementiert.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
> ... 14 more
> Caused by: ERROR XSAI3: Ein Feature ist nicht implementiert.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.log.ReadOnly.checkVersion(Unknown Source)
> at org.apache.derby.impl.store.raw.RawStore.checkVersion(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.checkVersion(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.putRow(Unknown Source)
> at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
Source)
> at org.apache.derby.impl.store.access.conglomerate.GenericScanController.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.JoinResultSet.openRight(Unknown Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.UnionResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 8 more
>
>
> This is an SQL-Querie, which works well with a database which is not stored in a jar
and which throw an exception, if you store the database in a jar:
>
> SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
> FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr = SpracheTab.IdentifikationsNr
INNER JOIN FassadenartenGruppeTab ON ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr

> WHERE SpracheTab.Sprache = 'de' 
> AND ArtikelTab.Baugruppe = 'asdf' 
> AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
> AND ArtikelTab.Lage LIKE '%Vertikal%' 
> AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
> AND ArtikelTab.Sortiment = 's' 
> ORDER BY ArtikelNr
>
> If you put the "ORDER BY"-line away or if you use "ORDER BY IdentifikationsNr" then it
works.
> But it is not only a problem with the "ORDER BY". I have some other SQL-Queries which
do not work too and who have no "ORDER BY".
>
> The following SQL-Querie works well:
>
> SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
> FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr = SpracheTab.IdentifikationsNr
INNER JOIN FassadenartenGruppeTab ON ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr

> WHERE SpracheTab.Sprache = 'de' 
> AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
> AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
> ORDER BY ArtikelNr
>
> It seems, that this problem is similar to the following:
> http://old.nabble.com/ResultSet.next%28%29-throws-ERROR-XSAI3%3A-Feature-not-implemented.-td20957328.html
> and
> https://issues.apache.org/jira/browse/DERBY-2354
>
>
> Could it be, that Derby can only handle little databases, if you put these in a jar (read-only
mode)?
> Is this a Bug?
> How can i fix the problem?

Hi Hans,

It looks like the root cause of this problem is the same as DERBY-2354,
that Derby isn't prepared for handling hash tables that spill to disk in
read-only databases.

You may be able to work around this problem by using optimizer overrides
to force Derby's optimizer to pick nested loop joins instead of hash
joins. That should eliminate the use of hash scans and prevent this bug
from getting triggered, I think. In the failing query above, you would
need to do something like this:

SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
FROM ArtikelTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
  INNER JOIN SpracheTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
    ON ArtikelTab.ArtikelNr = SpracheTab.IdentifikationsNr
  INNER JOIN FassadenartenGruppeTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
    ON ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr 
WHERE SpracheTab.Sprache = 'de' 
AND ArtikelTab.Baugruppe = 'asdf' 
AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
AND ArtikelTab.Lage LIKE '%Vertikal%' 
AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
AND ArtikelTab.Sortiment = 's' 
ORDER BY ArtikelNr

More on optimizer overrides can be found here:
http://db.apache.org/derby/docs/10.7/tuning/ctunoptimzoverride.html

Hope this helps,

-- 
Knut Anders

Mime
View raw message