TMP file in MYSQL keeps getting full and crashing my forum!

[WWu777]

Dear phpbb support,
My forum keeps crashing every week or two, because the TMP file in MYSQL keeps getting full and maxing out. When that happens I get this error message on the forum:

General Error
SQL ERROR [ mysqli ]

Disk full (/tmp/#sql_1d9a_1.MAI); waiting for someone to free some space... (errno: 28 "No space left on device") [1021]

An SQL error occurred while fetching this page. Please contact the Board Administrator if this problem persists.

My webhost said that I can't clear out the TMP file and that only their higher admins can. What is this? I created a ticket for this issue and today my webhost said that the issue is connected to the phpbb_sessions table. See their email to me below. What can I do about this?

Thank you for contacting support. My name is Joshua and I am a Web Administrator with Bluehost. I am following up on this case regarding /tmp being 100% full. Looking into this further it appears that /tmp is filling up due to checks done on your MySQL database happier2_phpbbbackup_restore. Currently, it looks like happier2_phpbbbackup_restore is 3.3G and your /tmp directory is only 1.1G. It does appear that the largest table is phpbb_sessions. Moving forward you can either:

1) Work with your site developer in order to reduce the size of the phpbb_sessions table

2) Have us increase the size of /tmp. Currently, /tmp is 1.1G. You have 2.1G available on your main drive. If you were looking to have us increase the size of /tmp you may want to reduce the current usage of your account first. Here is a breakdown of the account:

https://privatebin.support-tools.com/?a ... iWCB9PjAMq

If you would like us to increase the size of /tmp, please let us know how large you would like to increase it to. If you would like us to remove any content prior, please reply to this email with "I GIVE BLUEHOST PERMISSION TO REMOVE <insert FULL file paths here>". Please note that content removal is irrecoverable.

If you have any further questions, please do not hesitate to reply to this email.

My reply to them:

So you mean the phpbb sessions table is bloating up and causing the forum to crash? What if I just delete that table? That table is expendable and doesn't contain any posts or data and can be recreated automatically by the forum. Why would a sessions table become 1 GB? It's just a temporary table. Could spam bots be filling it up?

Yes you can just delete the TMP folder on my SQL server. You did that 2 or 3 times before right? Check my records. Removing it doesn't erase anything important. It's just a cache folder right? Why didn't you remove it yet? Last time your customer service staff just removed it without any further questions. It looks like your security protocols are more strict than before?

I have little space left on my server. Increasing the size of the TMP file allowance would only be a temporary solution until it fills up again. It's better to prevent it from filling up. Would enabling cloudflare help?

Their next reply to me:

My name is Elissa S. of escalated support,

Generally we should ask before removing client data. Since the sessions table is generated by your site we should be asking before removing the files. For files in /tmp we're generally more willing to go ahead wit those removals without permission given those files are often automatically generated by system services. For example I see we removed the /tmp/wpinstaller zip files as these were generated by wpinstaller and the failure of wpinstaller to manage these files in the past has been an issue. Since those do not contain sites or unique user generated data there's little harm in removing them.

On the sessions table: In my understand removing that table would logout all users on your site. They can just log back in. Generally we do not delete or drop tables automatically because we are not your system administrators nor do we specialized in phpbb. In most cases it would be fine but since we did not build, design or maintain your site I don't know if you've modified what's kept that or if this data was purposeful retained by you for a specific reason.

I do understand that the phpbb administrator tools do have a clear sessions option.

Why is it large? Probably because there are either a large amount of logins or login attempts or your site isn't cleaning up the sessions after they expire or they're set to not expire. Spambots could be at fault. That said we are not database administrators and I would suggest consulting with a phpbb administrator for better insight into this issue Using cloudflare is generally a good idea as their cdn usually filters out known problem bots and attackers that have hit other sites automatically.

As to dropping the sessions table, you can do so in phpmyadmin or via the mysql command line. There might be an option in phpbb to clear the sessions table too, if that's available I would suggest that as it would do it in the way the software expects. If you want me to do it I would login as root, and use the mysql command line to execute the following

USE happier2_phpbbbackup_restore;
TRUNCATE TABLE phpbb_sessions;


Again this would just completely wipe the table and I'm doing this on the inference that the following posts contain accurate information. Again we do not have phpbb administration experience here and I would strongly recommend retaining someone with the appropriate experience or having a working backup that you can restore.

