GET GROUP truncated

[darren]

My attempts to get a group with many members via the API seems to truncate the number of members returned at 1599 [and the 1599th member uid is truncated mid-string]. Is there a way to paginate a group or groups GET or return the full membership?

[nebulon]

I guess you mean the GET /api/v1/groups/:groupId api route? https://docs.cloudron.io/api.html#tag/Groups/operation/getGroupById

In code there is no limit or pagination for the members as such, so the truncating of of the response seems to be somewhere maybe in the reverse proxy or otherwise. Do you see any errors maybe in the nginx reverse proxy logs on the system?

[girish]

Does the UI show all the members? If it does, then the bug is somewhere else.

[darren]

Yes both /api/v1/groups/:groupId and /api/v1/groups/ produce the same behavior.
I don't see any errors in the NGINX log

here's a relevant line for a GET

[no timestamp]  - - [06/Oct/2024:23:59:19 +0000] "GET /api/v1/groups/gid-4802e498-4b29-4eec-bcee-57186b126aa4 HTTP/1.1" 200 36890 "-" "axios/1.7.4"

And the UI does show all group members

[nebulon]

Since the UI is using the very same REST api, looks like something else in your stack is truncating the response then.

[darren]

I was wrong. The UI also has truncated group membership. Here's the console:

[girish]

@darren try this on the server

mysql -uroot -ppassword -e 'select count(*) from box.groupMembers WHERE groupId="gid-4802e498-4b29-4eec-bcee-57186b126aa4"'

[darren]

+----------+
| count(*) |
+----------+
| 6048 |
+----------+

[girish]

@darren thanks. I wonder if the issue here is that we hit some packet limits . Did you already do a simple curl to check if the output is truncated or not? i.e a simple curl and not via another app, just to eliminate all other possible issues.

[girish]

@darren From a quick review of our code, it seems we don't use that API in the frontend. Only place is when you try to delete a group in the UI, the api is called to get the group count. So, if you click delete Group in the UI, do you see the correct count in the delete confirm dialog? Of course, don't follow through with the deletion (be careful!)

[darren]

Yes it's truncated with a simple curl as well. What's weird is that the truncation also occurs within the response for the specific group at the /groups endpoint and not just the groups/:groupId endpoint.

the deletion dialog has the understated member count:

This group still has 1599 member(s). Are you sure this group is not used?

[girish]

@darren I think the issue is that we use GROUP_CONCAT and apparently this has a max length - https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_group_concat_max_len

Can you try editing /etc/mysql/mysql.cnf and adding group_concat_max_len=65536 (just something large) in the mysqld section ? After that systemctl restart mysql

[darren]

Unfortunately the response is the same so far after updating the config file and restarting mysql on the server. I am going to try set max_allowed_packet to a larger value as well.

[darren]

Hmm nm I looked at max_allowed_packet and presumably the default of 64MB is not constraining the response.

[girish]

mmm, I was pretty sure it's the group concat.

You can try the mysql directly. mysql -uroot -ppassword box. and then (just replace the group id below),

mysql> SELECT id,name,source,GROUP_CONCAT(groupMembers.userId) AS userIds FROM userGroups LEFT OUTER JOIN groupMembers ON userGroups.id = groupMembers.groupId WHERE userGroups.id = 'gid-07e5c661-f43b-45b1-8dc0-8355ca3f05da' GROUP BY userGroups.id
    -> ;
+------------------------------------------+--------+--------+-----------------------------------------------------------------------------------+
| id                                       | name   | source | userIds                                                                           |
+------------------------------------------+--------+--------+-----------------------------------------------------------------------------------+
| gid-07e5c661-f43b-45b1-8dc0-8355ca3f05da | people |        | uid-1b9e3dcd-d91d-462b-8dac-d13f01febb14,uid-ca8ccd80-6cd2-4221-9150-e0b2d69333f4 |
+------------------------------------------+--------+--------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[darren]

@girish Good news I was able to isolate a little more. It seems like 65536 bytes is the length at which the group_concat was actually being truncated. So setting group_concat_max_len = 65536 wasn't doing anything. But if I set that to a sufficiently higher value, e.g. group_concat_max_len = 4294967295, we can get the full group membership within mysql. I have not yet been able to get the full response from the API though.

mysql> SHOW VARIABLES LIKE '%group_concat%';+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| group_concat_max_len | 4294967295 |
+----------------------+------------+
1 row in set (0.01 sec)

mysql> SELECT length(GROUP_CONCAT(groupMembers.userId))-length(replace(GROUP_CONCAT(groupMembers.userId),',','')) AS userIds FROM userGroups LEFT OUTER JOIN groupMembers ON userGroups.id = groupMembers.groupId WHERE userGroups.id = 'gid-4802e498-4b29-4eec-bcee-57186b126aa4';
+---------+
| userIds |
+---------+
|    6047 |
+---------+
1 row in set (0.01 sec)

[darren]

Ah I think I've found it. You're setting group_concat_max_len = 65536 on line 66 of database.js.
Looks like this commit: https://git.cloudron.io/cloudron/box/-/commit/94a196bfa00ff689b0862fe797d64a917111a91d

[girish]

@darren thanks! I have bumped the limit now. https://git.cloudron.io/cloudron/box/-/commit/34969d9980da38937f88201a01460109f04a60ac . Guess we have to make member pagination API at some point.

[darren]

Thank you!