Azure / Azure/azure-functions-sql-extension

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

Open
#1,078 2 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
C#
Stars
130
Forks
71
Avg merge
4d 8h
Merged PRs (30d)
4

Description

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](https://github.com/Azure/azure-functions-sql-extension/assets/27673665/262e8d31-d513-4232-a69c-ee64cd83a69c)

This sometimes also happens when using the Query editor.
![image002](https://github.com/Azure/azure-functions-sql-extension/assets/27673665/7467ccba-867b-4496-bf61-72e606ac9fa4)

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:
```json
"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](https://learn.microsoft.com/azure/azure-functions/functions-bindings-azure-sql-trigger?tabs=isolated-process%2Cportal&pivots=programming-language-csharp#optional-configuration):
```python
@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).

Contributor guide

Open the contributing guide

Research direction

Start at the Python V2 timer entry point `addrow` and the `r.set(rows_sql)` SQL output binding; reproduce the daily-trigger path and compare it with the successful manual invocation. Check whether binding failures are observable from `r.set` and use the Azure SQL cold-start error to define expected retry behavior; done means the trigger path reliably inserts the rows or documents the supported handling.

Written by the indexing model from the issue text.

Assessment

Tech stack
azure, python, sql
Domain
backend, cloud, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.