Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-14992

User notifications table allowing duplicate entries

    Details

      Description

      According to the report made here:
      https://www.phpbb.com/community/viewtopic.php?f=64&t=2401681
      It is possible to receive duplicate notifications.

      After debugging and researching this issue is happening because the user_notifications table allows for duplicate entries of each notification type and method per user. Not entirely sure how this happened, but one possible solution going forward is to add a constraint wherein the item_type, user_id, and method columns need to be unique.

      The query needed to find the existing duplicate data is as follows:

      SELECT un.user_id, un.item_type, un.method
      FROM phpbb_user_notifications un
      INNER JOIN (SELECT user_id, item_type, method
                  FROM phpbb_user_notifications
                  GROUP BY user_id, item_type, method
                  HAVING COUNT(item_type) > 1) AS undups
      ON un.user_id = undups.user_id;
      

      This returns 184 rows on phpbb.com but will be needed in order to create a migration to correct the data.

      While running the above query it was also noted that it is EXTREMELY slow. The notification settings page is also responding very slow as well on load and on saving the form. Upon closer inspection there are no indexes in the user_notifications table. I would recommend adding the following indexes:

      ALTER TABLE phpbb_user_notifications
         ADD KEY `user_id` (`user_id`),
         ADD KEY `user_id_item_id` (`user_id`, `item_id`),
         ADD KEY `item_type_item_id_user_id_method` (`item_type`, `item_id`, `user_id`, `method`),
         ADD KEY `user_id_item_type_item_id` (`user_id`, `item_type`, `item_id`);
      

      This is based on how the data is being retrieved in several portions of the core.

        Attachments

          Activity

            People

            • Assignee:
              Marc Marc
              Reporter:
              DavidIQ DavidIQ
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: