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.

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

Insert data in Azure SQL database on a schedule fails using Azure Functions Python V2 bindings due to cold start database #1078

Open
LouisDeconinck opened this issue May 4, 2024 · 1 comment
Labels
enhancement New feature or request

Comments

@LouisDeconinck
Copy link

LouisDeconinck commented May 4, 2024

I have an Azure Function that scrapes a website every day on a daily time trigger, all results are stored in a dictionary. At the end I want to store all of those rows (~10.000) in the Azure SQL database. Usually when I manually run the function after having manually connected to the database everything goes perfectly, but when the scraping happens due to the time trigger I always get this error:
Database 'X' on server 'x.database.windows.net' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '{11B1C15C-BA7C-4BF9-929E-33AE4F11FC72}'.
image001

This sometimes also happens when using the Query editor.
image002

I believe this is caused due to some kind of cold start of Azure SQL database? Now, this would not be a problem if I could implement a proper retry logic, unfortunately I have not succeeded. I have a feeling that r.set(rows_sql) is launched as a background task, and that the try statement does not check if it was successful, which it never is due to the cold start. Is there any way how I can deal with this scenario with the Azure SQL binding Python V2 for Azure functions?

  • Azure Functions SQL Extension or Extension Bundle Version:
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  }
  • Is this a deployed or local function: deployed
  • What type of Database are you using? (Run SELECT @@VERSION as Version, SERVERPROPERTY('EngineEdition') as EngineEdition on your database): Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 3 2024 14:04:26 Copyright (C) 2022 Microsoft Corporation, engine edition 5
  • List any custom settings for your function app. This could be a custom time-out defined specifically for your database server or optional configuration that can be customized for the app defined here:
@app.function_name("AddRow")
@app.schedule(schedule="0 0 7 * * *", arg_name="myTimer", run_on_startup=False, use_monitor=True)
@app.sql_output(arg_name="r",
                command_text="[dbo].[skool_groups]",
                connection_string_setting="AzureWebJobsSqlConnectionString")
def addrow(myTimer: func.TimerRequest, r: func.Out[func.SqlRowList]) -> None:
[...]
        # Convert the list of dictionaries into a SqlRowList
        rows_sql = func.SqlRowList(map(lambda r: func.SqlRow.from_dict(r), rows))

        max_retries = 10
        retries = 0

        while retries < max_retries:
            try:
                r.set(rows_sql)
                
                total_time = time.time() - start_time

                logging.info(f"End of scraping. Scraped {len(rows)} groups in {total_time}.")
                break

            except Exception as e:
                logging.error(f"An error occurred: {str(e)}")
                retries += 1
                if retries < max_retries:
                    logging.info(f"Retrying after 30 seconds... (retry {retries}/{max_retries})")
                    time.sleep(30)
                else:
                    logging.error(f"An error occurred: {str(e)}")

Steps to Reproduce:

  1. Use a daily time trigger to insert data in an Azure SQL database.
  2. Database connection fails the first time (probably due to cold start).
@lucyzhang929 lucyzhang929 added the enhancement New feature or request label May 7, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants