Authelia fails to run migrations on empty database

[m00nwtchr]

Version

v4.38.8

Deployment Method

Docker

Reverse Proxy

Traefik

Reverse Proxy Version

No response

Description

After updating authelia, I received an error about running one of the migrations (11 to 15, iirc), so since I don't have anything important in the authelia DB, (only one totp authenticator set up for one user), i just dropped all tables in the DB (only removing the duplicate index from the webauthn table didn't work, as it was constantly re-created). But now, with an empty DB, I'm getting the below error.

Reproduction

I'm using this ansible role to set-up my docker containers: (create_db sets up a database in a mariadb server which is itself started in another role, there is also one for the redis container)

---
- ansible.builtin.import_tasks: create_db.yml
  vars:
    db_name: authelia

- name: Ensures authelia dir exists
  ansible.builtin.file:
    path: "{{ container_dir }}/authelia"
    state: directory
    owner: "{{ authelia_uid + userns_remap_offset }}"
    group: "{{ authelia_uid + userns_remap_offset }}"
    mode: 0500

- name: Adding configuration.toml file
  ansible.builtin.template:
    src: configuration.yml
    dest: "{{ container_dir }}/authelia/configuration.yml"
    owner: "{{ authelia_uid + userns_remap_offset }}"
    group: "{{ authelia_uid + userns_remap_offset }}"
    mode: 0400
  register: authelia_config

- name: Create the authelia container
  community.docker.docker_container:
    name: authelia
    image: authelia/authelia
    pull: "{{ docker_update | default(false) }}"
    restart_policy: unless-stopped
    restart: "{{ authelia_config.changed | default(false) }}"
    user: "{{ authelia_uid }}:{{ authelia_uid }}"
    networks:
      - name: web
      - name: db
    volumes:
      - "{{ container_dir }}/authelia/configuration.yml:/config/configuration.yml:ro"
    labels:
      traefik.enable: "true"
      traefik.http.routers.authelia.rule: Host(`auth.{{ domain }}`)
      traefik.http.routers.authelia.entryPoints: web-secure
      traefik.http.services.authelia.loadbalancer.server.port: "9091"
      ## Auth Middleware
      traefik.http.middlewares.auth.forwardAuth.address: http://authelia:9091/api/verify?rd=https%3A%2F%2Fauth.{{ domain }}%2F
      traefik.http.middlewares.auth.forwardAuth.trustForwardHeader: "true"
      traefik.http.middlewares.auth.forwardAuth.authResponseHeaders: Remote-User,Remote-Groups,Remote-Name,Remote-Email
      traefik.http.middlewares.auth-basic.forwardAuth.address: http://authelia:9091/api/verify?auth=basic
      traefik.http.middlewares.auth-basic.forwardAuth.trustForwardHeader: "true"
      traefik.http.middlewares.auth-basic.forwardAuth.authResponseHeaders: Remote-User,Remote-Groups,Remote-Name,Remote-Email
    log_driver: "{{ log_driver }}"
    log_options: "{{ log_options }}"

Expectations

No response

Configuration (Authelia)

theme: auto
log:
  level: debug
telemetry:
  metrics:
    enabled: false
totp:
  disable: false
  issuer: '{{ domain }}'
webauthn:
  disable: false
identity_validation:
  reset_password:
    jwt_secret: '{{ authelia_jwt_secret }}'
authentication_backend:
  password_reset:
    disable: false
  ldap:
    address: 'ldap://lldap:3890'
    implementation: lldap
    base_dn: dc=ldap
    additional_users_dn: ou=users
    users_filter: >-
      (&(|({username_attribute}={input})({mail_attribute}={input}))(objectClass=person))
    additional_groups_dn: ou=groups
    groups_filter: '(member={dn})'
    user: 'cn={{ ldap_bind_user }},ou=people,dc=ldap'
    password: '{{ ldap_bind_password }}'
password_policy:
  standard:
    enabled: false
    min_length: 8
    max_length: 0
    require_uppercase: true
    require_lowercase: true
    require_number: true
    require_special: true
  zxcvbn:
    enabled: true
    min_score: 3
privacy_policy:
  enabled: false
  require_user_acceptance: false
  policy_url: ''
