[jira] [Closed] (GUACAMOLE-1253) Hitting 2100 SQLServer query parameter limit with large user base

["Mike Jumper (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/GUACAMOLE-1253?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Jumper closed GUACAMOLE-1253.
----------------------------------
    Resolution: Fixed

> Hitting 2100 SQLServer query parameter limit with large user base
> -----------------------------------------------------------------
>
>                 Key: GUACAMOLE-1253
>                 URL: https://issues.apache.org/jira/browse/GUACAMOLE-1253
>             Project: Guacamole
>          Issue Type: Bug
>          Components: guacamole-auth-jdbc, guacamole-auth-jdbc-sqlserver
>         Environment: docker
>            Reporter: Starke Gardner
>            Assignee: Mike Jumper
>            Priority: Minor
>             Fix For: 1.5.2
>
>
> We have a large user count ~1500 and use SQL Server. When trying to load the user settings on the web interface the page will never load and we get this error:
> {code:none}
> SELECT [guacamole_user].user_id, [guacamole_entity].entity_id, [guacamole_entity].name, password_hash, password_salt, password_date, disabled, expired, access_window_start, access_window_end, valid_from, valid_until, timezone, full_name, email_address, organization, organizational_role, ( SELECT MAX(start_date) FROM [guacamole_user_history] WHERE [guacamole_user_history].user_id = [guacamole_user].user_id ) AS last_active FROM [guacamole_user] JOIN [guacamole_entity] ON [guacamole_user].entity_id 
>  [guacamole_entity].entity_id WHERE [guacamole_entity].name IN ( ? , ? , ? ,.......... ? ) AND [guacamole_entity].type = 'USER'; SELECT [guacamole_user_attribute].user_id [guacamole_user_attribute].attribute_name, [guacamole_user_attribute].attribute_value FROM [guacamole_user_attribute] JOIN [guacamole_user] ON [guacamole_user].user_id 
>  [guacamole_user_attribute].user_id JOIN [guacamole_entity] ON [guacamole_user].entity_id = [guacamole_entity].entity_id WHERE [guacamole_entity].name IN ( ? , ? ,........ ? ) AND [guacamole_entity].type = 'USER';
> ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
> {code}
> I truncated the name IN statement as there was a "?" for each of the 1500 users two times in the select statement.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)