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.