Previous Thread
Next Thread
Print Thread
Hop To
We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
#263716 04/03/2020 4:00 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
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, '')


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263717 04/03/2020 4:08 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
Humm it is backup now.
But what do you have set for time for WOL


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263718 04/03/2020 4:13 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
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?

Last edited by Morgan; 04/03/2020 4:14 PM.

Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263720 04/03/2020 4:27 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
if you are getting slammed or being DoSed, you can move your ONLINE and TOPIC_VIEWS temporary tables from heap (memory) to disk.

--- Set temp data table engines: disk ---
SQL Query
alter table ubbt_ONLINE ENGINE=MyISAM
alter table ubbt_TOPIC_VIEWS ENGINE=MyISAM


DEFAULT SETTING used since 2007 (UBB.threads from version 7.1.0 through 7.7.4)
--- Set temp data table engines: heap (memory) ---
SQL Query
alter table ubbt_ONLINE ENGINE=heap
alter table ubbt_TOPIC_VIEWS ENGINE=heap


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263721 04/03/2020 4:28 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
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.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
1 member likes this: isaac
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Ruben #263722 04/03/2020 4:31 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
Originally Posted by Ruben
Either you have some high attacks going on or you have it set for a very high value.

The default is 10 minutes.

Absolutely agree with both of these thumbsup


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
isaac #263723 04/03/2020 4:37 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
Originally Posted by isaac
if you are getting slammed or being DoSed, you can move your temp WOL and TOPICVIEWS tables from heap (memory) to disk.

--- Set temp data table engines: disk ---
SQL Query
alter table ubbt_ONLINE ENGINE=MyISAM
alter table ubbt_TOPIC_VIEWS ENGINE=MyISAM


DEFAULT SETTING used since 2007 (UBB.threads from version 7.1.0 through 7.7.4)
--- Set temp data table engines: heap (memory) ---
SQL Query
alter table ubbt_ONLINE ENGINE=heap
alter table ubbt_TOPIC_VIEWS ENGINE=heap

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?


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263724 04/03/2020 4:38 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
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 ?


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263725 04/03/2020 4:43 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
As far as the suggestion you can run it from phpmyadmin or from the ubb cp database tools.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Ruben #263726 04/03/2020 4:44 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
Originally Posted by Ruben
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?

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.

[Linked Image]


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.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
1 member likes this: Gizmo
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
isaac #263727 04/03/2020 4:50 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
Originally Posted by isaac
Originally Posted by Ruben
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?

In 2007 with version 7.1.0, the 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. Ram generally is faster than physical hard disk storage, especially in 2007 when the change was made.

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


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263728 04/03/2020 4:52 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
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 ---


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263729 04/03/2020 4:55 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
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


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Ruben #263730 04/03/2020 5:03 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
Originally Posted by Ruben
So for future builds is this going to change from memory?
should we all follow this advise and change it now.?

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.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
1 member likes this: Gizmo
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263731 04/03/2020 5:04 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
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


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Ruben #263732 04/03/2020 5:10 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
Originally Posted by Ruben
Okay I read the expanded version and I see the size limitations of 16MB and or 5,000 rows
So if we don't exceed this limits should we still alter the tables for the future?

i think you and I replied at the same time smile 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 DoSed, 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


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263733 04/03/2020 5:11 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
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
Quote
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 ---


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263734 04/03/2020 5:14 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
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.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263736 04/03/2020 5:18 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
Isaac

it reads now
ubbt_TOPIC_VIEWS MEMORY
ubbt_ONLINE MEMORY

all others MyISAM


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263737 04/03/2020 5:22 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
Now all engines reads MyISAM

Last edited by Morgan; 04/03/2020 5:22 PM.

Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263738 04/03/2020 5:22 PM
Joined: Apr 2004
Posts: 1,689
Likes: 49
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,689
Likes: 49
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.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
1 member likes this: Gizmo
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263739 04/03/2020 5:36 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
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.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
1 member likes this: isaac
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263740 04/03/2020 5:40 PM
Joined: Jun 2006
Posts: 760
Likes: 5
Morgan Offline OP
Old Hand
OP Offline
Old Hand
Joined: Jun 2006
Posts: 760
Likes: 5
I just read Isaacs reference above so I set the engines back to MEMORY

Ruben, How do I block
SemrushBot
AhrefsBot


Morgan Johansson
BritBike Forum
http://www.britbike.com/forums/ubbthreads.php
Re: We Encountered A Problem: ubbt_ONLINE is full. SQL Error 1114
Morgan #263741 04/03/2020 5:51 PM
Joined: Dec 2003
Posts: 6,130
Likes: 20
Offline
Joined: Dec 2003
Posts: 6,130
Likes: 20
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.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers

Link Copied to Clipboard
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Ubb 7.7.4 Gallery problems
by jjjjj - 05/25/2020 2:35 PM
7.7.4 error after php upgrade to php 7.0
by Ruben - 05/25/2020 1:10 PM
7.7.4 move group all, fails with a error
by Ruben - 05/24/2020 3:07 PM
Grid lines in forum topics list
by Steve C - 05/23/2020 12:09 PM
Who's Online Now
0 members (), 89 guests, and 38 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Amusing Terain Scenics
Amusing Terain Scenics
by isaac, August 19
Sky places
Sky places
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 7.7.4