Installing SQLAlchemy in Consumption plan

[robertlagrant]

Runtime:

Resource: Azure Function
Type: functionapp,Linux

Libraries:

• Azure Functions version: 1.0.7
• Func version: 2.7.1948

Description

Having sqlalchemy in my requirements.txt produces the following when I build the function app:

• running --build local errors with the following:

ERROR: cannot install SQLAlchemy-1.3.12 dependency: binary dependencies without wheels 
are not supported when building locally. Use the "--build remote" option to build 
dependencies on the Azure Functions build server, or "--build-native-deps" option 
to automatically build and configure the dependencies using a Docker container. More 
information at https://aka.ms/func-python-publish

• running --build remote appears to succeed, but running an endpoint results in a ModuleNotFound: sqlalchemy error in the logs
• running --build-native-deps appears to succeed, but running an endpoint results in a ModuleNotFound: sqlalchemy error in the logs

SQLAlchemy doesn't yet issue wheels, and the build code seems to rely on there being a wheel available.

I think SQLAlchemy does some C compilation when it's built, although I can't actually remember, but according to the error message I get with --build local, either of these options should work. And neither of them errors. But in that case - why isn't SQLAlchemy available to the function app?

[robertlagrant]

I realise the above is a bit of a ramble - here's something more concrete.

1. Make sure bash (including sleep), az and func are installed, and you've run az login with a user who can create things in a subscription.

2. Pop these files in a directory:

infra.tf

provider "azurerm" {}

variable "app_name_base" {
  type = string
}

variable "func_name" {
    type = string
}

resource "azurerm_resource_group" "rg" {
        name = "${var.app_name_base}-rg"
        location = "uksouth"
}

resource "azurerm_storage_account" "storage" {
    name                        = "${var.app_name_base}storage"
    resource_group_name         = azurerm_resource_group.rg.name
    location                    = azurerm_resource_group.rg.location
    account_replication_type    = "LRS"
    account_tier                = "Standard"
}

resource "azurerm_application_insights" "insights" {
  name                = "${var.app_name_base}-insights"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  application_type    = "Web"
}

resource "azurerm_app_service_plan" "appservice" {
  name                = "${var.app_name_base}-appservice"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  kind                = "functionapp"
  reserved            = true

  sku {
    tier = "Dynamic"
    size = "Y1"
  }
}

resource "azurerm_function_app" "func" {
  name                      = "${var.func_name}"
  location                  = azurerm_resource_group.rg.location
  resource_group_name       = azurerm_resource_group.rg.name
  app_service_plan_id       = azurerm_app_service_plan.appservice.id
  storage_connection_string = azurerm_storage_account.storage.primary_connection_string

  identity {
    type = "SystemAssigned"
  }

  app_settings = {
    APPINSIGHTS_INSTRUMENTATIONKEY = azurerm_application_insights.insights.instrumentation_key
  }
}

build_and_deploy.sh (needs to be executable)

#!/bin/bash
set -eux

APP_NAME_BASE=githubiss598
FUNC_NAME=${APP_NAME_BASE}-func
TRIGGER_NAME=i_need_sqlalchemy

# Create Azure infrastructure
terraform init
terraform apply -var="app_name_base=${APP_NAME_BASE}" -var="func_name=${FUNC_NAME}"

# Create function app
func init ${FUNC_NAME} --python
cd ${FUNC_NAME}
echo "" >> requirements.txt
echo "sqlalchemy" >> requirements.txt

# Create HTTP trigger
func new --name ${TRIGGER_NAME} --template "HTTP trigger"
echo "import sqlalchemy" >> ${TRIGGER_NAME}/__init__.py

# Let Azure breathe, poor poppet
sleep 30

# Publish function app
func azure functionapp publish ${FUNC_NAME} --build-native-deps

