Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-10630

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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Minor Minor
    • 3.0.11-RC1
    • 3.0.10
    • ACP
    • None
    • PHP 5.3.10, MySQL 5.1.61, OS X 10.6.8, Firefox 10.0

      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.

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

              Created:
              Updated:
              Resolved: