[
https://issues.apache.org/jira/browse/NIFI-5724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16678656#comment-16678656
]
ASF GitHub Bot commented on NIFI-5724:
--------------------------------------
Github user viswaug commented on a diff in the pull request:
https://github.com/apache/nifi/pull/3113#discussion_r231638762
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/PutSQL.java
---
@@ -134,6 +134,14 @@
.expressionLanguageSupported(ExpressionLanguageScope.FLOWFILE_ATTRIBUTES)
.build();
+ static final PropertyDescriptor AUTO_COMMIT = new
PropertyDescriptor.Builder()
+ .name("database-session-autocommit")
+ .displayName("Database session autocommit value")
+ .description("The autocommit mode to set on the database
connection being used.")
+ .allowableValues("true", "false")
+ .defaultValue("false")
+ .build();
--- End diff --
@ijokarumawak Here is the snowflake documentation that refers to the locks
being held for ever during abruptly disconnected sessions.
https://docs.snowflake.net/manuals/sql-reference/transactions.html#aborting-transactions
I have also confirmed this with the snowflake support ticket and the
resolution suggested was to set the autocommit value to true.
> I did reviewed the query id thanks for providing it. The query is
associated to session id 2392474452590 and from the session id we found that
there was an alter session set autocommit=false was executed (query id for your
reference 10d44ff5-69a4-4d8c-91c3-a206c4a126b8) and after that there was no
commit executed explicitly. This lead to the open transactions in the session
and hence there was a lock created.
> By default autocommit is true and, once all the transaction is completed,
it gets automatically committed. The commit query will not be visible in the
history as this is a background task. For best practices, we recommend to
manually commit the transactions if the session has set to autocommit=false .
> Yes, there is a difference between a session being "closed" versus
"terminated abruptly". Session being "closed" implies to those sessions which
is closed manually after all the transactions is completed . Session being
"terminated abruptly" implies to those sessions which terminates due to various
reason like network issues, system outage ..etc.
I am done making the changes you had requested. I will have a PR out soon.
I just need to hone my git skills to combine these commits and send a PR ...
still figuring that part out ...
> Make the autocommit value in the PutSQL processor configurable
> --------------------------------------------------------------
>
> Key: NIFI-5724
> URL: https://issues.apache.org/jira/browse/NIFI-5724
> Project: Apache NiFi
> Issue Type: Improvement
> Components: Extensions
> Reporter: vish uma
> Priority: Minor
>
> The PutSQL processor currently always sets the autocommit value on the
> database session to false before the SQL statement is run and resets it back
> to the original value after.
> i am not sure if the autocommit value is hardcoded to false for a reason, if
> it is, please let me know.
> This is causing an issue with the snowflake DB where abruptly disconnected
> sessions do not release the locks they have taken.
> i would like to make this autocommit value configurable. I can submit a patch
> for this if there is no objections.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
