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

Prune Users produced unnecessarily long query; Got a packet bigger than 'max_allowed_packet' bytes

    Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.0.10
    • Fix Version/s: 3.0.11-RC1
    • Component/s: ACP
    • Labels:
      None
    • Environment:
      PHP 5.3.10, MySQL 5.1.61, OS X 10.6.8, Firefox 10.0

      Description

      I apologize if this is too verbose but I want to make sure you have all the context.

      I was working on cleaning up an old phpBB board with a severe spam infestation — over a hundred spambot users, many with thousands of posts each. I downloaded the board's database (over 4GB) and imported it into my local MySQL server. I set PHP's max_execution_time to 0, max_input_time to -1, and memory_limit to 4G. I created a new phpBB 3.0.8 installation locally and used the converter to import the data from the old phpBB 2 board. Then I upgraded to phpBB 3.0.10. Then I used the Prune Users function to delete the spambot users and all their posts, a few users at a time. Each such batch might take an hour or more, but completed successfully.

      At one point, I asked phpBB to delete ten such users at once, and let it run overnight. It successfully deleted seven users and their posts but then returned this error:

      Got a packet bigger than 'max_allowed_packet' bytes [1153]

      max_allowed_packet was not specified in my my.cnf so it was at its default value. I increased max_allowed_packet to 128M and proceeded with smaller batches of users and was able to eliminate all the spam.

      But I don't believe phpBB should have needed to construct such a large query. The query it was trying to run was displayed:

      SELECT search_key FROM phpbb_search_results WHERE search_authors LIKE '% 1701 %' OR search_authors LIKE '% 1701 %' OR search_authors LIKE '% 1701 %' OR search_authors LIKE '% 1701 %' OR [snip]

      I've snipped the rest of the query because it just repeats the same "search_authors LIKE '% 1701 %'" condition over and over — 33,175 times. The entire query is 1,127,996 bytes. There doesn't seem to be a reason why the query needs to be any longer than just "SELECT search_key FROM phpbb_search_results WHERE search_authors LIKE '% 1701 %'" so I think there's something wrong in the phpBB code that generated this query.

        Attachments

          Activity

            People

            • Assignee:
              nickvergessen Joas Schilling
              Reporter:
              ryandesign ryandesign
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: