UBB.threads PHP Forum Software Community Forums Thread Like Summary - We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114

Hi my forum is down, any idea what it could be??
Please advice
Cheers
Morgan


We Encountered A Problem
Script: /xxxxx/xxxxx/xxxxx/forums/libs/html.inc.php
Line: 467
SQL Error: The table 'ubbt_ONLINE' is full
SQL Error: 1114

Query:
REPLACE INTO
ubbt_ONLINE
(ONLINE_DISPLAY_NAME, USER_ID, ONLINE_LAST_ACTIVITY, ONLINE_SCRIPT_NAME, ONLINE_BROWSING_FORUM, ONLINE_USER_TYPE, ONLINE_USER_IP, ONLINE_REFERER, ONLINE_AGENT, ONLINE_POST_ID, ONLINE_POST_SUBJECT)
VALUES
('Morgan aka Admin', xxxxx, 1585943868, 'portal', '', 'r', 'xxxxxxxxxxxxxx', 'http://www.britbike.com/top.html', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/11.1.2 Safari/605.1.15', 0, '')

in the general section of the ubb control panel is a setting at the bottom on how long to keep stats on who is on line.
Who's Online Timeframe
Either you have some high attacks going on or you have it set for a very high value.
The default is 10 minutes.

My 2 cents.
I am using cloud flare and mitigated a lot from it.

In 2007 with version 7.1.0, those TEMPORARY high-transaction tables were moved from disk storage to memory storage. The idea behind this was probably due to the tables being temporary, and that they had a high volume of read/writes. Memory generally is faster than physical hard disk storage, especially in 2007 when the change was made.

But it came with a major trade-off. One that I've been seeing lately. The size of tables in memory is a setting that is rarely adjusted on standard hosting servers or set to a ceiling on shared server environment. IIRC, the size is 16MB.

The ONLINE and TOPIC_VIEWS tables use VARCHAR (variable-length) fields.

When the table is in MEMORY, those VARCHAR fields are no longer variable-length. They are converted to static CHAR field max length. Due to this, a rather small table needs a lot of memory when its storage engine is set to MEMORY. Effectively, VARCHAR becomes CHAR with the MEMORY engine.

"MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length."
SOURCE: https://dev.mysql.com/doc/refman/8....-engine-characteristics-of-memory-tables


Example:
Both of the items in this screenshot are the same table.




When you hit about 5,000 rows on the static field size ONLINE table (using memory/heap storage), you will be very close to that that 16MB default maximum, and an error will be generated if attempting to store more. The same error that Morgan is having.

Moving the table to disk does not have the 16MB limitation, and the fields will only use as much as they need, rather than their maximum character width. But disk storage is generally slower than memory storage -- especially for a high-transaction table.

Attachments

If you feel that you are running in to this issue often because you have a massive forum, then you need to think about this being the correct course of action. But depending on your server disk speed, you may find that having these temporary tables remaining in memory/ram storage to be better for your forums.

If you run in to the error Morgan presented because you have your WOL (Who's Online / users currently online) set too high, for example; 2 hours, 24 hours, 48 hours...etc, then you need to rethink about what you are doing that could be causing your errors, and make the necessary correction.

For reference:

UBBdev uses MyISAM disk based for ONLINE and TOPIC_VIEWS temporary tables, because there are very few members logging in (all public discussions are free to view and download on UBBDEV). The topic views are rarely updated until a new post is created, or someone logs in to trigger their updates. It also gets a ton of traffic and spiders, which sometimes makes the ONLINE list very large.

My larger UBB.threads forums, are all stock database settings of MEMORY for ONLINE and TOPIC_VIEWS temporary tables. I have had zero issues with their stock settings. But I also keep their ONLINE time frame to a reasonable 10 minutes to 60 minutes. Never had an issue.

Its rare that you would want to make this setting change. But now it is documented here on UBBCentral.com for others to review and adjust as they feel are needed.


EDIT:
Further research takes me to a discussion over here:
https://dba.stackexchange.com/quest...o-use-the-memory-storage-engine-in-mysql

Since I only came in to working as an official developer of UBB.threads in 2015 (beginning with version 7.5.9), I believe we may need to revisit the need for having these two tables in memory storage, as was decided in 2007 for UBB.threads 7.1.0... from the days of MySQL 4.x/5.1, PHP 4.x, and 5400 RPM HDDs being relevant.

My 2 cents I would block
SemrushBot
AhrefsBot
They don't give you anything as far as a user search,They provide statistical data to subscribers only.
and they are over whelming your site.
According to the web they both honor the robots.txt file.
Or block them in cloudflare.

Now all engines reads MyISAM

if you are getting slammed or being DDoS attacked, you can move your ONLINE and TOPIC_VIEWS temporary tables from heap (memory) to disk.

--- Set temp data table engines: disk ---


DEFAULT SETTINGS used since 2007 (UBB.threads from version 7.1.0 through 7.7.4)
--- Set temp data table engines: heap (memory) ---