Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-40354

chatData requests cause all chat messages in channel to be queried from db

    Details

    • Type: Bug
    • Status: Verified
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 12.2
    • Fix Version/s: 12.4, 19.0
    • Component/s: Chat
    • Labels:
    • 12 status:
      Resolved
    • Test Plan:
      Hide

      No functional changes. Regression testing that chat continues to work.

      Impact of this change can be verified by looking at sql queries to the database (e.g. using mysql general logging). The query in the issue description should not occur when a user is idle in the chat room (i.e. in response to chatData requests).

      Show
      No functional changes. Regression testing that chat continues to work. Impact of this change can be verified by looking at sql queries to the database (e.g. using mysql general logging). The query in the issue description should not occur when a user is idle in the chat room (i.e. in response to chatData requests).

      Description

      Observed with a single user in a chat room on a single-node test system, with mysql logging enabled.

      Each request like this:

      /direct/chat-message/14085e39-ce5d-4d36-8699-161ae4103612/chatData.json?siteId=6f8b7fe3-ddf1-4361-90d3-3f5c84cf7d49&channelId=a9e17333-043c-471d-8379-e353028797d6&_=1532006826992 
      

      causes a db query like this, which fetches all messages in the channel:

      2018-07-19T13:28:53.464021Z     639663 Execute  select messages0_.CHANNEL_ID as CHANNEL_6_18_1_, messages0_.MESSAGE_ID as MESSAGE_1_19_1_, messages0_.MESSAGE_ID as MESSAGE_1_19_0_, messages0_.BODY as BODY2_19_0_, messages0_.CHANNEL_ID as CHANNEL_6_19_0_, messages0_.MESSAGE_DATE as MESSAGE_3_19_0_, messages0_.migratedMessageId as migrated4_19_0_, messages0_.OWNER as OWNER5_19_0_ from CHAT2_MESSAGE messages0_ where messages0_.CHANNEL_ID='a9e17333-043c-471d-8379-e353028797d6'
      

      though if there are no new messages, the JSON payload is basically empty.

      This is very inefficient, especially in sites with a lot of message in a channel (we have many sites with > 5K messages in a single chat room, and one site with 32K messages).

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jtingen Jolie Tingen
                Reporter:
                smarquard Stephen Marquard
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code