user_status/api results in a massive rise of 404 requests

[markuman]

Steps to reproduce

1. update from NC 19 to NC 20
2. login only one user
3. request user status of all other users
4. see the rise of 404 response

E.g. we see here in 3 hours > 1000 404 requests.

/....de f8795923dd7595f43bebc04383fa31a6d2ca2c594a3ecf3432ee4b39665a0270 1.2.3.4 - - [14/Oct/2020:14:38:43 +0200] "GET /ocs/v2.php/apps/user_status/api/v1/statuses/someuser.....de HTTP/1.1" 404 806 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:80.0) Gecko/20100101 Firefox/80.0"

MariaDB [nextcloud]> select * from oc_user_status where user_id like 'someuser%';
Empty set (0.00 sec)

MariaDB [nextcloud]> select count(*) from oc_users;
+----------+
| count(*) |
+----------+
|      258 |
+----------+
1 row in set (0.00 sec)

MariaDB [nextcloud]> select count(*) from oc_user_status;
+----------+
| count(*) |
+----------+
|       65 |
+----------+
1 row in set (0.00 sec)

MariaDB [nextcloud]> 

Once you're logged in, a status is set.
Maybe the server should not response 404, but 200 with a user_status of "undefined" or something like that.

Otherwise when you're monitoring your 4xx requests, you need to preprocess it before throwing alerts.

[markuman]

INSERT
    INTO
    oc_user_status (user_id, status, status_timestamp, is_user_defined)
SELECT
    uid AS user_id,
    'Offline' AS status,
    configvalue AS status_timestamp,
    0 AS is_user_defined
FROM
    oc_users
INNER JOIN oc_preferences ON
    (uid = userid)
WHERE
    uid NOT IN (
    SELECT
        user_id
    FROM
        oc_user_status)
    AND configkey = 'lastLogin';

This query needs to be executed...and is maybe missed.

[nishantbb]

Interested in potential resolution

[markuman]

DIY resolution is as mentioned above. Insert status = 'Offline' for all users that has no status set yet.
And imho this is the only solution. There is no other.

I guess we won't see any upstream fix, because this will happen only when you update from nextcloud < 20 to nextcloud >= 20 and from time to time (the more users logged in) the rise of 404 error will shrink.
All setups that start already with nextcloud >= 20 won't run into this issue.

I can think of two options for a permanent fix.

The table design not ideal here. The user status should be part of the oc_users table itself with a default value if not set, instead a separate table. But I guess this is not an option anymore.

Another permanent fix can be a view, which returns status = 'Offline' if not set. The usage and request of the view must be patched in the api endpoint /ocs/v2.php/apps/user_status/api/v1/statuses/<someuser>

[szaimen]

Is this Issue still valid in NC21.0.2? If not, please close this issue. Thanks! :)

[ghost]

This issue has been automatically marked as stale because it has not had recent activity and seems to be missing some essential information. It will be closed if no further activity occurs. Thank you for your contributions.

[mitsol]

I still have this issue, is there any solution? Where can i set the needed endpoint (currently the error occurs from /ocs/v2.php/apps/user_status/api/v1/user_status?format=json

[markuman]

I still have this issue, is there any solution?

As mentioned above. Insert status='Offline' in the table where status is null is the only solution. There is no other way and there will be no other way.

[Heyian]

I had to make a couple of changes to the commande to make it work. The variable for the status_timestamp wasn't linked to any data, so it was considering it as a string, so I just manually put the Unix time of one of my user. And the 0 for is_user_defined was considered an integer instead of a boolean so I changed it to "false".

Also, for any newb like me reading this, accessing the database with the psql command wasn't working until I specified the host as 127.0.0.1, localhost wasn't working for me because it was set to the "PEER" method. You can check this in your Postgres config in /etc/postgresql/{version}/main/pg_hba.conf

Here's the code I used to make it work:

INSERT
    INTO
    oc_user_status (user_id, status, status_timestamp, is_user_defined)
SELECT
    uid AS user_id,
    'Offline' AS status,
    1686843638 AS status_timestamp,
    false AS is_user_defined
FROM
    oc_users
INNER JOIN oc_preferences ON
    (uid = userid)
WHERE
    uid NOT IN (
    SELECT
        user_id
    FROM
        oc_user_status)
    AND configkey = 'lastLogin';

[markuman]

Also, for any newb like me reading this, accessing the database with the psql command wasn't working until I specified the host as 127.0.0.1, localhost wasn't working for me because it was set to the "PEER" method. You can check this in your Postgres config in /etc/postgresql/{version}/main/pg_hba.conf

Here's the code I used to make it work:

Good to mention. My SQL statement above was executed on a mariadb.