[Bug]: db connection leak when sql exception ?

[jimmybow]

Apache Hop version?

2.15.0

Java version?

17

Operating system

Windows

What happened?

I loop run Table input to query multi sql, db is oracle and I found when sql is exception the connection will be not release (close?) and I face IO Error: Got minus one from a read call (reach max connection?) and that hop jvm connot connection to db forever

the problem maybe is in org.apache.hop.core.database.Database.openQuery() no close connection when sql exception ?

Issue Priority

Priority: 2

Issue Component

Component: Database

[hansva]

Normally, the transform should handle the connection. For table input, there is a disconnect in the dispose() method, which is called when the transform fails.

[jimmybow]

I upload the sample hop project in https://drive.google.com/file/d/10qyyMhrVI8C19zPyoQcyvUwUg4egAIum/view?usp=drive_link

the sample project exec 1000 sql and when it exec 300~400 SQL, the error happen

and then hop jvm connot connection to db forever

Error connecting to database [oracle] :org.apache.hop.core.exception.HopDatabaseException: Error occurred while trying to connect to the database

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver) Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

org.apache.hop.core.exception.HopDatabaseException: Error occurred while trying to connect to the database

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver) Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

at org.apache.hop.core.database.Database.normalConnect(Database.java:383) at org.apache.hop.core.database.Database.connect(Database.java:314) at org.apache.hop.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:49) at org.apache.hop.core.database.DatabaseFactory.getConnectionTestResults(DatabaseFactory.java:74) at org.apache.hop.core.database.DatabaseMeta.testConnectionSuccess(DatabaseMeta.java:2113) at org.apache.hop.ui.core.database.DatabaseMetaEditor.testConnection(DatabaseMetaEditor.java:903) at org.apache.hop.ui.core.database.DatabaseMetaEditor.test(DatabaseMetaEditor.java:766) at org.apache.hop.ui.core.database.DatabaseMetaEditor.lambda$createButtonsForButtonBar$6(DatabaseMetaEditor.java:958) at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:91) at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4321) at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1208) at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4119) at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3707) at org.apache.hop.ui.hopgui.HopGui.open(HopGui.java:486) at org.apache.hop.ui.hopgui.HopGui.main(HopGui.java:364) Caused by: org.apache.hop.core.exception.HopDatabaseException: Error connecting to database: (using class oracle.jdbc.driver.OracleDriver) Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

at org.apache.hop.core.database.Database.connectUsingClass(Database.java:478) at org.apache.hop.core.database.Database.normalConnect(Database.java:368) ... 14 more Caused by: java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:854) at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562) at org.apache.hop.core.database.DelegatingDriver.connect(DelegatingDriver.java:45) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191) at org.apache.hop.core.database.Database.connectUsingClass(Database.java:475) ... 15 more Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found

at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:284) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:340) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1596) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588) ... 23 more

Hostname: 172.17.24.72 Port: 1521 Database name: ORCL Url: jdbc:oracle:thin:@172.17.24.72:1521:ORCL

[jimmybow]

the error is Caused by:

Unable to get queryfields for SQL: 
SELECT <values> FROM <table name> WHERE <conditions>

Couldn't get field info from [SELECT <values> FROM <table name> WHERE <conditions>]

ORA-00936: missing expression

