UBB.threads PHP Forum Software Community Forums UBB.threads Bug Reports 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, '')

Humm it is backup now.
But what do you have set for time for WOL

Ruben I see in the error logs that it was loads of error messages the file is 1,381,033 bytes all with about same message.
yes its working again. but I received a message from my host that there was a high LFD load for five minutes

what is "WOL" please explain and what is a good value for WOL?

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) ---

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.

Absolutely agree with both of these

I understand what this does but in what scenario would you use this option.
Meaning site is slow have a lot of valid visitors or what?

Why is it using memory to begin with?

Ruben
First my WOL was set to 240min
I have now set it to 10 min

Isaac, if I need to follow your instruction I am not sure how to.. or in which sequense
are you saying that I should go to
Control Panel >> Database Tools >> SQL command and execute queries ?

As far as the suggestion you can run it from phpmyadmin or from the ubb cp database tools.

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.

So for future builds is this going to change from memory?
should we all follow this advise and change it now.?

Isaac

so I would just add these two lines

alter table ubbt_ONLINE ENGINE=MyISAM
alter table ubbt_TOPIC_VIEWS ENGINE=MyISAM

into Control Panel >> Database Tools >> SQL command and execute
all done in one click and then it would be on disk as in
--- Set temp data table engines: disk ---

I've just updated my above post with a lot more information.

https://www.ubbcentral.com/forums/u...6/re-we-encountered-a-problem#Post263726

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.

Okay I read the expanded version and I see the size limitations of 16MB and or 5,000 row so if we don't exceed this limits should we still alter the tables for the future?

Damn we posted on top of each other

i think you and I replied at the same time as my reply above yours gives an example of why you'd want to alter your temporary table from memory to disk.

I'll post more if you want more reading to whats going on as it relates to the temporary table engine type. But as for UBB.threads, I have not completely explored other options since discovering this issue late last year on a super-forum that was being DDoS attacked, and I had a short time to research what was directly triggering database error.

EDIT:
I've updated my previous post with source/further reading
https://www.ubbcentral.com/forums/u...6/re-we-encountered-a-problem#Post263726

This is the first time I noticed this error message
way in the past I used both 24h, 8h and for a pretty long time 4h WOL.
Now that I set the time frame to only 10 min then it would help or am I understanding it wrongly.
If 10 min does not help and I get another error then I should do as it reads below correct??


so I would just add these two lines
into Control Panel >> Database Tools >> SQL command and execute
all done in one click and then it would be on disk as in
--- Set temp data table engines: disk ---

Morgan, run each line separately from the Control Panel > Database Tools > SQL Command tool.
Review the Control Panel > Database Tools > Information page for each of those two tables to see what engine they are currently using.

Isaac

it reads now
ubbt_TOPIC_VIEWS MEMORY
ubbt_ONLINE MEMORY

all others MyISAM

Now all engines reads MyISAM

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.

I just read Isaacs reference above so I set the engines back to MEMORY

Ruben, How do I block
SemrushBot
AhrefsBot

According to the web they both honor the robots.txt file.
But they do state it may take a few days to honor.
If you google each bot there is reference on how to do so.

Also as I recall you are using cloudflare. which can be done in the firewall rules.

I love cloudflare.