Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

use_browser_authentication fails with "Connection is closed" #225

Closed
larsrinn opened this issue Jul 17, 2024 · 6 comments
Closed

use_browser_authentication fails with "Connection is closed" #225

larsrinn opened this issue Jul 17, 2024 · 6 comments

Comments

@larsrinn
Copy link

I have a pipeline working using username/password authentication. When I switch this to use_browser_authentication: true, it fails. It seems like the failure occurs at the very end. What happens is:

  • The tap is executed and provides data
  • A browser for Snowflake-SSO opens
  • Data seems to be loaded into Snowflake
  • Cleanup is executed
  • The state is updated
  • Failure due to closed connection

Relevant log messages

2024-07-17T11:49:45.428395Z [info     ] 2024-07-17 13:49:45,427 | INFO     | target-snowflake.<stream-name> | Cleaning up <stream-name>
2024-07-17T11:49:45.428644Z [info     ] 2024-07-17 13:49:45,427 | INFO     | target-snowflake     | Emitting completed target state 
2024-07-17T11:49:45.428861Z [info     ] 2024-07-17 13:49:45,428 | INFO     | snowflake.connector.connection | closed
2024-07-17T11:49:45.429008Z [info     ] 2024-07-17 13:49:45,428 | INFO     | snowflake.connector.connection | No async queries seem to be running, deleting session 
2024-07-17T11:49:45.448569Z [info     ] Incremental state has been updated at 2024-07-17 11:49:45.448552.
...
2024-07-17T11:49:45.723670Z [info     ] 2024-07-17 13:49:45,723 | ERROR    | sqlalchemy.pool.impl.QueuePool | Exception during reset or similar
<traceback>
2024-07-17T11:49:45.725255Z [info     ] snowflake.connector.errors.DatabaseError: 250002 (08003): None: Connection is closed
2024-07-17T11:49:45.783624Z [info     ] Block run completed.           block_type=ExtractLoadBlocks err=None set_number=0 success=True
@edgarrmondragon
Copy link
Member

I'm not familiar with how browser authentication is by snowflake-connector-python under the hood, but perhaps we could try to connect early if use_browser_authentication.

@larsrinn
Copy link
Author

Connecting works. Just looking at the logs (I don't know how the target works under the hood), it seems like it's trying to operate on an already closed connection

@edgarrmondragon
Copy link
Member

edgarrmondragon commented Jul 18, 2024 •
edited
Loading

Ah I see. The pipeline seems to have exited cleanly and the error is logged but doesn't crash the target. It might help to look at the debug level logs for snowflake.connector.connection in case there's an operation that it tries to execute after the connection is closed.

https://sdk.meltano.com/en/v0.38.0/implementation/logging.html#logging

This was referenced Sep 16, 2024
Merged
Merged
@dlouseiro
Copy link
Contributor

Ah I see. The pipeline seems to have exited cleanly and the error is logged but doesn't crash the target. It might help to look at the debug level logs for snowflake.connector.connection in case there's an operation that it tries to execute after the connection is closed.

https://sdk.meltano.com/en/v0.38.0/implementation/logging.html#logging

@edgarrmondragon @larsrinn , as I was the one introducing the browser authentication functionality in the connector and was also experiencing this issue myself, I took the liberty of opening a PR to fix it!

@larsrinn
Copy link
Author

Great @dlouseiro. Happy to see it's that easy. Would be great, if this was merged quickly, @edgarrmondragon

edgarrmondragon pushed a commit that referenced this issue Sep 16, 2024
@dlouseiro
fix: Fix "Connection is closed" error when using browser authenticati…
c6c6305 
…on (#257)

The purpose of this PR is to solve the issue reported
[here](#225).

## Root cause:

There was one usage of `engine.connect` outside of a context manager (in
`create_engine`), causing the connector to leave one connection "lying
around".

I did not dive too deep into the internals of the connector nor focused
too much in understanding why is it different for the browser
authentication mechanism, but mostly in ensuring all calls to
`engine.connect()` were done within a context manager, ensuring SQL
alchemy does its job in closing all connections when they should be
closed.

## Implementation details:

- Use a context manager when checking if the database exists (in the
`create_engine` ) method.
@edgarrmondragon
Copy link
Member

Closed by #257

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
MeltanoLabs Overview
Archived in project
Milestone
No milestone
Development

No branches or pull requests
3 participants
@edgarrmondragon @larsrinn @dlouseiro