SQL Error during login

[guyds]

Suddenly I can't login anymore on my Matomo instance.
Whenever I try to login, I get the following SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'login' in 'where clause'

In the Cloudron logs:

Nov 08 10:10:18[Wed Nov 08 09:10:18.623617 2023] [php:notice] [pid 225] [client 172.18.0.1:49142] [<domain-name-redacted>] Error in Matomo: SQLSTATE[42S22]: Column not found: 1054 Unknown column &#039;login&#039; in &#039;where clause&#039;, referer: https://<domain-name-redacted>/index.php
Nov 08 10:10:1894.227.68.6 - - [08/Nov/2023:09:10:18 +0000] "POST /index.php?module=Login HTTP/1.1" 500 1733 "https://<domain-name-redacted>/index.php" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/118.0"
Nov 08 10:10:18172.18.0.1 - - [08/Nov/2023:09:10:18 +0000] "POST /index.php?module=Login HTTP/1.1" 500 2051 "https://<domain-name-redacted>/index.php" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/118.0"
Nov 08 10:10:20172.18.0.1 - - [08/Nov/2023:09:10:20 +0000] "GET / HTTP/1.1" 200 30862 "-" "Mozilla (CloudronHealth)"

I'm not 100% sure, but I think this happens since the latest update to 1.42.0 which includes a migration to OIDC login.

[nebulon]

This looks a bit like the database migration failed for some reason. Can you open a webterminal into the app and run the migration manually via:

sudo -E -u www-data -- /app/code/console core:update --yes

If you need to be up and running quickly, maybe consider restoring the app to the backup before the update.

[girish]

Can you:

1. Revert to old backup
2. Create a Clone from old backup to a new location
3. Try to update the clone. Is it reproducible?

[guyds]

Thanks for the suggestions.
Running the migration manually doesn't have any effect though:

/app/code#  sudo -E -u www-data -- /app/code/console core:update --yes

Everything is already up to date.

I'll try to revert to an older backup or set up a clone from an older backup

[girish]

@guyds yes, please. I think there is some bug in our migration logic. It uses some raw sql to migrate from ldap to oidc , maybe something is wrong there. Our own instance of mamoto migrated fine though

[guyds]

Unfortunately, since I'm using a free version of Cloudron I can only have 2 apps on a single Cloudron and therefore I'm not in the possibility to clone from an older version because I already have another app on that same Cloudron.
And when importing a backup on another Cloudron, the versions of the old and new app should be identical and I can only install the latest version of the app on the new Cloudron, so I would only be able to clone from the latest version.

Or is there a way to clone an older version of the app to another server?

[jdaviescoates]

@guyds said in SQL Error during login:

Or is there a way to clone an older version of the app to another server?

I think there may be ways to install older versions of apps using the Cloudron CLI, but having never actually used it I couldn't tell you how.

[girish]

@guyds mm, indeed that would be problematic. Are you able to send us a mail at support@cloudron.io , we can debug it directly then ?

[guyds]

@girish sure, I just sent an email to support.

[girish]

Been debugging this a bit. The issue is that the brute_force_log table has a missing field in @guyds instance. Maybe a missing migration

mysql> describe brute_force_log;
+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id_brute_force_log | bigint      | NO   | PRI | NULL    | auto_increment |
| ip_address         | varchar(60) | YES  | MUL | NULL    |                |
| attempted_at       | datetime    | NO   |     | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

[girish]

OK, I had to do this:

• Run MySQL command UPDATE option SET option_value = "4.2.0" WHERE option_name = "version_core";
• sudo -E -u www-data -- /app/code/console core:update

Works now. This is document upstream in https://matomo.org/faq/how-to-update/faq_179/

[guyds]

Thanks @girish, I'm glad you found the issue.
Before posting here, I did a search on the error but that didn't bring me to the Matomo FAQ. Probably my search was too specific.

The question now is if/how this can be prevented in the future
For starters it might be a good idea to add this info to the documentation for the Matomo app?

[girish]

@guyds I think this was just a one off error specific to your instance. A migration was not run from 5-6 releases ago. I am not sure why, I just reran the migration.

As for docs, I prefer not to put these "sensitive" things there. I don't want our users to run commands by copy/pasting. This required some debugging before I ran those commands. One has to actually figure which migrations to run by inspecting the code.

[guyds]

@girish, fair enough.
I definitely understand your reasoning, but on the other side there is probably a good reason why the issue was added to the Matomo FAQ