access_control:
  default_policy: deny
  networks:
    - name: internal
      networks:
        - 192.168.0.0/24
    - name: VPN
      networks:
        - 172.16.0.0/24
        - 'fd9f:9a66:67c6::/64'
  rules:
    - domain: '{{ domain }}'
      policy: two_factor
    - domain_regex: ^.*\.m00nlit\.dev$
      subject: 'group:admins'
      policy: two_factor
    - domain: 'qbit-basic.{{ domain }}'
      subject: 'user:qbit'
      policy: one_factor
    - domain:
        - 'jellyfin.{{ domain }}'
        - 'photos.{{ domain }}'
      policy: one_factor
      subject: 'group:users'
session:
  secret: '{{ authelia_session_secret }}'
  cookies:
    - domain: '{{ domain }}'
      authelia_url: 'https://auth.{{ domain }}'
  name: authelia_session
  same_site: lax
  inactivity: 5m
  expiration: 1h
  remember_me: 1M
  redis:
    host: redis
    port: 6379
    database_index: 0
storage:
  encryption_key: '{{ authelia_storage_encryption_key }}'
  mysql:
    address: 'tcp://mariadb:3306'
    database: authelia
    username: authelia
    password: '{{ authelia_db_password }}'
notifier:
  disable_startup_check: true
  filesystem:
    filename: /tmp/notification.txt
identity_providers:
  oidc:
    hmac_secret: '{{ authelia_hmac_secret }}'
    jwks:
      - algorithm: RS256
        use: sig
        key: |
          {% for line in authelia_issuer_private_key.split('\n') %}
          {{ line }}
          {% endfor %}
    cors:
      allowed_origins_from_client_redirect_uris: true

Build Information

Last Tag: v4.38.8
State: tagged clean
Branch: v4.38.8
Commit: cd32d5ce0b3cc2d581f63700c5cec1174c027718
Build Number: 28628
Build OS: linux
Build Arch: amd64
Build Compiler: gc
Build Date: Mon, 15 Apr 2024 13:44:28 +1000
Extra:

Go:
    Version: go1.22.1
    Module Path: github.com/authelia/authelia/v4
    Executable Path: github.com/authelia/authelia/v4/cmd/authelia

Logs (Authelia)

time="2024-05-19T00:39:44Z" level=debug msg="Loaded Configuration Sources" files="[/config/configuration.yml]" filters="[]"
time="2024-05-19T00:39:44Z" level=debug msg="Logging Initialized" fields.level=debug file= format= keep_stdout=false
time="2024-05-19T00:39:44Z" level=debug msg="Process user information" gid=1000 uid=1000
time="2024-05-19T00:39:44Z" level=warning msg="Configuration: identity_providers: oidc: clients: client 'gitea': option 'client_secret' is plaintext but for clients not using the 'token_endpoint_auth_method' of 'client_secret_jwt' it should be a hashed value as plaintext values are deprecated with the exception of 'client_secret_jwt' and will be removed in the near future"
time="2024-05-19T00:39:44Z" level=info msg="Authelia v4.38.8 is starting"
time="2024-05-19T00:39:44Z" level=info msg="Log severity set to debug"
time="2024-05-19T00:39:44Z" level=debug msg="Registering client gitea with policy one_factor (one_factor)"
time="2024-05-19T00:39:44Z" level=info msg="Storage schema is being checked for updates"
time="2024-05-19T00:39:44Z" level=info msg="Storage schema migration from 0 to 15 is being attempted"
time="2024-05-19T00:39:44Z" level=debug msg="Storage schema migrated from version 0 to 1"
time="2024-05-19T00:39:44Z" level=debug msg="Storage schema migrated from version 1 to 2"
time="2024-05-19T00:39:44Z" level=debug msg="Storage schema migrated from version 2 to 3"
time="2024-05-19T00:39:45Z" level=debug msg="Storage schema migrated from version 3 to 4"
time="2024-05-19T00:39:45Z" level=debug msg="Storage schema migrated from version 4 to 5"
time="2024-05-19T00:39:46Z" level=debug msg="Storage schema migrated from version 5 to 6"
time="2024-05-19T00:39:46Z" level=error msg="Error occurred running a startup check" error="error during schema migrate: error applying migration version 7 to version 6 for rollback: schema migration 7 (ConsistencyFixes) failed: Error 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted. rollback caused by: schema migration 7 (ConsistencyFixes) failed: Error 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted" provider=storage stack="github.com/authelia/authelia/v4/internal/commands/root.go:108 doStartupChecks\ngithub.com/authelia/authelia/v4/internal/commands/root.go:88  (*CmdCtx).RootRunE\ngithub.com/spf13/cobra@v1.8.0/command.go:983                  (*Command).execute\ngithub.com/spf13/cobra@v1.8.0/command.go:1115                 (*Command).ExecuteC\ngithub.com/spf13/cobra@v1.8.0/command.go:1039                 (*Command).Execute\ngithub.com/authelia/authelia/v4/cmd/authelia/main.go:10       main\nruntime/internal/atomic/types.go:194                          (*Uint32).Load\nruntime/asm_amd64.s:1695                                      goexit"
time="2024-05-19T00:39:46Z" level=debug msg="LDAP Supported OIDs. Control Types: none. Extensions: 1.3.6.1.4.1.4203.1.11.1"
time="2024-05-19T00:39:46Z" level=debug msg="notification provider: startup check skipped as it is disabled"
time="2024-05-19T00:39:46Z" level=fatal msg="One or more providers had fatal failures performing startup checks, for more detail check the error level logs" providers="[storage]" stack="github.com/authelia/authelia/v4/internal/commands/root.go:150 doStartupChecks\ngithub.com/authelia/authelia/v4/internal/commands/root.go:88  (*CmdCtx).RootRunE\ngithub.com/spf13/cobra@v1.8.0/command.go:983                  (*Command).execute\ngithub.com/spf13/cobra@v1.8.0/command.go:1115                 (*Command).ExecuteC\ngithub.com/spf13/cobra@v1.8.0/command.go:1039                 (*Command).Execute\ngithub.com/authelia/authelia/v4/cmd/authelia/main.go:10       main\nruntime/internal/atomic/types.go:194

