Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-31955

Fix ORA-03146 when setting a CLOB in a prepared statement

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2021.40, 2023.1
    • Component/s: Key/Value Store
    • Release Notes Summary:
      CLOB values are set with the setClob method in PreparedStatement.
    • Tags:
    • Backlog priority:
      900
    • Sprint:
      nxplatform #91
    • Story Points:
      3

      Description

      According to Oracle, Nuxeo's DialectOracle class produces statements which will fail against some Oracle instances (mainly Amazon RDS for Oracle)

      The problem comes from DialectOracle#setToPreparedStatement which handles CLOB and VARCHAR columns with the same code which calls "ps.setString()" whereas a CLOB value must be set with ps.setClob()

      https://github.com/nuxeo/nuxeo-lts/blob/2021/modules/core/nuxeo-core-storage-sql/nuxeo-core-storage-sql/src/main/java/org/nuxeo/ecm/core/storage/sql/jdbc/dialect/DialectOracle.java#L416-L418

      As a result, an exception is thrown when setting a big string value to a CLOB column:

      ERROR [stream/introspectionPool-00,in:0,inCheckpoint:0,out:0,lastRead:1684165600886,lastTimer:0,wm:0,loop:2,rebalance assigned] [org.nuxeo.lib.stream.computation.AbstractComputation] Computation: stream/introspection fails last record: internal-processors-00:+3527, after retries.
org.nuxeo.ecm.core.api.NuxeoException: java.sql.SQLException: ORA-03146: invalid buffer length for TTC field

at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.put(SQLKeyValueStore.java:791) ~[nuxeo-core-storage-sql-2021.20.1.jar:?]
 at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.put(SQLKeyValueStore.java:736) ~[nuxeo-core-storage-sql-2021.20.1.jar:?]
 at org.nuxeo.ecm.core.bulk.introspection.StreamIntrospectionComputation.updateModel(StreamIntrospectionComputation.java:159) ~[nuxeo-core-bulk-2021.20.1.jar:?]
 at org.nuxeo.ecm.core.bulk.introspection.StreamIntrospectionComputation.processRecord(StreamIntrospectionComputation.java:134) ~[nuxeo-core-bulk-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.lambda$processRecordWithRetry$10(ComputationRunner.java:507) ~[nuxeo-stream-2021.20.1.jar:?]
 at net.jodah.failsafe.Functions$10.call(Functions.java:252) ~[failsafe-1.1.0.jar:1.1.0]
 at net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145) [failsafe-1.1.0.jar:1.1.0]
 at net.jodah.failsafe.SyncFailsafe.run(SyncFailsafe.java:81) [failsafe-1.1.0.jar:1.1.0]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.processRecordWithRetry(ComputationRunner.java:507) [nuxeo-stream-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.processRecordWithTracing(ComputationRunner.java:458) [nuxeo-stream-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.processRecord(ComputationRunner.java:450) [nuxeo-stream-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.processLoop(ComputationRunner.java:308) [nuxeo-stream-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.runOnce(ComputationRunner.java:252) [nuxeo-stream-2021.20.1.jar:?]
 at org.nuxeo.lib.stream.computation.log.ComputationRunner.run(ComputationRunner.java:225) [nuxeo-stream-2021.20.1.jar:?]
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
 at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
 at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.sql.SQLException: ORA-03146: invalid buffer length for TTC field

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.8.0.jar:2.8.0]
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.8.0.jar:2.8.0]
 at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.put(SQLKeyValueStore.java:776) ~[nuxeo-core-storage-sql-2021.20.1.jar:?]
 ... 18 more
Caused by: oracle.jdbc.OracleDatabaseException: ORA-03146: invalid buffer length for TTC field

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081) ~[ojdbc10-19.15.0.0.1.jar:19.15.0.0.1]
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.8.0.jar:2.8.0]
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[commons-dbcp2-2.8.0.jar:2.8.0]
 at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.put(SQLKeyValueStore.java:776) ~[nuxeo-core-storage-sql-2021.20.1.jar:?]
 ... 18 more

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: