a114383
commented
8 months ago While this seemed to work initially after giving everything time to expire/purge from Snowflake it still was giving me these errors.
Given this was for an initial account setup where I had manually set this same policy, but then wanted to rerun it with schemachange for documentation sake, I decided to go ahead and switch this back to username and password and then use oauth going forward.
This might be related to using the oauth authentication method, but it seems like my GHA runners sometimes run into this issue where by I get the error:
I havn't been able to pin down what triggers this because it seems like if I try to run many new files at once I get this error or if I am testing out a bunch of changes (like dozens over the course of a few hours - maybe token expiration?).
GHA Logs from initial Run
``` Step 2: Running schemachange Debug: $OAUTH_CONFIG = { "token-provider-url": "https://login.microsoftonline.com/XXX/oauth2/v2.0/token", "token-response-name": "access_token", "token-request-headers": { "Content-Type": "application/x-www-form-urlencoded", "User-Agent": "python/schemachange" }, "token-request-payload": { "client_id": "XXX", "username": "user@email.com", "password": "***", "grant_type": "password", "scope": "XXX/session:role-any" } } Debug: $VARIABLES = { "V111__oauth_resource_application_id": "XXX" } Using root folder /opt/actions-runner/_work/temp-dss-schemachange/temp-dss-schemachange/sc_accountadmin Using variables: V111__oauth_resource_application_id: XXX Using Snowflake account progressive-progressive-dev Using default role ACCOUNTADMIN Using default warehouse data_core_developers Using default database admin_db Proceeding with Oauth Access Token authentication SQL query: SELECT CREATED, LAST_ALTERED FROM ADMIN_DB.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = REPLACE('SCHEMACHANGE','"','') AND TABLE_NAME = replace('CHANGE_HISTORY','"','') Using change history table ADMIN_DB.SCHEMACHANGE.CHANGE_HISTORY (last altered 2023-10-12 14:54:26.198000-04:00) SQL query: SELECT VERSION FROM ADMIN_DB.SCHEMACHANGE.CHANGE_HISTORY WHERE SCRIPT_TYPE = 'V' ORDER BY INSTALLED_ON DESC LIMIT 1 SQL query: SELECT DISTINCT SCRIPT, FIRST_VALUE(CHECKSUM) OVER (PARTITION BY SCRIPT ORDER BY INSTALLED_ON DESC) FROM ADMIN_DB.SCHEMACHANGE.CHANGE_HISTORY WHERE SCRIPT_TYPE = 'R' AND STATUS = 'Success' Max applied change script version: None Found Versioned file /opt/actions-runner/_work/temp-dss-schemachange/temp-dss-schemachange/sc_accountadmin/V1.1.1__update_oauth_settings.sql Ignoring non-change file /opt/actions-runner/_work/temp-dss-schemachange/temp-dss-schemachange/sc_accountadmin/schemachange_parameters.json Applying change script V1.1.1__update_oauth_settings.sql SQL query: USE ROLE ACCOUNTADMIN; USE WAREHOUSE data_core_developers; USE DATABASE admin_db; SQL query: ALTER SESSION SET QUERY_TAG = 'schemachange 3.6.0;V1.1.1__update_oauth_settings.sql' SQL query: CREATE OR REPLACE SECURITY INTEGRATION EXTERNAL_OAUTH_AZURE_AD type = external_oauth enabled = true external_oauth_type = azure // app tells snowflake it wants to use OAuth, gets redirected to our tenant (pgr specific) external_oauth_issuer = 'https://sts.windows.net/XXX/' external_oauth_jws_keys_url = ( 'https://login.windows.net/common/discovery/keys', -- PBI setup 'https://login.microsoftonline.com/XXX/discovery/v2.0/keys' ) external_oauth_audience_list = ( 'XXX', --PBI setup, using $VARIABLES 'https://analysis.windows.net/powerbi/connector/Snowflake' ) external_oauth_token_user_mapping_claim = 'upn' external_oauth_snowflake_user_mapping_attribute = 'email_address' external_oauth_any_role_mode = 'ENABLE' Traceback (most recent call last): File "/home/ztbld02p/.local/lib/python3.9/site-packages/schemachange/cli.py", line 360, in execute_snowflake_query self.con.commit() File "/home/ztbld02p/.local/lib/python3.9/site-packages/snowflake/connector/connection.py", line [72](https://github.com/PCDST/temp-dss-schemachange/actions/runs/6500411677/job/17655734973#step:4:73)1, in commit self.cursor().execute("COMMIT") File "/home/ztbld02p/.local/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 908, in execute Error.errorhandler_wrapper(self.connection, self, error_class, errvalue) File "/home/ztbld02p/.local/lib/python3.9/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper handed_over = Error.hand_to_other_handler( File "/home/ztbld02p/.local/lib/python3.9/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler cursor.errorhandler(connection, cursor, error_class, error_value) File "/home/ztbld02p/.local/lib/python3.9/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler raise error_class( snowflake.connector.errors.ProgrammingError: 390111: Session no longer exists. New login required to access the service. During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/usr/lib64/python3.9/runpy.py", line 197, in _run_module_as_main return _run_code(code, main_globals, None, File "/usr/lib64/python3.9/runpy.py", line 87, in _run_code exec(code, run_globals) File "/home/ztbld02p/.local/lib/python3.9/site-packages/schemachange/cli.py", line 891, inI tried running schemachange with
--verbosebut I don't see anything other than what's here that would be useful. If anyone has ideas on how I can debug more i'd appreciate it.