Repository: mamba-org/quetz (language: python)

Issue:
adding two versions of same package with sqlite backend causes segfaults
steps to reproduce:

```
# start quetz without  reload
quetz run test_quetz_test12  --dev --copy-conf dev_config.toml 

export QUETZ_API_KEY=...

curl -X POST "http://localhost:8000/api/channels" \
   -H  "accept: application/json" \
   -H  "Content-Type: application/json" \
   -H  "X-API-Key: ${QUETZ_API_KEY}" \
   -d '{"name":"channel12", "private":false }'
   
 quetz-client http://localhost:8000/api/channels/channel12 quetz/tests/data/test-package-0.1-0.tar.bz2 
 
 quetz-client http://localhost:8000/api/channels/channel12 quetz/tests/data/test-package-0.2-0.tar.bz2 
```

Results = segmentation fault

## Triaging

this might be related that the background tasks in fastapi run in separate threads. When we add files the indexes are updated in a background task using the function `quetz.indexing.update_indexes`: 

https://github.com/mamba-org/quetz/blob/d0b7b2dc331e672c32b9c8bd9917e30b509656c7/quetz/main.py#L728-L729

Running the function directly (in foreground) does not cause the segfault. The problem might be that we are re-using the same database session (attached to `dao` object) in several threads, that is known to cause problems with sqlite. postgres does not seem to have this problem (although reusing db session in mutliple threads still seem evil). 


Discussion / hints:
with postgres the steps above work correctly. we can check that we get the right files:

```
curl "http://localhost:8000/api/chan/channel12/noarch/repodata.json"
```

```
{
  "info": {
    "subdir": "noarch"
  },
  "packages": {},
  "packages.conda": {},
  "repodata_version": 1
}(miniconda3-latest/envs/quetz) [bartosz@lenovo quetz]$ curl "http://localhost:8000/channels/channel12/repodata.json""
curl "http://localhost:8000/channels/channel12/linux-64/repodata.json"
{
  "info": {
    "subdir": "linux-64"
  },
  "packages": {
    "test-package-0.1-0.tar.bz2": {
      "arch": "x86_64",
      "build": "0",
      "build_number": 0,
      "depends": [],
      "license": "BSD",
      "license_family": "BSD",
      "md5": "83db5a378ba1997aa0bb05f60721ffd7",
      "name": "test-package",
      "platform": "linux",
      "sha256": "5e8b0fb9bbaeb7c4a466e38b84eeee7b695d62a9c359bf117978454fdbc68553",
      "size": 2630,
      "subdir": "linux-64",
      "time_modified": 1602143415,
      "timestamp": 1599839787252,
      "version": "0.1"
    },
    "test-package-0.2-0.tar.bz2": {
      "arch": "x86_64",
      "build": "0",
      "build_number": 0,
      "depends": [],
      "license": "BSD",
      "license_family": "BSD",
      "md5": "33107eeed8011e2d8a97a569366ae7ed",
      "name": "test-package",
      "platform": "linux",
      "sha256": "0735a1f0c928646c186f2c6d05193fb9fdf597d94138eb13a0db65551dd9f88e",
      "size": 2692,
      "subdir": "linux-64",
      "time_modified": 1602143444,
      "timestamp": 1599839787300,
      "version": "0.2"
    }
  },
  "packages.conda": {},
  "repodata_version": 1
}
```
sqlite supports thread-safe `SERIALIZED` mode: https://www.sqlite.org/threadsafe.html

but for some reason it's not configurable from python wrappers: https://stackoverflow.com/questions/6296055/serializing-sqlite3-in-python#6313962


the steps in the description do not reproduce the segfault any more, but it can be still triggered by running time after time the index generation:

```
 curl -X PUT localhost:8000/api/channels/channel12/actions -d '{"action":"generate_indexes"}' -H "X-Api-key: ${QUETZ_API_KEY}"
 ```

an easy fix is to switch to subprocess workers instead of default threading

Fix patch:
diff --git a/quetz/deps.py b/quetz/deps.py
index eca36262..366d799a 100644
--- a/quetz/deps.py
+++ b/quetz/deps.py
@@ -2,6 +2,8 @@
 Define common dependencies for fastapi depenendcy-injection system
 """
 
+import logging
+
 import requests
 from fastapi import BackgroundTasks, Depends, HTTPException, Request, status
 from requests.adapters import HTTPAdapter
@@ -23,6 +25,8 @@
 DEFAULT_TIMEOUT = 5  # seconds
 MAX_RETRIES = 3
 
+logger = logging.getLogger("quetz")
+
 
 class TimeoutHTTPAdapter(HTTPAdapter):
     def __init__(self, *args, **kwargs):
@@ -116,6 +120,7 @@ def get_tasks_worker(
     else:
         raise ValueError("wrong configuration in worker.type")
 
+    logger.debug(f"created worker of class {worker.__class__.__name__}")
     return Task(auth, worker)
 
 
diff --git a/quetz/tasks/workers.py b/quetz/tasks/workers.py
index f1723d90..64d7fb2d 100644
--- a/quetz/tasks/workers.py
+++ b/quetz/tasks/workers.py
@@ -112,6 +112,7 @@ def __init__(
 
     def execute(self, func: Callable, *args, **kwargs):
 
+        dialect = self.dao.db.bind.name
         resources = {
             "dao": self.dao,
             "auth": self.auth,
@@ -123,6 +124,22 @@ def execute(self, func: Callable, *args, **kwargs):
         extra_kwargs = prepare_arguments(func, **resources)
         kwargs.update(extra_kwargs)
 
+        # sqlite is not thread safe so we can't reuse
+        # the db connection
+
+        if dialect == 'sqlite':
+            logger.debug("using sqlite backend - create a new connection for thread")
+            self.background_tasks.add_task(
+                job_wrapper,
+                func,
+                self.auth.API_key,
+                self.auth.session,
+                self.config,
+                *args,
+                **kwargs,
+            )
+            return
+
         self.background_tasks.add_task(
             func,
             **kwargs,


Test patch:
diff --git a/quetz/testing/fixtures.py b/quetz/testing/fixtures.py
index fa5efacb..ab0b4c38 100644
--- a/quetz/testing/fixtures.py
+++ b/quetz/testing/fixtures.py
@@ -58,7 +58,7 @@ def use_migrations() -> bool:
         return True
     else:
         raise ValueError(
-            f"QUETZ_TESET_DBINIT should be either {CREATE_TABLES} or {USE_MIGRATIONS}"
+            f"QUETZ_TEST_DBINIT should be either {CREATE_TABLES} or {USE_MIGRATIONS}"
         )
 
 
@@ -116,6 +116,7 @@ def session_maker(sql_connection, create_tables, auto_rollback):
     if auto_rollback:
         trans = sql_connection.begin()
 
+    sql_connection.name = 'sqlite-test'
     yield get_session_maker(sql_connection)
 
     if auto_rollback: