[jira] [Commented] (GUACAMOLE-1796) Hitting 2100 SQLServer query parameter limit with large connection base

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

    [ https://issues.apache.org/jira/browse/GUACAMOLE-1796?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728042#comment-17728042 ] 

Mike Jumper commented on GUACAMOLE-1796:
----------------------------------------

GUACAMOLE-1253 changed the way all queries execute, not just users, running those queries in batches. Perhaps even the new default batch size of 1000 is too large for some queries (this particular query contains the same set of identifiers in three locations).

[~zeclab], can you try setting the following in your {{guacamole.properties}}:

{code:none}
sqlserver-batch-size: 500
{code}


> Hitting 2100 SQLServer query parameter limit with large connection base
> -----------------------------------------------------------------------
>
>                 Key: GUACAMOLE-1796
>                 URL: https://issues.apache.org/jira/browse/GUACAMOLE-1796
>             Project: Guacamole
>          Issue Type: Bug
>          Components: guacamole-auth-jdbc-sqlserver
>    Affects Versions: 1.5.2
>         Environment: docker
>            Reporter: Chris Johnson
>            Priority: Major
>         Attachments: guacamole-client.log
>
>
> We have a rather large number of connections and after logging in, I'm getting "An error occurred and this action cannot be completed".
> In the Guacamole log I'm getting the following error:
> {quote}^### Error querying database.  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.^
> ^### The error may exist in org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml^
> ^### The error may involve defaultParameterMap^
> ^### The error occurred while setting parameters^
> ^### SQL: SELECT             [guacamole_connection].connection_id,             [guacamole_connection].connection_name,             parent_id,             protocol,             max_connections,             max_connections_per_user,             proxy_hostname,             proxy_port,             proxy_encryption_method,             connection_weight,             failover_only,             (                 SELECT MAX(start_date)                 FROM [guacamole_connection_history]                 WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id             ) AS last_active         FROM [guacamole_connection]         WHERE [guacamole_connection].connection_id IN << A load of question marks >> ### 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.^
> {quote}
> I believe that there was a similar issue that was fixed in GUACAMOLE-1253 but for the number of users.
> I have attached the log if that helps.
> At the moment, the platform is unusable for ourselves.



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