SNOW-752633: Session Keep-alive doesn't work as expected

Tang8330 commented 1 year ago

Issue description

Tell us what should happen and what happens instead

I tried to use the keepSessionAlive flag by setting it to true when I connected to Snowflake. After a few hours of idling, my session ended up getting aborted and it never tried to re-establish a new one.

Example code

If possible, please enter some example code here to reproduce the issue.

https://github.com/artie-labs/transfer/pull/56

Error log

If you have an error log, please paste it here.

Error message in console: 390114: Authentication token has expired. The user must authenticate again.

Configuration

Driver version (or git SHA):

v1.6.13 Dependency + SHA can be viewed here

Go version: run go version in your console

go1.19

mihaitodor commented 1 year ago

I also bumped into this and, as a workaround, I was able to set it in the Params field like so. But yeah, it's really confusing and not sure what's the purpose of KeepSessionAlive... This is a duplicate of #556, which was closed without further explanations

Tang8330 commented 1 year ago

Ah good to know. I had a workaround also: https://github.com/artie-labs/transfer/pull/65

cc: @sfc-gh-wfateem looks like you close https://github.com/snowflakedb/gosnowflake/issues/556.

mihaitodor commented 1 year ago

You're welcome! Looks like there's some overlap between transfer and Benthos. Curious to see what you'll add to it in the future. For BigQuery tests, this emulator might interest you

sfc-gh-dszmolka commented 1 year ago

hi and thank you for raising this issue. as mihaitodor explained, the correct way of implementing the Snowflake 'keep session alive pings' (by default sent every hour, to prevent the authentication token from expiring after 4 hours) is using the client_session_keep_alive connection option.

however when trying to figure out what KeepSessionAlive does then , I found this comment:

KeepSessionAlive bool          // Enables the session to persist even after the connection is closed

and looking further, also found that this capability came ~2 years ago with PR #354 . Apparently there was an internal requirement that we should be able to keep the session open on the Snowflake engine, even when the client closed the connection object. Not sure about the requirement behind that requirement, but hopefully this explains the current situation somewhat.

So if i got the purposes right:

client_session_keep_alive - you'll want to use this connection option to avoid authentication token expire , when the connection is idling e.g. between queries. keepalive 'pings' will be sent by the driver hourly.
KeepSessionAlive - DSN config to avoid closing the session down with Snowflake on driver connection close

Closing this issue now but please if you feel something is still unclear or if an issue is here, please comment and I'll reopen for you.

snowflakedb / gosnowflake