Logs (Proxy / Application)

No response

Documentation

No response

Pre-Submission Checklist

• I agree to follow the Code of Conduct

• This is a bug report and not a support request

• I have read the security policy and this bug report is not a security issue or security related issue

• I have either included the complete configuration file or I am sure it's unrelated to the configuration

• I have either included the complete debug / trace logs or the output of the build-info command if the logs are not relevant

• I have provided all of the required information in full with the only alteration being reasonable sanitization in accordance with the Troubleshooting Sanitization reference guide

• I have checked for related proxy or application logs and included them if available

• I have checked for related issues and checked the documentation

[glime-ai]

Please note this response was created using generative AI leveraging previously available information from the repository. This information may be helpful however it is our strong recommendation that you carefully consider the information before blindly applying it.

---

Based on the detailed logs and configuration you provided, it appears that the error you're encountering during the Authelia startup is related to a failed database migration from schema version 6 to 7. The specific error message indicates a problem with the MySQL database: "Error 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted."

This error suggests that there might be corruption in the MySQL system tables, specifically mysql.proc, which is used to store stored procedures and functions. Here are a few steps you can take to resolve this issue:

1. Check and Repair MySQL Tables: You can use the MySQL CHECK TABLE and REPAIR TABLE commands to check for corruption and attempt to repair it. For the mysql.proc table, you can run:

   CHECK TABLE mysql.proc;
   REPAIR TABLE mysql.proc;

2. Restore from Backup: If you have a backup of your MySQL database, consider restoring from the backup before the corruption occurred.

3. Reinitialize the Database: Since you mentioned that you don't have anything important in the Authelia database and already tried dropping all tables, you might consider reinitializing the database entirely. Make sure to drop the entire database and recreate it, rather than just dropping the tables, to ensure all system tables are correctly recreated.

4. Upgrade MySQL: If you're running an older version of MySQL, consider upgrading to a newer version. Sometimes, bugs related to system table corruption are resolved in newer versions.

5. Check MySQL Logs: Look into the MySQL error logs for any additional messages that might give more insight into what caused the corruption and how to prevent it in the future.

6. Re-run Authelia with a Clean Setup: After addressing the database issues, ensure that your Authelia configuration is correct and try running Authelia again. Make sure that the database connection settings in your configuration.yml are correct and that Authelia has the necessary permissions to access and modify the database.

If the problem persists after trying these steps, you might need to delve deeper into the specific configurations of your MySQL server or consider getting professional support for MySQL.

This comment was generated by Glime.

[m00nwtchr]

Tried 1. and 3., don't have a backup for this particular database, and I'm already running latest mariadb. (recently upgraded, if that could cause any issues)

[james-d-elliott]

Performing mysql_upgrade is not optional with MySQL/MariaDB when changing versions, it must be performed every time you upgrade it, as per their documentation. This is not a bug, it's a corrupt MySQL/MariaDB installation. See our docs for more information on why this isn't supported.