3. In build_and_deploy.sh, alter APP_NAME to be unique (letters and numbers only - Azure Storage doesn't like punctation) and run ./build_and_deploy.sh - this should work. You'll need to type yes when Terraform asks you to.

4. Then in a browser hit the convenience URL given by the function app deploy's console output - something like https://your-app-name-base-func.azurewebsites.net/api/i_need_sqlalchemy?code=Skxvcmv7TAvbbFVnrDWwuRaOjWos/V3YKePvCxoPGldamkaD0wGZFg== - and you should get a 500

5. Browse to your function app's Monitor page and you should get this - an import error of sqlalchemy:
   

[Hazhzeng]

Hey @robertlagrant,

Thanks for creating this detailed testing scenario and I suggest you take a look into these few things.

1. I tried deploying with the terraform you provided, but the final function app settings is off

• FUNCTIONS_EXTENSION_VERSION should set to "~2"
• FUNCTIONS_WORKER_RUNTIME should set to "python"
• WEBSITE_CONTENTSHARE should be removed
• WEBSITE_CONTENTAZUREFILECONNECTIONSTRING should also be removed

2. We no longer need --build-native-deps when doing func azure functionapp publish. The server will resolve the dependencies for you. The reason I'm guessing you're running into this issue is you have mismatch version (!= 3.7) of python in your local machine. Removing --build-native-deps should solve the issue.

[ghost]

This issue has been automatically marked as stale because it has been marked as requiring author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment.

[robertlagrant]

@msftbot 4 days! It took 16 for the first response from Azure, and that was a list of suggestions, which take time to implement. Please keep open.

[robertlagrant]

@Hazhzeng I've gone through the settings and changed them. I was running in a 3.7 venv.

Removing --build-native-deps gives a 503 response on publish:

$ func azure functionapp publish rob598-func
Getting site publishing info...
Response status code does not indicate success: 503 (Site Unavailable).
$

Adding --build remote instead gives the same opaque error.

Did you get this working with the changes you suggested?

[Hazhzeng]

Hi @robertlagrant,

The 503 error is due to a misconfiguration in your function app.
WEBSITE_CONTENTSHARE and WEBSITE_CONTENTAZUREFILECONNECTIONSTRING should not be set in a Linux Consumption function app. Please remove these two settings.

You can diagnose the function app via Azure Portal -> Function App -> Platform Features -> Diagnose and solve problems -> Availability and Performance (5xx error) -> Function App Down or Reporting Errors.

[robertlagrant]

Hi @Hazhzeng - thanks for the reply. I'd removed those already. However, navigating to where you said got me to an error that said I shouldn't have AzureWebJobsDashboard set with an app insights instrumentation key (why does it set these vars by default - I don't add them in the terraform!?).

Anyway. I removed that, tried another publish, and...same 503 error, as above. Bear in mind the 503 is on publish, not run. It doesn't even get that far.

[Hazhzeng]

Hi @robertlagrant,

This is a TerraForm issue and it is out of our reach. It keeps creating the function app with FUNCTIONS_EXTENSION_VERSION = ~1 (even when I include FUNCTIONS_EXTENSION_VERSION
= "~3" in the app_settings section. We don't support Python language in V1 app and that's why the Function Host fails to start and returning 503.

Could you try using Azure CLI to create the function app:
az functionapp create --resource-group myResourceGroup --consumption-plan-location uksouth --name <APP_NAME> --storage-account <STORAGE_NAME> --runtime python --os-type Linux

[Hazhzeng]

Please raise an issue in the terraform AzureRM repository maintained by hashicorp, and ask them to change FUNCTIONS_EXTENSION_VERSION to ~3, and remove WEBSITE_CONTENTSHARE and WEBSITE_CONTENTAZUREFILECONNECTIONSTRING.
https://github.com/terraform-providers/terraform-provider-azurerm

Feel free to reopen this issue if you have more questions.

[robertlagrant]

For more info - there are two things going on:

1. I need version = "~3" in the function app config
2. I need enable_builtin_logging = false in the function app config
3. There is a bug in the Azure Terraform provider that means incorrect variables are being set: Linux consumption plan apps created using terraform return 503 because of WEBSITE_CONTENT* settings hashicorp/terraform-provider-azurerm#5209

[borancar]

I would like to reopen this as I'm having the following issue now when running the Function (with the correct flags applied by my patch to the terraform provider and confirmed within the App Settings). Note that my requirements.txt includes the required eventhub (azure-eventhub==1.3.*):

Exception while executing function: Functions.SessionWriter <--- Result: Failure Exception: ModuleNotFoundError: No module named 'azure.eventhub'
Stack:
    File "/azure-functions-host/workers/python/3.6/LINUX/X64/azure_functions_worker/dispatcher.py", line 242, in _handle__function_load_request func_request.metadata.entry_point)
    File "/azure-functions-host/workers/python/3.6/LINUX/X64/azure_functions_worker/loader.py", line 66, in load_function mod = importlib.import_module(fullmodname)
    File "/usr/local/lib/python3.6/importlib/__init__.py", line 126, in import_module return _bootstrap._gcd_import(name[level:], package, level)
    File "/home/site/wwwroot/SessionWriter/__init__.py", line 1, in <module> from . import sessionsparser File "/home/site/wwwroot/SessionWriter/sessionsparser.py", line 1, in <module> from . import SessionsWriter
    File "/home/site/wwwroot/SessionWriter/SessionsWriter.py", line 4, in <module> from . import aslogger as logger
    File "/home/site/wwwroot/SessionWriter/aslogger.py", line 2, in <module> from . import EventhubsWriter
    File "/home/site/wwwroot/SessionWriter/EventhubsWriter.py", line 2, in <module> from azure.eventhub import EventHubClient, Receiver, Offset, EventData 

[borancar]

@Hazhzeng, could you help me reopen this please as it doesn't work even with the specified Terraform fixes.

[robertlagrant]

@borancar this sounds a bit defeatist, but we've had terrible luck with Azure Functions - they don't seem ready for primetime.

[balag0]

@borancar Could you share the function app name so we can check if the config looks good.

[borancar]

So, it boils down to Linux Consumption plans not supporting installation from requirements.txt. The package needs to come with all dependencies included. So, you basically have to use func to resolve the dependencies locally and prepare everything. Run:

func azure functionapp publish <....> --build local --build-native-deps

but stop it at:

Running 'docker exec -t 9602cd chmod +x /ziptofs.sh'..done

Then you can zip up the files, making sure to include the generated .python_packages folder as part of your zip package, as that contains all the dependencies.