[
https://issues.apache.org/jira/browse/NIFI-8119?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17275587#comment-17275587
]
Christian Gumpert commented on NIFI-8119:
-----------------------------------------
[~pwicks] pointed out a possible work around on the mailing list:
{quote}Can you try setting `FINALIZE_AUTO_CLOSE=ON` in your connection string
to Teradata? It’s not a best practice, but based on what the docs say, I think
it might work.
>From the docs:
FINALIZE_AUTO_CLOSE values are OFF (default) or ON:
• When set to OFF (the default), the Teradata JDBC Driver provides the JDBC
4.0 API Specification behavior such that JDBC objects are not closed
automatically during finalize. The application is responsible for closing or
freeing JDBC objects.
• When set to ON, the Teradata JDBC Driver provides the JDBC 3.0 API
Specification behavior to close JDBC objects during finalize. This will have a
performance impact on garbage collection, and is not recommended.
Java programming best practice is to avoid finalize methods altogether. If a
finalize method is used, best practice is to minimize its processing time, and
to avoid operations that can take a long time, such as network communications.
The JDBC 3.0 API Specification contradicted these best practices by requiring a
JDBC Driver to close JDBC objects automatically during garbage collection. The
JDBC 4.0 API Specification dropped the requirement for automatic closing of
JDBC objects during garbage collection, so the JDBC 4.0 API Specification is in
agreement with these best practices.
Garbage collection can be blocked indefinitely when FINALIZE_AUTO_CLOSE is set
to ON, and the Teradata JDBC Driver does not receive a response from the
database after sending a message to the database to close the response spool.
This parameter is available for SQL connections beginning with Teradata JDBC
Driver 14.00.00.08
{quote}
This worked for me locally so I reduced the criticality to minor.
> ExecuteSQL does not properly free database ressources
> -----------------------------------------------------
>
> Key: NIFI-8119
> URL: https://issues.apache.org/jira/browse/NIFI-8119
> Project: Apache NiFi
> Issue Type: Bug
> Components: Extensions
> Affects Versions: 1.11.2
> Reporter: Christian Gumpert
> Priority: Minor
>
> We are using Nifi to ingest data from a Teradata database into our S3-based
> data lake using a typical pattern of GenerateTableFetch and ExecuteSQL
> processors. Our Teradata database tables contain columns of type CLOB (which
> contains some JSON data).
> We have installed the Teradata JDBC driver from the Teradata Tools and
> Utilities package version 16.10.26.00 as described in this [Cloudera
> community
> article|https://community.cloudera.com/t5/Community-Articles/Using-Teradata-JDBC-connector-in-NiFi/ta-p/246783].
> After having configured a DBConnectionPool service with the Teradata
> connection parameters we are able to execute our flow. The GenerateTableFetch
> processors generates flowfiles containing SQL Queries which are then executed
> by the ExecuteSQL processor.
> After having processed the first 15 flowfiles the ExecuteSQL processor yields
> the following error:
> {noformat}
> 2020-12-17T12:53:17+01:00 L921000109090A nifi-app.log: 2020-12-17
> 12:53:11,578 ERROR [Timer-Driven Process Thread-2]
> o.a.nifi.processors.standard.ExecuteSQL
> ExecuteSQL[id=afa23b0f-2e57-1fb6-d047-13646de03ebf] Unable to execute SQL
> select query call devezv_replworkedec.get_edec_meldung(561, 562, 2, 0); for
> StandardFlowFileRecord[uuid=ff7219a7-14e9-404e-a57a-28121653fed8,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1607610786368-4986, container=repo0,
> section=890], offset=701888,
> length=1077672],offset=32266,name=ff7219a7-14e9-404e-a57a-28121653fed8,size=58]
> due to java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.07]
> [Error 3130] [SQLState HY000] GET_EDEC_MELDUNG:Response limit exceeded.;
> routing to failure: java.sql.SQLException: [Teradata Database] [TeraJDBC
> 16.10.00.07] [Error 3130] [SQLState HY000] GET_EDEC_MELDUNG:Response limit
> exceeded.
> java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.07] [Error
> 3130] [SQLState HY000] GET_EDEC_MELDUNG:Response limit exceeded.
> at
> com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:309)
> at
> com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)
> at
> com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)
> at
> com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)
> at
> com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)
> at
> com.teradata.jdbc.jdbc_4.statemachine.PreparedStatementController.run(PreparedStatementController.java:46)
> at
> com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389)
> at
> com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:331)
> at
> com.teradata.jdbc.jdbc_4.TDPreparedStatement.doPrepExecute(TDPreparedStatement.java:177)
> at
> com.teradata.jdbc.jdbc_4.TDPreparedStatement.execute(TDPreparedStatement.java:2778)
> at
> org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
> at
> org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
> at
> org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:266)
> at
> org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)
> at
> org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
> at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748){noformat}
> After googling for a while it appears that the important part is
> *java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.07] [Error
> 3130] [SQLState HY000] GET_EDEC_MELDUNG:Response limit exceeded.*
> The [Teradata documentation for "Working with
> LOBs"|https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_5.html#CHDGCHBB]
> contains information on how to properly free resources to avoid this limit.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
