Cannot upgrade from 4.36.9 to 4.37.x

[Northguy]

Version

v4.37.0, v4.36.9

Deployment Method

Docker

Reverse Proxy

Traefik

Reverse Proxy Version

n/a

Description

Tried upgrading from V4.36.9 to V4.37.x, but it seems something breaks in the database conversion between v4.36.9 and v4.37.0

Reproduction

Authelia works without problems on my v4.36.9 install, but crashes on database conversion from v5 to v6 when installing v4.37.0. Switching back to v4.36.9 reverts to a working installation of Authelia

Expectations

Flawless table migration from v5 to v6

Configuration (Authelia)

n/a

Logs (Authelia)

time="2023-02-14T21:53:21+01:00" level=info msg="Storage schema is being checked for updates"
time="2023-02-14T21:53:21+01:00" level=info msg="Storage schema migration from 5 to 6 is being attempted"
**time="2023-02-14T21:53:23+01:00" level=error msg="Failure running the storage provider startup check: error during schema migrate: error applying migration version 6 to version 5 for rollback: schema migration 6 (ConsentPreConfiguration) failed: Error 1051: Unknown table 'Authelia.oauth2_authorization_code_session'. rollback caused by: schema migration 6 (ConsentPreConfiguration) failed: Error 1050: Table 'oauth2_consent_preconfiguration' already exists" stack="github.com/authelia/authelia/v4/internal/commands/root.go:277 doStartupChecks\ngithub.com/authelia/authelia/v4/internal/commands/root.go:87  cmdRootRun\ngithub.com/spf13/cobra@v1.6.0/command.go:920                  (*Command).execute\ngithub.com/spf13/cobra@v1.6.0/command.go:1040                 (*Command).ExecuteC\ngithub.com/spf13/cobra@v1.6.0/command.go:968                  (*Command).Execute\ngithub.com/authelia/authelia/v4/cmd/authelia/main.go:10       main\nruntime/proc.go:250                                           main\nruntime/asm_amd64.s:1594                                      goexit"**
time="2023-02-14T21:53:23+01:00" level=debug msg="LDAP Supported OIDs. Control Types: 2.16.840.1.113730.3.4.9, 1.2.840.113556.1.4.473, 1.3.6.1.4.1.4203.1.9.1.1, 2.16.840.1.113730.3.4.5, 2.16.840.1.113730.3.4.4, 1.3.6.1.4.1.42.2.27.8.5.1, 2.16.840.1.113730.3.4.18, 2.16.840.1.113730.3.4.2, 1.3.6.1.4.1.4203.1.10.1, 1.3.6.1.1.22, 1.2.840.113556.1.4.319, 1.2.826.0.1.3344810.2.3, 1.3.6.1.1.13.2, 1.3.6.1.1.13.1, 1.3.6.1.1.12. Extensions: 1.3.6.1.4.1.1466.20037, 1.3.6.1.4.1.4203.1.11.1, 1.3.6.1.4.1.4203.1.11.3, 1.3.6.1.1.8"
time="2023-02-14T21:53:23+01:00" level=error msg="Your LDAP Server supports TLS but you don't appear to be utilizing it. We strongly recommend using the scheme 'ldaps://' or enabling the StartTLS option to secure connections with your LDAP Server." stack="github.com/authelia/authelia/v4/internal/commands/root.go:319 doStartupCheck\ngithub.com/authelia/authelia/v4/internal/commands/root.go:282 doStartupChecks\ngithub.com/authelia/authelia/v4/internal/commands/root.go:87  cmdRootRun\ngithub.com/spf13/cobra@v1.6.0/command.go:920                  (*Command).execute\ngithub.com/spf13/cobra@v1.6.0/command.go:1040                 (*Command).ExecuteC\ngithub.com/spf13/cobra@v1.6.0/command.go:968                  (*Command).Execute\ngithub.com/authelia/authelia/v4/cmd/authelia/main.go:10       main\nruntime/proc.go:250                                           main\nruntime/asm_amd64.s:1594                                      goexit"

Logs (Proxy / Application)

n/a

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 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

[james-d-elliott]

What happens when you use 4.37.5

[james-d-elliott]

