The Rate Limiting page displays an error as soon as any user reaches the maximum allowed number of requests for the first time

XMLWordPrintable

    • 9
    • Severity 2 - Major
    • 50

      Issue Summary

      After configuring and enabling rate limiting, as soon as any user reaches the maximum number of requests allowed, an error "Rate limiting might not be working properly" appears when visiting the Rate Limiting page, and the list of users who were "rate limited" is empty.

      This error occurs when using Confluence with MS SQL server 2017, 2019, 2022 or Azure SQL database or Oracle 19.3 database.

      This is reproducible on the Data Center: Yes

      Steps to Reproduce

      • Spin up a Confluence instance, connected to any of the affected databases listed above.
      • Enable Rate Limiting and configure it with the number of Requests allowed per hour per user on a node.
      • Use the following curl (or something similar) to trigger the Rate Limiting: 
        for i in $(seq 60); do curl -v -u username:password https://<confluence-base-url>/rest/api/2/myself ; done
      • Navigate to Confluence Administration > General Configuration > Rate Limiting and click on Exemptions and then List of Limited accounts, such that the request is made to the endpoint $CONF_BASE_URL/plugins/servlet/ratelimiting#history in the browser navigation URL.

      Note - If the issue is not directly reproducible for any of the affected databases, try accessing Confluence from an Incognito window, flushing the cache from General Configuration> Cache Management> Flush All, or waiting for a couple of minutes (~5 mins).

      Expected Results

      The Rate Limiting page has no errors, and the list of users who were rate-limited is populated.

      Actual Results

      Below error is observed while viewing the list of Rate Limited users from the UI.

      The below error is observed in the atlassian-confluence.log

      2025-04-24 12:20:03,299 DEBUG [http-nio-8090-exec-49 url: /confluence/rest/rate-limiting/latest/admin/rate-limit/history/; user: xxxxx] [ratelimiting.rest.resource.RateLimitingExceptionMapper] handleGeneralException Caught unknown exception: 
 -- url: /confluence/rest/rate-limiting/latest/admin/rate-limit/history/ | userName: admin | referer: https://CONFLUENCE_BAS_URL/confluence/plugins/servlet/ratelimiting | traceId: 330d4ab527983574
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_AC3877_RL_USER_COUNTER"."USER_ID", sum("AO_AC3877_RL_USER_COUNTER"."REJECT_COUNT"), max("AO_AC3877_RL_USER_COUNTER"."INTERVAL_START"), "AO_AC3877_USER_RL_SETTINGS"."USER_ID" is not null from "AO_AC3877_RL_USER_COUNTER" "AO_AC3877_RL_USER_COUNTER" left join "AO_AC3877_USER_RL_SETTINGS" "AO_AC3877_USER_RL_SETTINGS" on "AO_AC3877_RL_USER_COUNTER"."USER_ID" = "AO_AC3877_USER_RL_SETTINGS"."USER_ID" group by "AO_AC3877_RL_USER_COUNTER"."USER_ID", "AO_AC3877_USER_RL_SETTINGS"."USER_ID" order by sum("AO_AC3877_RL_USER_COUNTER"."REJECT_COUNT") desc offset ? rows fetch next ? rows only
	at com.querydsl.sql.support.JavaSE7SQLExceptionWrapper.wrap(JavaSE7SQLExceptionWrapper.java:41)
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:48)
	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
	at com.querydsl.sql.AbstractSQLQuery.fetchResults(AbstractSQLQuery.java:542)
        .
        .
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'is'.
        .
        .
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid usage of the option next in the FETCH statement.

      Although we see the error in the UI and in the logs, the Rate Limiting functionality is working as expected. However, this issue prevents admins from tracking which users are hitting the Rate Limit.

      2025-04-24 12:40:15,793 WARN [http-nio-8090-exec-14 url: /confluence/rest/api/2/myself; user: xxxx] [ratelimiting.internal.filter.RateLimitFilter] lambda$userHasBeenRateLimited$0 User [2cb2808596678e79019667ae469d000b] has been rate limited

      Workaround

      Since the Bug prevents viewing the list of users who have been rate-limited from the UI, the following SQL statement can be used as a workaround to identify the list of users who have been Rate-Limited and whether they have already been added to the Exemptions List.

      List of Rate Limited Users
      SELECT 
    RL_COUNTER.USER_ID,
    um.username,  
    SUM(RL_COUNTER.REJECT_COUNT) AS TOTAL_REJECTS,
    MAX(RL_COUNTER.INTERVAL_START) AS LAST_INTERVAL,
    CASE 
        WHEN RL_SETTINGS.USER_ID IS NOT NULL THEN 'RATE_LIMITED_BUT_EXEMPTED' 
        ELSE 'RATE_LIMITED' 
    END AS IS_RATE_LIMITED_USER
FROM AO_AC3877_RL_USER_COUNTER RL_COUNTER
INNER JOIN user_mapping um ON um.user_key = RL_COUNTER.USER_ID
LEFT JOIN AO_AC3877_USER_RL_SETTINGS RL_SETTINGS 
    ON RL_COUNTER.USER_ID = RL_SETTINGS.USER_ID
GROUP BY 
    RL_COUNTER.USER_ID, 
    um.username,  
    RL_SETTINGS.USER_ID
ORDER BY 
SUM(RL_COUNTER.REJECT_COUNT) DESC;

      The output of the above SQL query will list the users who have been rate-limited based on the defined limits and indicate whether they have already been added to the Exempted Users list.

        1. Screenshot 2025-04-24 at 6.00.23 PM.png
          Screenshot 2025-04-24 at 6.00.23 PM.png
          145 kB
        2. Screenshot 2025-04-24 at 5.50.32 PM.png
          Screenshot 2025-04-24 at 5.50.32 PM.png
          253 kB

            Assignee:
            Przemysław Grabarek
            Reporter:
            Nitin Rastogi
            Votes:
            14 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved: