snowflake-connector-python: Python Connector Randomly Stopped Working - Won't Connect

Please answer these questions before submitting your issue. Thanks!

What version of Python are you using?

Python 3.9.13 (main, Sep 4 2022, 17:18:12) [Clang 13.1.6 (clang-1316.0.21.2.5)]

What operating system and processor architecture are you using?

macOS-13.0.1-arm64-arm-64bit

What are the component versions in the environment (pip freeze)?

asn1crypto==1.5.1
certifi==2022.9.24
cffi==1.15.1
charset-normalizer==2.1.1
cryptography==38.0.3
filelock==3.8.0
idna==3.4
oscrypto==1.3.0
pycparser==2.21
pycryptodomex==3.15.0
PyJWT==2.6.0
pyOpenSSL==22.1.0
pytz==2022.6
requests==2.28.1
snowflake-connector-python==2.8.2
typing_extensions==4.4.0
urllib3==1.26.12

What did you do?

import logging
import os

for logger_name in ['snowflake','botocore']:
	logger = logging.getLogger(logger_name)
	logger.setLevel(logging.DEBUG)
	ch = logging.FileHandler('python_connector.log')
	ch.setLevel(logging.DEBUG)
	ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
	logger.addHandler(ch)

import snowflake.connector
snowflake_ctx = snowflake.connector.connect(
    user='username',
    account='accountname',
    password='password',
)
print(snowflake_ctx)

What did you expect to see?

Expect to connect to the platform. Same code was working for a solid 3 weeks before. Nothign changed on computer.

Can you set logging to DEBUG and collect the logs?

import logging
import os

for logger_name in ('snowflake.connector',):
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
    logger.addHandler(ch)

snowflake.connector.errors.OperationalError: 251011: 251011: ConnectionTimeout occurred. Will be handled by authenticator

During handling of the above exception, another exception occurred:

