Using NiFi 2.0.0-M4

I am trying to fetch data from mssql database using ExecuteSQL processor through DBCPConnectionPool, queries are very light and should return couple of records with less than second. However there are two type of problems that occur on random (as it seems to me) basis. Process is scheduled to be executed every hour, but:

at certain hours the processor hangs for 16 minutes after it returns data but during this period there’s no trace of connection on database side.

the other case is that I receive ERROR after 16 minutes :

nifi-app_2024-08-12_01.0.log:2024-08-12 01:16:04,397 ERROR [Timer-Driven Process Thread-7] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=2baf5b88-0191-1000-71a1-2ea7fa7a655d] Unable to execute SQL select query [select greatest(AddDate,Modification) as nifi_last_timestamp, * from mfprod_salesuser.Clients where greatest(AddDate,Modification) > '2024-08-11 23:59:59.4267361'] for StandardFlowFileRecord[uuid=38ffa490-bece-4065-b9b6-f9eedb7f9670,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1723417200001-107, container=default, section=107], offset=0, length=54],offset=0,name=nifi_Maczhub_2_AzureStorage_Clients_variables.json,size=54] routing to failure
nifi-app_2024-08-12_01.0.log:2024-08-12 01:16:04,397 ERROR [Timer-Driven Process Thread-14] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=01911010-6605-1bc3-3a54-254b3da0090f] Unable to execute SQL select query [select greatest(AddDate,Modification) as nifi_last_timestamp, * from mfprod_salesuser.TransactionPackageMeals where greatest(AddDate,Modification) > '2024-08-11 23:59:59.9650349'] for StandardFlowFileRecord[uuid=c0385a69-cb35-4d92-9dcb-78a930532535,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1723413605191-106, container=default, section=106], offset=136, length=54],offset=0,name=nifi_Maczhub_2_AzureStorage_TransactionPackageMeals_variables.json,size=54] routing to failure
nifi-app_2024-08-12_02.0.log:2024-08-12 02:16:02,733 ERROR [Timer-Driven Process Thread-16] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=2baf5b88-0191-1000-71a1-2ea7fa7a655d] Unable to execute SQL select query [select greatest(AddDate,Modification) as nifi_last_timestamp, * from mfprod_salesuser.Clients where greatest(AddDate,Modification) > '2024-08-11 23:59:59.4267361'] for StandardFlowFileRecord[uuid=e8c45149-0d2b-434b-b5e5-cf4df6c60a08,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1723417200001-107, container=default, section=107], offset=54, length=54],offset=0,name=nifi_Maczhub_2_AzureStorage_Clients_variables.json,size=54] routing to failure
nifi-app_2024-08-12_02.0.log:2024-08-12 02:16:02,733 ERROR [Timer-Driven Process Thread-3] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=01911010-6605-1bc3-3a54-254b3da0090f] Unable to execute SQL select query [select greatest(AddDate,Modification) as nifi_last_timestamp, * from mfprod_salesuser.TransactionPackageMeals where greatest(AddDate,Modification) > '2024-08-11 23:59:59.9650349'] for StandardFlowFileRecord[uuid=5672c568-3a4f-40ec-b87f-d448a9a85d89,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1723413605191-106, container=default, section=106], offset=190, length=54],offset=0,name=nifi_Maczhub_2_AzureStorage_TransactionPackageMeals_variables.json,size=54] routing to failure
nifi-app_2024-08-12_04.0.log:2024-08-12 04:16:01,457 ERROR [Timer-Driven Process Thread-10] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=2baf5b88-0191-1000-71a1-2ea7fa7a655d] Unable to execute SQL select query [select greatest(AddDate,Modification) as nifi_last_timestamp, * from mfprod_salesuser.Clients where greatest(AddDate,Modification) > '2024-08-12 02:59:57.4172976'] for StandardFlowFileRecord[uuid=c9530e3d-fae7-4108-bac6-0417a750af02,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1723424405151-108, container=default, section=108], offset=133, length=54],offset=0,name=nifi_Maczhub_2_AzureStorage_Clients_variables.json,size=54] routing to failure

I am nifi beginner user and would appriciate any hints where to look for solution.

I’ve found some solutions that other users were having like:

• adding validation query “select 1” in DBDPConnectionPool
• increasing Max Wait Time to 10 seconds in DBCPConnectionPool
• setting Max Total Connections to infinite
  but still the issue appears