I think it's the collation. For the ones that are not utf8mb4_unicode_520_ci it may be worth trying this following script with the <name> being the table name. MySQL/MariaDB versions were inconsistent with the default collation and we didn't set this previously unfortunately, it was fixed but you may be in a weird edge case. But I can't see why this would cause the upgrade error, just the downgrade error.

ALTER TABLE <name> ENGINE=InnoDB, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

[james-d-elliott]

Not sure what you mean with the completely manual upgrade tasks, but no: I ran my docker-compose.yml against "latest"

It's required by MySQL (prior to v8.0.6 or so) and MariaDB that you run mysql_upgrade manually every time you increase the version number. Some containers may do this for you but a vast majority do not.

[Northguy]

ALTER TABLE <name> ENGINE=InnoDB, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Thanks,

'big' jump from V4.36.9 to V4.37.5 after the collation fix was not possible. Stepwise approach, starting with upgrading from V4.36.9 to V4.37.1 helped in migrating from Schema 5 to Schema 6 and lets me run V4.37.1 and V4.37.2 without problems, but upgrading to V4.37.3 fails again with the following message

time="2023-02-16T12:05:49+01:00" level=info msg="Storage schema is being checked for updates"
time="2023-02-16T12:05:49+01:00" level=info msg="Storage schema migration from 6 to 7 is being attempted"
time="2023-02-16T12:05:49+01:00" level=error msg="Failure running the storage provider startup check: 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" stack="github.com/authelia/authelia/v4/internal/commands/root.go:281 doStartupChecks\ngithub.com/authelia/authelia/v4/internal/commands/root.go:87  cmdRootRun\ngithub.com/spf13/cobra@v1.6.1/command.go:920                  (*Command).execute\ngithub.com/spf13/cobra@v1.6.1/command.go:1044                 (*Command).ExecuteC\ngithub.com/spf13/cobra@v1.6.1/command.go:968                  (*Command).Execute\ngithub.com/authelia/authelia/v4/cmd/authelia/main.go:10       main\nruntime/proc.go:250                                           main\nruntime/asm_amd64.s:1594                                      goexit"

Seems that something is still not ok with my database and seems to relate to #4262. Current table status:

MariaDB [Authelia]> SHOW TABLE STATUS;
+-----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------------------+----------+----------------+---------+------------------+-----------+
| Name                              | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation              | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------------------+----------+----------------+---------+------------------+-----------+
| _bkp_UP_V0002_totp_configurations | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |        16384 |         0 |              9 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| _bkp_UP_V0002_u2f_devices         | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| authentication_logs               | InnoDB |      10 | Dynamic    |  263 |            249 |       65536 |               0 |        32768 |         0 |            264 | 2023-02-16 12:11:58 | 2023-02-16 12:00:20 | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| duo_devices                       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| encryption                        | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |        16384 |         0 |              4 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| identity_verification             | InnoDB |      10 | Dynamic    |   12 |           1365 |       16384 |               0 |        16384 |         0 |             13 | 2023-02-16 12:11:58 | 2023-02-16 11:54:56 | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| migrations                        | InnoDB |      10 | Dynamic    |    6 |           2730 |       16384 |               0 |            0 |         0 |              7 | 2023-02-16 12:11:58 | 2023-02-16 11:36:14 | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_access_token_session       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_authorization_code_session | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_blacklisted_jti            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_consent_preconfiguration   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_consent_session            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_openid_connect_session     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_pkce_request_session       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| oauth2_refresh_token_session      | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        81920 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| totp_configurations               | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |        16384 |         0 |              8 | 2023-02-16 12:11:58 | 2023-02-16 12:00:20 | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| user_opaque_identifier            | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              2 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| user_preferences                  | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |        16384 |         0 |              8 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
| webauthn_devices                  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2023-02-16 12:11:58 | NULL                | NULL       | utf8mb4_unicode_520_ci |     NULL |                |         |                0 | N         |
+-----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------------------+----------+----------------+---------+------------------+-----------+
19 rows in set (0.002 sec)

[james-d-elliott]

Yeah you need to run mysql_upgrade. Your MariaDB's system schemas (sys, mysql, etc) are broken.

[Northguy]

Hey @james-d-elliott got things running. It was indeed a mysql_upgrade which did the job. Ran the upgrade and after that the transition from Schema 6 to Schema 7 went smoothly. Thanks for your support. Learned a lot today.