MySQL database cleanup of specifi rows

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
stevenospam
Registered User
Posts: 84
Joined: Thu Dec 15, 2011 2:02 am

MySQL database cleanup of specifi rows

Post by stevenospam »

My database is getting too big. I find the following have lots of records and I'm wondering if it's OK to empty these via PHPmyAdmin.
phpbb3_confirm
phpbb3_qa_confirm
phpbb3_search_wordlist
phpbb3_search_wordmatch
phpbb3_sessions

I don't find a way to clear these in the Admistration Control Panel
User avatar
Crizzo
Translations & International Support Teams Manager
Translations & International Support Teams Manager
Posts: 1653
Joined: Thu Apr 23, 2009 1:20 pm
Location: Stuttgart, Germany
Name: Christian
Contact:

Re: MySQL database cleanup of specifi rows

Post by Crizzo »

No, Deal with it.

These tables e.g. contain valid sessions from your users and the search index. Without it, the search function will not work anymore.
My extensions for phpBB: CDB
German phpBB Support at www.phpbb.de
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: MySQL database cleanup of specifi rows

Post by thecoalman »

The contents of the search tables can be deleted in the ACP,
ACP >> Maintenance tab >> Search link on left

Search will no longer work for old posts. The index can be recreated so those posts are searchable and will likely produce slightly smaller size, how much smaller depends but without any changes to search options it will in most cases be negligible.

You can fully disable search to prevent them from being populated with new posts.
ACP >> Search Settings

If you wanted to reduce the size of those table and still keep search functional there is option there for word length. Increasing that will produce smaller index, downside is shorter words are not searchable. You can also disable indexing on individual forums on a forum's setting page. You would make these changes before recreating the search index.
“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
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: MySQL database cleanup of specifi rows

Post by david63 »

The sessions table should be being maintained automatically by a phpBB cron task. If that table is exceptionally large it may be that you are having a problem with cron tasks.
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
stevenospam
Registered User
Posts: 84
Joined: Thu Dec 15, 2011 2:02 am

Re: MySQL database cleanup of specifi rows

Post by stevenospam »

thecoalman wrote: Wed Apr 14, 2021 12:01 pm The contents of the search tables can be deleted in the ACP,
ACP >> Maintenance tab >> Search link on left

Search will no longer work for old posts. The index can be recreated so those posts are searchable and will likely produce slightly smaller size, how much smaller depends but without any changes to search options it will in most cases be negligible.

You can fully disable search to prevent them from being populated with new posts.
ACP >> Search Settings

If you wanted to reduce the size of those table and still keep search functional there is option there for word length. Increasing that will produce smaller index, downside is shorter words are not searchable. You can also disable indexing on individual forums on a forum's setting page. You would make these changes before recreating the search index.
@thecoalman thanks so much for your response. Search isn't important and I've cleaned up the search data. The remaining very large entity is PHPBB3_sessions. Can I empty this? I find little documentation about its use.
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: MySQL database cleanup of specifi rows

Post by david63 »

stevenospam wrote: Wed Apr 14, 2021 12:55 pm The remaining very large entity is PHPBB3_sessions. Can I empty this?
See my post above.

If you empty the sessions table then it will have the effect of logging all your users out.

If you intend emptying that table I would only remove the records for user_id 1 (Anonymous User)
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
stevenospam
Registered User
Posts: 84
Joined: Thu Dec 15, 2011 2:02 am

Re: MySQL database cleanup of specifi rows

Post by stevenospam »

david63 wrote: Wed Apr 14, 2021 1:06 pm
stevenospam wrote: Wed Apr 14, 2021 12:55 pm The remaining very large entity is PHPBB3_sessions. Can I empty this?
See my post above.

If you empty the sessions table then it will have the effect of logging all your users out.

If you intend emptying that table I would only remove the records for user_id 1 (Anonymous User)
david63, you're exactly right. I have the cron setting to use the system cron rather than PHPBB because the board is lightly used but it's important that digests go out regularly. I have in the system cron
curl --silent https://forum.somedomainname.org/cron.p ... .cron_task
I don't understand what actually runs nor where to find the configured PHPBB crons but I'm wondering if I need to add the bin/phpbbcli.php cron:run to the system cron.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: MySQL database cleanup of specifi rows

Post by thecoalman »

Try:

Code: Select all

*/5  *  *  *  * /usr/local/bin/php /home/username/public_html/phpbb/bin/phpbbcli.php cron:run
That will execute it every five minutes. The second path needs to be edited for full server path to cron.php. If you are using Cpanel /home/username/ is typical on and the username is typically the main domain without the .com etc. If you do not know what it is look up the value for doc_root in php information.

ACP >> System tab >> PHP information link on left.


----edit----
Before doing this you might want to look in cache folder for the file queue.php, if this is large file it's because cron.php was not sending notifications. It will start sending them and your users will get bombarded with old notices. You can safely delete it.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
stevenospam
Registered User
Posts: 84
Joined: Thu Dec 15, 2011 2:02 am

Re: MySQL database cleanup of specific rows

Post by stevenospam »

Thanks for the cron info. It turns out there's a "purge sessions" selection under the General administration tab that purges the rows from the sessions table in the database. That removed the 2 million records and, since it took 10 years to get that big, I'll call it a win. I'll now work on getting the cron syntax you provided working on my system. The directory paths are weird for my host.
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: MySQL database cleanup of specifi rows

Post by david63 »

stevenospam wrote: Wed Apr 14, 2021 9:10 pm It turns out there's a "purge sessions" selection under the General administration tab that purges the rows from the sessions table in the database.
When you use that feature you will remove all sessions from the sessions table which will have the effect of logging all your users out which could upset them if they were in the process of creating a post
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
stevenospam
Registered User
Posts: 84
Joined: Thu Dec 15, 2011 2:02 am

Re: MySQL database cleanup of specifi rows

Post by stevenospam »

Thanks for all your help David63! I couldn't have fixed this without you. The board is lightly used and no one was affected by my clearing the sessions. I did try a MySQL DELETE Where command but it timed out because there were so many records. I'll stay on top of this in the future.
Post Reply

Return to “[3.2.x] Support Forum”