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

User notifications table allowing duplicate entries



      According to the report made here:
      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.




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


              • Created: