GET GROUP truncated

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