viewtopic.php?t=1314325
viewtopic.php?t=1638695
viewtopic.php?t=2375091

I hope this information helps, but if you require further assistance or have any additional questions, then please do not hesitate to reply to this email so that we may continue to help as needed.

Any advice? What does it mean to truncate the phpbb_sesssions table? How do I do that? Would that help? Is that a permanent fix?

Also, what's the connection between the TMP file in MYSQL and the phpbb_sessions table? I searched this forum's archives and there's no info about that that I could find.

Thanks.

[thecoalman]

Just to be clear you don't want to remove or delete the table. Truncate means to empty the table of data.

You can do this right from ACP on main page using the button for "Purge all sessions". As noted that will also log all users out.

Alternatively you can run this SQL in phpMyadmin and just remove the sessions for guests:

Code: Select all

DELETE FROM `phpbb_sessions` WHERE `session_user_id` = 1

When you see a lot of guests online check their user agents. Click the Who is online link and then click display guests If they are identifying themselves as bots add them to the bot list. That is one thing that can bloat the sessions table because a bot can generate numerous sessions.

[WWu777]

Just to be clear you don't want to remove or delete the table. Truncate means to empty the table of data.

You can do this right from ACP on main page using the button for "Purge all sessions". As noted that will also log all users out.

Alternatively you can run this SQL in phpMyadmin and just remove the sessions for guests:

Code: Select all

DELETE FROM `phpbb_sessions` WHERE `session_user_id` = 1

When you see a lot of guests online check their user agents. Click the Who is online link and then click display guests If they are identifying themselves as bots add them to the bot list. That is one thing that can bloat the sessions table because a bot can generate numerous sessions.

Thanks. I guess I can just empty the sessions table in phpmyadmin too. Or do it from the ACP. However, why does the sessions table keep causing the TMP file in MYSQL to overload? Is it because of spam bots or DDOS attacks? If so, is it better to enable Cloudflare even though it will slow down the forum a little?

[thecoalman]

If you have a lot of traffic from bots that are not listed in the bots list yes that can cause bloated sessions table.

Another reason is phpbb cron is not running successfully.

Cloudflare has lot of tools for handling bot traffic and many other things. It' should not impact the performance of your site. If anything it will probably increase it.

[WWu777]

If you have a lot of traffic from bots that are not listed in the bots list yes that can cause bloated sessions table.

Another reason is phpbb cron is not running successfully.

Cloudflare has lot of tools for handling bot traffic and many other things. It' should not impact the performance of your site. If anything it will probably increase it.

What is phpbb cron and how do you fix it or check it?

Btw, my webhost sent me this article about increasing the size of the TMP file so it doesn't get full again.

https://support.cpanel.net/hc/en-us/art ... filesystem

Do you recommend doing that? Is there a better way?

Their email to me:

We hope this message finds you well. Our escalated support team has successfully worked on your recent ticket E-212895 and resolved the reported issue. We sincerely apologize for any inconvenience this may have caused and appreciate your patience throughout this process.

Resolution Details:

The reason you were unable to repair the phpbb_posts table was because /tmp was full. When /tmp is full you will be unable to run repair on a database. To fix this, I just resized /tmp. This process is outlined here: https://support.cpanel.net/hc/en-us/art ... filesystem. After correcting the issue with /tmp I just ran mysqlcheck on the database. If you run into this issue again, please reach out to support so that we can take a look.

As for what you should do moving forward, if the session files were created by bots then setting up Cloudflare would help mitigate the requests to the site. It is recommended that you work with your site developer to determine what the best actions would be to make sure the session table does not fill up. Otherwise, I would recommend reaching out to phpBB forum for assistance.

[thecoalman]

The easiest way to tell if cron is running correctly is if the emai notifications are being sent, specifically the notifications.

Under server settings is "Run periodic tasks from system cron:" If this is set to yes then you need to set up cron job on the server to run phpBB's cron task. If it's set to no then phpBB will handle it internally.

Do you recommend doing that?

What I would recommend is fixing why your sessions table is so large to begin with.

[WWu777]

The easiest way to tell if cron is running correctly is if the emai notifications are being sent, specifically the notifications.

Under server settings is "Run periodic tasks from system cron:" If this is set to yes then you need to set up cron job on the server to run phpBB's cron task. If it's set to no then phpBB will handle it internally.

