table _qa_confirm trim?

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Chobop
Registered User
Posts: 10
Joined: Sat May 07, 2022 10:36 pm

table _qa_confirm trim?

Post by Chobop »

I've a phpbb forum that has gone through several phpbb version upgrades since its inception almost 20 years ago. The board is currently on 3.3.10.

I recently noticed that the _qa_confirm table in the database is at 6.5 million records and growing, with an overall table filesize of 1.7 G. Is this intentional or was there a table cleanup script that didn't get activated in a 3.0 upgrade somewhere in time?

I do have to replace the Q&A captcha questions with new entries every several months to thwart the bots.
Chobop
Registered User
Posts: 10
Joined: Sat May 07, 2022 10:36 pm

Re: table _qa_confirm trim?

Post by Chobop »

Just updating my original topic; the _qa_confirm table is now at 2.3 G and 8 million records :lol: The recent influx was caused by a flood of infected machines hammering away at the submission form, which have since been dealt with. But as always, the table only increases in size and is, by far, the largest table in my phpbb3 installation.

Has anyone ever had to manually clear this table, or is this working as intended?
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5960
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: table _qa_confirm trim?

Post by thecoalman »

It should not be anywhere near that size. I see a couple of dozen rows on most forums. Is your sessions table massive too?
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26658
Joined: Fri Aug 29, 2008 9:49 am

Re: table _qa_confirm trim?

Post by Mick »

How are you dealing with the spam before you change the Q&A? Are you removing it it leaving it in place? Please fill out the Support Request Template and post it back here to enable us to assist you better.
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
User avatar
warmweer
Jr. Extension Validator
Posts: 11374
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: table _qa_confirm trim?

Post by warmweer »

Chobop wrote: Wed Dec 27, 2023 4:01 am Has anyone ever had to manually clear this table, or is this working as intended?
Deleting 8.000.000 records from a table could easily result in a lock due to insufficient memory.

Simple way to avoid that =
1. backup the table (definition only) separately
2. delete the massive table
3. restore the backup you made in 1.

Result = a new empty table _qa_confirm
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53463
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: table _qa_confirm trim?

Post by Brf »

Just truncate the table. Run this:

Code: Select all

truncate table phpbb_qa_confirm
change phpbb_ to match your prefix
Chobop
Registered User
Posts: 10
Joined: Sat May 07, 2022 10:36 pm

Re: table _qa_confirm trim?

Post by Chobop »

thecoalman wrote: Wed Dec 27, 2023 2:58 pm It should not be anywhere near that size. I see a couple of dozen rows on most forums. Is your sessions table massive too?
Mick wrote: Wed Dec 27, 2023 5:30 pm How are you dealing with the spam before you change the Q&A? Are you removing it it leaving it in place? Please fill out the Support Request Template and post it back here to enable us to assist you better.
Sorry about the delay in response, got busy with holiday stuff ;)

Session table is an okay size - about 1000 records. I recently had a lot of malicious bots that pounded away at the forum for a while (think 400+ random IPs from infected machines trying to get through the Q&A all at the same time), which I believed is what caused the recent inflation of the _qa_confirm table, now at 8.8 million records. Anonymous posting is allowed in several sections of the board for those looking for a quick answer to a problem, but guest users have to clear the Q&A questions to successfully post. The bad script bots don't get through since I manually changed the questions every so often, but they're still programmed to try. So while there actually aren't any spam posts on the board, there is an ongoing attempt at such.

Where is the mechanism found in phpbb that automatically deflates the qa_confirm table? While it sounds like I can manually purge the table (thank you guys for confirmation that is a possible solution), the same situation may happen in a few years. I'm thinking I missed something during an upgrade that would have taken care of that automatically.

Return to “[3.3.x] Support Forum”