lmetrak
lmetrak

Reputation: 65

java.sql.SQLRecoverableException: Closed Connection during receiving blob field from oracle database

I'm facing the strange issue during receiving blob field from oracle database: "SQLRecoverableException: Closed Connection

stack trace describing this exception:

java.sql.SQLRecoverableException: Closed Connection
    at oracle.jdbc.driver.OracleBlob.getDBAccess(OracleBlob.java:960) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleBlob.getBinaryStream(OracleBlob.java:319) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleBlob.getBinaryStream(OracleBlob.java:300) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.sql.BLOB.getBinaryStream(BLOB.java:316) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_221]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_221]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_221]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_221]
    at org.hibernate.engine.jdbc.SerializableBlobProxy.invoke(SerializableBlobProxy.java:60) ~[hibernate-core-5.3.18.Final.jar:5.3.18.Final]
    at com.sun.proxy.$Proxy142.getBinaryStream(Unknown Source) ~[?:?]
    at com.xxx.yyy.util.KeystoreHelper.loadKeyStore(KeystoreHelper.java:89) [cphcore-3.23.0.23-SNAPSHOT.jar:?]
    at com.xxx.yyy.bp.job.substep.ProcessIF2Files.getChannelUnsealer(IFB2UnsealFiles.java:207) [classes/:?]
    at com.xxx.yyy.bp.job.substep.ProcessIF2Files.access$4(IFB2UnsealFiles.java:198) [classes/:?]
    at com.xxx.yyy.bp.job.substep.ProcessIF2Files$1.execute(IFB2UnsealFiles.java:86) [classes/:?]
    at com.xxx.yyy.bp.job.substep.ProcessIF2Files$1.execute(IFB2UnsealFiles.java:1) [classes/:?]

source code: class KeystoreHelper method loadKeyStore:

(...)

    Blob keystoreBlob = keystore.getKeystoreData();
    if (keystoreBlob == null || keystoreBlob.length() == 0) {
      log.error("Keystore data is empty");
      throw new KeystoreAccessException(keystore, "Keystore data is empty", null);
    }

    inputStream = keystore.getKeystoreData().getBinaryStream(); // line 89 - Exception
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    IOUtils.copy(inputStream, outputStream);
    inputStream.close();
    outputStream.close();
    byte[] keystoreData = outputStream.toByteArray();

    inputStream = new ByteArrayInputStream(keystoreData);
    
(...)

source code for variable - keystore, class DbKeystore (entity), method getKeystoreData:

(...)
    private java.sql.Blob keystoreData;
    
    public java.sql.Blob getKeystoreData() {
        return this.keystoreData;
    }
    
    public void setKeystoreData(java.sql.Blob keystoreData) {
        this.keystoreData = keystoreData;
    }
(...)

Have any of you encountered a similar problem? Thanks for any help :)

Upvotes: 1

Views: 2271

Answers (2)

mborkowski
mborkowski

Reputation: 68

I'm working together with @lmetrak and finally I figured out what was wrong. Our application supports Oracle and MSSQL databases. That error occurs only when we use Oracle, because it's caused by oracle's BLOB implementation.

@lmetrak posted peace of KeystoreHelper.class. Here we are out of transaction. We fetched Keystore entity for database on previous step, we closed transaction and we passed entity to KeystoreHelper class.

oracle.sql.BLOB contains information about connection which was used to fetch entity from database. So when we ask for BinaryStream, blob tries to connect to database to download date, because not it's only proxy. It doesn't try to get open connection form connection pool, but it tries to use the same connection which was used to fetch Keystore entity. Sometimes it happens that the connection is closed in the meantime, and then error appears. I'm not sure what's MSSQL strategy, but their implementation of BLOB doesn't contains information about connection and doesn't have that problem.

Our solution was to change Keystore.keystoreData filed type from BLOB to byte[]. Now it works on Oracle and MSSQL.

(...)

     byte[] keystoreData = keystore.getKeystoreData();
     /*A few checks*/
     inputStream = new ByteArrayInputStream(keystore.getKeystoreData());
     ByteArrayOutputStream outputStream= new ByteArrayOutputStream();
     IOUtils.copy(inputStream , outputStream);
     inputStream .close();
     outputStream.close();
     byte[] myKeystoreData = myBos.toByteArray();
     inputStream = new ByteArrayInputStream(myKeystoreData);
    
(...)

Upvotes: 2

Christian Beikov
Christian Beikov

Reputation: 16400

Most likely this is cause by the underlying JDBC connection being put back to a connection pool. Make sure your EntityManager or Session with which you loaded the entity is still active at the point where you access the blob.

Upvotes: 0

Related Questions