Do you recommend doing that?

What I would recommend is fixing why your sessions table is so large to begin with.

I saw that setting in ACP: "Run periodic tasks from system cron". What is that? Is it better to set it to yes or no? What's the tradeoff? Thanks.

[thecoalman]

Th phpBB cron performs task that need to be run periodically like cleaning up the sessions table.

If you have it set to yes you need to use sytem cron task to run it. If you set it to no then it's by user activity, as long as you have active forum you can use no.

[WWu777]

Th phpBB cron performs task that need to be run periodically like cleaning up the sessions table.

If you have it set to yes you need to use sytem cron task to run it. If you set it to no then it's by user activity, as long as you have active forum you can use no.

So it's best to have it set to no then? I set it to yes before accidentally. Will setting it to no reduce load on the sessions table? How often does it clear the sessions table?

Also is it better to enable this? Should I disable it too?

Enable GZip compression

[thecoalman]

Yes or no are the same thing as far as functionality. The only difference is yes requires you set up system cron job to run it. No is the easier option. If you set it to no it will take care of it itself.

If you have it set to yes and never set up cron job on server that is why sessions table is so big. You may also want to see if the file cache/production/queue,php is huge as in many MB's. If so delete it, it's loaded up with lot of old notifications because they were never sent. If you don;t do that they will get sent when you set the server cron task to no.

Enable GZip compression

This compresses files before sending them to browser, it may already be enabled through php configuration. Go to the php information page in phpBB's ACP and see if "gzip compression" is enabled. If it's enabled leave the phpBB setting alone. If there is no reference or it is not enabled you can set it to yes. I'd wait to figure out if you have current issue fixed because enabling compressions increases server load.

[P_I]

The default phpBB settings:

Screenshot 2024-11-20 064809.png

Unless you have a compelling reason to change the default I would use the default.

For the GZip compression, check whether your server already performs compression. You can use HTTP Compression Test / WhatsMyIP.org as an example.

To the best of my knowledge servers are generally configured with compression enabled so there is no need to turn in on within the phpBB ACP settings.

[WWu777]

Yes or no are the same thing as far as functionality. The only difference is yes requires you set up system cron job to run it. No is the easier option. If you set it to no it will take care of it itself.

If you have it set to yes and never set up cron job on server that is why sessions table is so big. You may also want to see if the file cache/production/queue,php is huge as in many MB's. If so delete it, it's loaded up with lot of old notifications because they were never sent. If you don;t do that they will get sent when you set the server cron task to no.

Enable GZip compression

This compresses files before sending them to browser, it may already be enabled through php configuration. Go to the php information page in phpBB's ACP and see if "gzip compression" is enabled. If it's enabled leave the phpBB setting alone. If there is no reference or it is not enabled you can set it to yes. I'd wait to figure out if you have current issue fixed because enabling compressions increases server load.

Ok I set the system cron to no. Hope that will prevent the overload from happening again.

Where is the path to: cache/production/queue.php ? How do I check it?

Also should I delete the search index too? Doesn't that take up a lot of space in the SQL database file? Do all the types of search index take up a lot of space? I see in the ACP that there are several types.

Thanks.

[WWu777]

The default phpBB settings:
Screenshot 2024-11-20 064809.png
Unless you have a compelling reason to change the default I would use the default.

For the GZip compression, check whether your server already performs compression. You can use HTTP Compression Test / WhatsMyIP.org as an example.

To the best of my knowledge servers are generally configured with compression enabled so there is no need to turn in on within the phpBB ACP settings.

Thanks. I just tested my site with that link. It said my forum was compressed because I enabled gzip compression in the ACP. However when I tested my domain name it said it was not compressed. That means my server does not automatically compress my entire site? How do I make my server compress everything? What do I look for in the cpanel? Thanks.

[thecoalman]

Where is the path to: cache/production/queue.php ? How do I check it?

Largely irrelevant now. They have probably already been sent.

Also should I delete the search index too? Doesn't that take up a lot of space in the SQL database file? Do all the types of search index take up a lot of space? I see in the ACP that there are several types.

Not unless you are going to disable the search feature.

How do I make my server compress everything? What do I look for in the cpanel?

Disable it in phpBB's ACP first. You'll find it under MultiPHP INI Editor. On the Basic Mode tab down the bottom enable zlib.output_compression.