Exception during startup of HA-JDBC 3.0.0 with Oracle 11

[Justin Cranford]

Hi Paul,

I have HA-JDBC 3.0.0 working with MySQL now, so I moved on to Oracle. HA-JDBC initialized ok and I can get a connection, but I get an exception while calling Connection.prepareStatement(String). This only happens using HA-JDBC, not if I connect to the Oracle database directly.

I am tested my applications Oracle compatibility with HA-JDBC 3.0.0, Tomcat 5.5.31, Java 7u45 x32, and ojdbc7.jar (from Oracle 12.1.0.1.0). I am connecting to an Oracle 11.2.0.1.0 database. All are co-located on my Windows 7 x64 desktop.

java.sql.SQLException: ORA-25191: cannot reference overflow table of an index-organized table
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:853)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1469)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:389)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at net.sf.hajdbc.dialect.StandardDialect.getColumns(StandardDialect.java:745)
    at net.sf.hajdbc.cache.eager.EagerTableProperties.<init>(EagerTableProperties.java:52)
    at net.sf.hajdbc.cache.eager.EagerDatabaseProperties.<init>(EagerDatabaseProperties.java:55)
    at net.sf.hajdbc.cache.eager.EagerDatabaseMetaDataCache.createDatabaseProperties(EagerDatabaseMetaDataCache.java:103)
    at net.sf.hajdbc.cache.eager.EagerDatabaseMetaDataCache.getDatabaseProperties(EagerDatabaseMetaDataCache.java:90)
    at net.sf.hajdbc.sql.AbstractSQLProxyFactory.getDatabaseProperties(AbstractSQLProxyFactory.java:158)
    at net.sf.hajdbc.sql.AbstractSQLProxyFactory.isSelectForUpdate(AbstractSQLProxyFactory.java:164)
    at net.sf.hajdbc.sql.ConnectionInvocationHandler.getProxyFactoryFactory(ConnectionInvocationHandler.java:91)
    at net.sf.hajdbc.sql.ConnectionInvocationHandler.getProxyFactoryFactory(ConnectionInvocationHandler.java:44)
    at net.sf.hajdbc.sql.AbstractInvocationHandler.invokeOnProxy(AbstractInvocationHandler.java:100)
    at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:85)
    at net.sf.hajdbc.sql.ChildInvocationHandler.invoke(ChildInvocationHandler.java:44)
    at com.sun.proxy.$Proxy16.prepareStatement(Unknown Source)

[Justin Cranford]

It works if I switch from EagerDatabaseMetaDataCacheFactory to LazyDatabaseMetaDataCacheFactory. What am I losing by switching to lazy method?

[Paul Ferraro]

HA-JDBC uses DatabaseMetaData.getTables(...) method to determine the table for which to cache meta data. The intention is that only user tables would be returned by this method. It looks like, for Oracle at least, this method is returning certain system-generated tables. Can you verify the output of this method? In HA-JDBC, this logic is implemented by the Dialect abstraction. To workaround this, you can override this logic in a custom dialect that extends OracleDialect. See the docs for how to do this. When you figure out how to exclude the problematic table, let me know so I can apply the fix to the OracleDialect.

[Justin Cranford]

See attached list of tables returned by Oracle DatabaseMetaData.getTables().

I tried passing hard-coded catalog="User", but it had no filtering effect. I got the idea because it is the default in EclipseEE Database Developer properties for Oracle JDBC connections, but it did not work for me.

What did work is passing schemaPattern=metaData.getUserName(). This value is populated in my table list, and filtering on it worked. Unfortunately Oracle JDBC URL does not allow specifying the default schema, but it is implicitly the same as username in Oracle.

Not sure if this applies to all databases, but I imagine this is the fix in StandardDialect.java. If it breaks other databases, then I guess it belongs in method overrides in OracleDialect.java.

Change line 710 of StandardDialect.java from this:

    ResultSet resultSet = metaData.getTables(getCatalog(metaData), null, Strings.ANY, new String[] { "TABLE" });

... to this ...

    ResultSet resultSet = metaData.getTables(getCatalog(metaData), metaData.getUserName(), Strings.ANY, new String[] { "TABLE" });

[Justin Cranford]

Is eager Oracle meta data lookup resolved in upcoming HA-JDBC maintenance release? It does not block me since lazy meta data lookup is a confirmed workaround. I am just curious.

[Paul Ferraro]

https://github.com/ha-jdbc/ha-jdbc/pull/43
Using lazy meta data caching is not a feasible workaround, since you would run into this issue eventually, i.e. once that meta data is fetched.