https://docs.oracle.com/error-help/db/ora-00936/


	at org.apache.hop.pipeline.transforms.tableinput.TableInputMeta.getFields(TableInputMeta.java:241)
	at org.apache.hop.pipeline.transform.BaseTransformMeta.getFields(BaseTransformMeta.java:328)
	at org.apache.hop.pipeline.PipelineMeta.getThisTransformFields(PipelineMeta.java:1414)
	at org.apache.hop.pipeline.PipelineMeta.getTransformFields(PipelineMeta.java:1222)
	at org.apache.hop.pipeline.PipelineMeta.getTransformFields(PipelineMeta.java:1116)
	at org.apache.hop.pipeline.PipelineMeta.getTransformFields(PipelineMeta.java:1082)
	at org.apache.hop.pipeline.engines.local.LocalPipelineEngine.addTransformExecutionSamplers(LocalPipelineEngine.java:323)
	at org.apache.hop.pipeline.engines.local.LocalPipelineEngine.prepareExecution(LocalPipelineEngine.java:242)
	at org.apache.hop.pipeline.Pipeline.execute(Pipeline.java:528)
	at org.apache.hop.workflow.actions.pipeline.ActionPipeline.execute(ActionPipeline.java:543)
	at org.apache.hop.workflow.Workflow.executeFromStart(Workflow.java:750)
	at org.apache.hop.workflow.Workflow.executeFromStart(Workflow.java:900)
	at org.apache.hop.workflow.Workflow.executeFromStart(Workflow.java:451)
	at org.apache.hop.workflow.Workflow.startExecution(Workflow.java:311)
	at org.apache.hop.workflow.engines.local.LocalWorkflowEngine.startExecution(LocalWorkflowEngine.java:249)
	at org.apache.hop.pipeline.transforms.workflowexecutor.WorkflowExecutor.executeWorkflow(WorkflowExecutor.java:223)
	at org.apache.hop.pipeline.transforms.workflowexecutor.WorkflowExecutor.processRow(WorkflowExecutor.java:177)
	at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:54)
	at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: org.apache.hop.core.exception.HopDatabaseException: 
Couldn't get field info from [SELECT <values> FROM <table name> WHERE <conditions>]

ORA-00936: missing expression

https://docs.oracle.com/error-help/db/ora-00936/

	at org.apache.hop.core.database.Database.getQueryFieldsFallback(Database.java:2327)
	at org.apache.hop.core.database.Database.getQueryFields(Database.java:2153)
	at org.apache.hop.pipeline.transforms.tableinput.TableInputMeta.getFields(TableInputMeta.java:230)
	... 18 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

https://docs.oracle.com/error-help/db/ora-00936/
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:715)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:615)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1315)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:969)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:237)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:508)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:285)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1145)
	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1300)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1225)
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1593)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1443)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3950)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4054)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1113)
	at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeQuery(Unknown Source)
	at org.apache.hop.core.database.Database.getQueryFieldsFallback(Database.java:2304)
	... 20 more
Caused by: Error : 936, Position : 7, SQL = SELECT <values> FROM <table name> WHERE <conditions>, Original SQL = SELECT <values> FROM <table name> WHERE <conditions>, Error Message = ORA-00936: missing expression

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:723)
	... 36 more

[alexmller]

@jimmybow the Oracle Docs say that rate limiting takes place per second. I assume that your test case opens too many connections within a second, so you reach the database-side limit.

The Table input naturally fails, because it contains an invalid SQL statement.

[jimmybow]

the problem is db connection not release and I am not concurrent send SQL ...

[alexmller]

Apologies @jimmybow, you're right. Though the code seemed correct at first sight, I can confirm the idle connection using Postgres:

@hansva: While TableInputMeta.getFields() fails, it properly disconnects from the database but the connection TableInputData.db is not disconnected in this case.

[hansva]

Odd, as we have a db.disconnect() in the finally clause

[alexmller]

It seems to me that the exception inTableInputMeta.getFields() does not escalate to the TableInput.

[jimmybow]

It seems to me that the exception inTableInputMeta.getFields() does not escalate to the TableInput.

yeah, I found another similar issue.
When SQL exception in Table input and the next time run pipeline in the hop GUI, the Data Orchestration view and the metrics monitoring view below do not update in real time. The only place where the result updates in real time can be seen is in the Execution Information View (local-audit) by refresh button (F5). and when pipeline in the hop GUI is finished, the Data Orchestration view and the metrics monitoring view below return to work normally

[alexmller]

This could indeed be related. When you run the failed pipeline again, it does close the idle connection.

[mattcasters]

For really high throughput systems where connections are made and closed very quickly, it's important to remember that there is a period in which a network socket is kept in time_wait state (usually 1 or 2 minutes). Database connection pooling code was removed at some point because it rarely if ever got used and usually in a bad way. However, that doesn't mean that users can't create scenarios in which a situation occurs in which a lot of connections are made and closed in succession. If it's only reading from a database, you could try to run the looping pipeline or workflow in transactional mode forcing the use of a single database connection. See also: https://hop.apache.org//manual/latest/pipeline/pipeline-run-configurations/native-local-pipeline-engine.html