RecursionError: maximum recursion depth exceeded while calling a Python object

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/network.py", line 1018, in _request_exec
    raw_ret = session.request(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/requests/sessions.py", line 587, in request
    resp = self.send(prep, **send_kwargs)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/requests/sessions.py", line 701, in send
    r = adapter.send(request, **kwargs)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/requests/adapters.py", line 489, in send
    resp = conn.urlopen(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 703, in urlopen
    httplib_response = self._make_request(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 386, in _make_request
    self._validate_conn(conn)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 1042, in _validate_conn
    conn.connect()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/vendored/urllib3/connection.py", line 414, in connect
    self.sock = ssl_wrap_socket(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/ssl_wrap_socket.py", line 78, in ssl_wrap_socket_with_ocsp
    from .ocsp_asn1crypto import SnowflakeOCSPAsn1Crypto as SFOCSP
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 47, in <module>
    from snowflake.connector.ocsp_snowflake import SnowflakeOCSP, generate_cache_key
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/ocsp_snowflake.py", line 81, in <module>
    ] = SFDictFileCache(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 404, in __init__
    self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 213, in _update
    other._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
**** ABOUT 65,000 LINES OF REPEATING TEXT ****
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 482, in _load
    self._update(
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 204, in _update
    self._clear_expired_entries()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 271, in _clear_expired_entries
    self._getitem(k, should_record_hits=False)
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 424, in _getitem
    loaded = self._load_if_should()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 542, in _load_if_should
    return self._load()
  File "/Users/michael/Source/AWS/testproject/venv/lib/python3.9/site-packages/snowflake/connector/cache.py", line 480, in _load
    with open(self.file_path, "rb") as r_file:
RecursionError: maximum recursion depth exceeded while calling a Python object
2022-11-22 18:38:25,096 - MainThread network.py:1152 - _use_requests_session() - DEBUG - Session status for SessionPool 'XXX.snowflakecomputing.com', SessionPool 0/1 active sessions
2022-11-22 18:38:25,096 - MainThread connection.py:1118 - __authenticate() - DEBUG - Continuing authenticator specific timeout handling

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 2
Comments: 30 (1 by maintainers)

Most upvoted comments

The issue was resolved by clearing the snowflake cache directory (/Users/michael/Library/Caches/Snowflake) – I highly recommend that the showflake team build a fix for this into the system. The connection would not fail at all would stay open for hours.

mbwmbw1337 on Nov 22, 2022

For people using ubuntu the cache folder is here: ~/.cache/snowflake

This worked great for me as a temporary solution… I was going crazy spinning up new vms because I thought the linux networking was broken!

voyera on Dec 1, 2022

So glad I found this issue. I thought I was going crazy. New ETL scripts were running fine for a few days and then kept taking down the server once it maxxed out the memory for no good reason.

So far so good with 2.8.3…

bneelon85 on Nov 30, 2022

we’ve found the root cause, it’s a bug in our code that when there’re expired entries in the OCSP cache dict, the code will try to clean up the expired ones, and then reload. However, reload again kicks off the cleaning up procedure, leading to infinite recursion. I had a PR out for fix the issue: #1348 really appreciate sharing us with the error stack which helps us to locate the issue easily. We plan to do a bug fix release very soon. for now please either keep using the workaround you have or downgrade to 2.8.1.

@sfc-gh-aling Hey, Can you share the estimated time for this bug fix release?

Here’s the latest I got… They’re recommending rolling back to the prior version until they’re able to get a release done.

Hello Michael,

Hope you are doing great!

We are releasing the fix for this issue in the next python connector version which is most probably 2.8.3. Until then, we would suggest you use 2.8.1. We will let you know once this version is released so that you can verify the same on your end.

Meanwhile, please let me know if you have any further questions on this support case.

mbwmbw1337 on Nov 28, 2022

For Windows users, I was able to proceed by deleting the corrupted cache files from C:\Users\<respective user>\AppData\Local\Snowflake\Caches folder.

lijoabraham on Nov 28, 2022

Cool. Thanks, @mbwmbw1337. Much Appreciated.

yuvanist on Nov 28, 2022

The issue was resolved by clearing the snowflake cache directory (/Users/michael/Library/Caches/Snowflake) – I highly recommend that the showflake team build a fix for this into the system. The connection would not fail at all would stay open for hours.

mbwmbw1337 - Thank you! Was trying to resolve the issue for 3hrs… Clearing the Cache folder made my day.

Smorasie on Nov 25, 2022

we’ve found the root cause, it’s a bug in our code that when there’re expired entries in the OCSP cache dict, the code will try to clean up the expired ones, and then reload. However, reload again kicks off the cleaning up procedure, leading to infinite recursion.

I had a PR out for fix the issue: https://github.com/snowflakedb/snowflake-connector-python/pull/1348

really appreciate sharing us with the error stack which helps us to locate the issue easily. We plan to do a bug fix release very soon. for now please either keep using the workaround you have or downgrade to 2.8.1.

sfc-gh-aling on Nov 23, 2022

I was developing with intermittent internet, so it seems it may need to be the perfect storm of events to occur. I have tried manually corrupting the JSON files in the cache, but that didn’t seem to do it. I wish I had backed up the infringing files before deleting them. The easiest solution might be if a method occurs on the authentication more than ten (10) times in a row to wipe the cache automagially. Note that this relates to #1325 in a “hard” timeout. I did try adjusting HTTP/login timeouts on the connect method, but they still did not timeout the authentication.

I will keep trying to see if I can corrupt the files to replace this issue.

mbwmbw1337 on Nov 23, 2022

The workaround, for now, is below. I have validated that this only happens in certain instances and the entire connect hangs indefinitely if a file in the cache directory is corrupted. Essentially, I put a hard timeout to flush the cache directory if the connection does not connect. This has resolved the issue.

from retrying import retry
import signal
import glob

    @retry(stop_max_attempt_number=3, wait_exponential_multiplier=500)
    def get_snowflake_connection(self,
                                 user: str,
                                 account: str,
                                 private_key):
        """

        :param username:
        :param endpoint:
        :param private_key:
        :return:
        """
        # noinspection PyPackageRequirements
        import snowflake.connector
        # noinspection PyPackageRequirements
        import snowflake.connector.ocsp_snowflake
        snowflake_cache_directory = \
            snowflake.connector.ocsp_snowflake.OCSPCache.CACHE_DIR
        try:
            signal.signal(signal.SIGALRM, self.timeout_handler)
            signal.alarm(7)
            snowflake_ctx = snowflake.connector.connect(
                user=user,
                account=account,
                private_key=private_key
            )
            return snowflake_ctx
        except TimeoutError:
            print("Snowflake failed to connect. "
                  "This is likely due to a corrupt cache directory. "
                  "Deleting directory and retrying.")
            files = glob.glob(f"{snowflake_cache_directory}/*")
            for f in files:
                os.remove(f)

            raise ConnectionError("Unable to create a connection to Snowflake")
        finally:
            signal.alarm(0)

mbwmbw1337 on Nov 22, 2022