[BUG] Upgrade to 1.5.12 from 1.5.9 fails (Cannot delete or update a parent row: a foreign key constraint fails)

[ANBAL534]

What happened?

When upgrading, a k8s deployment of bunkerweb, from version 1.5.9 to the latest 1.5.12, when the db pod restarted, it fails to apply the migrations with this error: Cannot delete or update a parent row: a foreign key constraint fails [SQL: DROP TABLE bw_plugins_error]

How to reproduce?

Have a k8s deployment of bunkerweb running wil some ingresses on version 1.5.9, change deployments to 1.5.12 and apply again the yaml file to upgrade.

Configuration file(s) (yaml or .env)

apiVersion: v1
kind: Namespace
metadata:
  name: bunkerweb
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: cr-bunkerweb
  namespace: bunkerweb
rules:
  - apiGroups: [""]
    resources: ["services", "pods", "configmaps", "secrets"]
    verbs: ["get", "watch", "list"]
  - apiGroups: ["networking.k8s.io"]
    resources: ["ingresses"]
    verbs: ["get", "watch", "list"]
---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: sa-bunkerweb
  namespace: bunkerweb
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: crb-bunkerweb
  namespace: bunkerweb
subjects:
  - kind: ServiceAccount
    name: sa-bunkerweb
    namespace: bunkerweb
    apiGroup: ""
roleRef:
  kind: ClusterRole
  name: cr-bunkerweb
  apiGroup: rbac.authorization.k8s.io
---
apiVersion: apps/v1
kind: DaemonSet
metadata:
  name: bunkerweb
  namespace: bunkerweb
spec:
  selector:
    matchLabels:
      app: bunkerweb
  template:
    metadata:
      labels:
        app: bunkerweb
      # mandatory annotation
      annotations:
        bunkerweb.io/INSTANCE: "yes"
    spec:
      containers:
        # using bunkerweb as name is mandatory
        - name: bunkerweb
          image: bunkerity/bunkerweb:1.5.12
          imagePullPolicy: Always
          securityContext:
            runAsUser: 101
            runAsGroup: 101
            allowPrivilegeEscalation: false
            capabilities:
              drop:
                - ALL
          ports:
            - containerPort: 8080
              hostPort: 80
            - containerPort: 8443
              hostPort: 443
          env:
            - name: KUBERNETES_MODE
              value: "yes"
            # replace with your DNS resolvers
            # e.g. : coredns.kube-system.svc.cluster.local
            - name: DNS_RESOLVERS
              value: "kube-dns.kube-system.svc.cluster.local"
            - name: USE_API
              value: "yes"
            # 10.0.0.0/8 is the cluster internal subnet
            - name: API_WHITELIST_IP
              value: "127.0.0.0/8 10.0.0.0/8"
            - name: WHITELIST_IP
              value: "127.0.0.0/8 10.0.0.0/8"
            - name: SERVER_NAME
              value: ""
            - name: MULTISITE
              value: "yes"
            - name: USE_REDIS
              value: "yes"
            - name: USE_REVERSE_PROXY
              value: "yes"
            - name: REDIS_HOST
              value: "svc-bunkerweb-redis.bunkerweb.svc.cluster.local"
            - name: BLACKLIST_COUNTRY
              value: "CN CZ RU IN"
            - name: EMAIL_LETS_ENCRYPT
              value: "[email protected]"
            - name: USE_BUNKERNET
              value: "no"
            - name: INTERCEPTED_ERROR_CODES
              value: "401 403 405 413 429 500 501 502 503 504"
            - name: USE_LIMIT_CONN
              value: "no"
            - name: ALLOWED_METHODS
              value: "GET|POST|HEAD|OPTIONS|PATCH|PUT|DELETE"
          livenessProbe:
            exec:
              command:
                - /usr/share/bunkerweb/helpers/healthcheck.sh
            initialDelaySeconds: 30
            periodSeconds: 5
            timeoutSeconds: 1
            failureThreshold: 3
          readinessProbe:
            exec:
              command:
                - /usr/share/bunkerweb/helpers/healthcheck.sh
            initialDelaySeconds: 30
            periodSeconds: 1
            timeoutSeconds: 1
            failureThreshold: 3
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: bunkerweb-controller
  namespace: bunkerweb
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: bunkerweb-controller
  template:
    metadata:
      labels:
        app: bunkerweb-controller
    spec:
      serviceAccountName: sa-bunkerweb
      containers:
        - name: bunkerweb-controller
          image: bunkerity/bunkerweb-autoconf:1.5.12
          imagePullPolicy: Always
          env:
            - name: KUBERNETES_MODE
              value: "yes"
            - name: "DATABASE_URI"
              value: "mariadb+pymysql://bunkerweb:Redacted1234@svc-bunkerweb-db:3306/db"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: bunkerweb-scheduler
  namespace: bunkerweb
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: bunkerweb-scheduler
  template:
    metadata:
      labels:
        app: bunkerweb-scheduler
    spec:
      serviceAccountName: sa-bunkerweb
      containers:
        - name: bunkerweb-scheduler
          image: bunkerity/bunkerweb-scheduler:1.5.12
          imagePullPolicy: Always
          env:
            - name: KUBERNETES_MODE
              value: "yes"
            - name: "DATABASE_URI"
              value: "mariadb+pymysql://bunkerweb:Redacted1234@svc-bunkerweb-db:3306/db"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: bunkerweb-redis
  namespace: bunkerweb
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: bunkerweb-redis
  template:
    metadata:
      labels:
        app: bunkerweb-redis
    spec:
      containers:
        - name: bunkerweb-redis
          image: redis:7-alpine
          imagePullPolicy: Always
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: bunkerweb-db
  namespace: bunkerweb
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: bunkerweb-db
  template:
    metadata:
      labels:
        app: bunkerweb-db
    spec:
      containers:
        - name: bunkerweb-db
          image: mariadb:10.10
          imagePullPolicy: Always
          env:
            - name: MYSQL_RANDOM_ROOT_PASSWORD
              value: "yes"
            - name: "MYSQL_DATABASE"
              value: "db"
            - name: "MYSQL_USER"
              value: "bunkerweb"
            - name: "MYSQL_PASSWORD"
              value: "Redacted1234"
          volumeMounts:
            - mountPath: "/var/lib/mysql"
              name: vol-db
      volumes:
        - name: vol-db
          persistentVolumeClaim:
            claimName: pvc-bunkerweb
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: bunkerweb-ui
  namespace: bunkerweb
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: bunkerweb-ui
  template:
    metadata:
      labels:
        app: bunkerweb-ui
    spec:
      serviceAccountName: sa-bunkerweb
      containers:
        - name: bunkerweb-ui
          image: bunkerity/bunkerweb-ui:1.5.12
          imagePullPolicy: Always
          env:
            - name: KUBERNETES_MODE
              value: "yes"
            - name: "DATABASE_URI"
              value: "mariadb+pymysql://bunkerweb:Redacted1234@svc-bunkerweb-db:3306/db"
---
apiVersion: v1
kind: Service
metadata:
  name: svc-bunkerweb
  namespace: bunkerweb
spec:
  clusterIP: None
  selector:
    app: bunkerweb
---
apiVersion: v1
kind: Service
metadata:
  name: svc-bunkerweb-db
  namespace: bunkerweb
spec:
  type: ClusterIP
  selector:
    app: bunkerweb-db
  ports:
    - name: sql
      protocol: TCP
      port: 3306
      targetPort: 3306
---
apiVersion: v1
kind: Service
metadata:
  name: svc-bunkerweb-redis
  namespace: bunkerweb
spec:
  type: ClusterIP
  selector:
    app: bunkerweb-redis
  ports:
    - name: redis
      protocol: TCP
      port: 6379
      targetPort: 6379
---
#apiVersion: v1
#kind: Service
#metadata:
#  name: bunkerweb-ui-service
#  namespace: bunkerweb
#spec:
#  ports:
#    - name: http
#      targetPort: 7000
#      port: 7000
#  selector:
#    app: bunkerweb-ui
#---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: pvc-bunkerweb
  namespace: bunkerweb
spec:
#  storageClassName: longhorn-fast
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi

Relevant log output

[2024-12-15 17:33:59] - ENTRYPOINT - ℹ️ - Starting the job scheduler v1.5.12 ...
[2024-12-15 17:33:59] - ENTRYPOINT - ℹ️ - Setup and check /data folder ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Save config started ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Settings : /usr/share/bunkerweb/settings.json
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Core : /usr/share/bunkerweb/core
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Plugins : /etc/bunkerweb/plugins
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Pro plugins : /etc/bunkerweb/pro/plugins
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Init : True
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Detected Kubernetes integration
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Checking arguments ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Variables : /tmp/variables.env
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Computing config ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [⚠️ ] - Ignoring variable KUBERNETES_PORT : variable name KUBERNETES_PORT doesn't exist
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - ✅ Database connection established
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [ℹ️ ] - Database is already initialized, checking for changes ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [⚠️ ] - Database version (error) is different from Bunkerweb version (1.5.12), migrating ...
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [⚠️ ] - Table "bw_cli_commands" is missing, creating it
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [⚠️ ] - Table "bw_plugins" already exists, dropping it to make room for the new one
/usr/share/bunkerweb/db/Database.py:274: SAWarning: transaction already deassociated from connection
  session.rollback()
[2024-12-15 17:34:02 +0000] [GENERATOR] [159] [❌] - Exception while executing config saver : Traceback (most recent call last):
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/share/bunkerweb/deps/python/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/usr/share/bunkerweb/deps/python/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/share/bunkerweb/deps/python/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/share/bunkerweb/gen/save_config.py", line 302, in <module>
    ret, err = db.init_tables(
               ^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/db/Database.py", line 699, in init_tables
    session.execute(text(f"DROP TABLE {table_name}_{db_version_id}"))
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/orm/scoping.py", line 778, in execute
    return self._proxied.execute(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/orm/session.py", line 2362, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/orm/session.py", line 2256, in _execute_internal
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/share/bunkerweb/deps/python/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/share/bunkerweb/deps/python/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/bunkerweb/deps/python/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/usr/share/bunkerweb/deps/python/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/share/bunkerweb/deps/python/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails')
[SQL: DROP TABLE bw_plugins_error]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

[2024-12-15 17:34:02] - ENTRYPOINT - ❌ - Scheduler generator failed

BunkerWeb version

1.5.12

What integration are you using?

Kubernetes

Linux distribution (if applicable)

Almalinux 9.3

Removed private data

• I have removed all private data from the configuration file and the logs

Code of Conduct

• I agree to follow this project's Code of Conduct

[thelittlefireman]

Seems similar to #1725