[Forum] Error #1114 - The table 'session' is full

[SpookyDan]

I need some additional assistance figuring this one out. I have been running my forum at www.urbanatrophy.com for many years and keeping it up to date with no issues until now really. All of the sudden my session table is filling up daily and crashing my VB.

This is the exact error:
Database error in vBulletin 4.2.0:


Invalid SQL:


INSERT IGNORE INTO session
(sessionhash, userid, host, idhash, lastactivity, location, styleid, languageid, loggedin, inforum, inthread, incalendar, badlocation, useragent, bypass, profileupdate, apiclientid, apiaccesstoken)
VALUES
('ef844440d15a103124f6aed5a30e6605', 1, '70.91.82.217', '5acaaf45a6d463bb925cef9241c14715', 1340196757, '/forum/', 0, 0, 1, 0, 0, 0, 0, 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.56 Safari/536.5', 0, 0, 0, '');


MySQL Error : The table 'session' is full
Error Number : 1114
Request Date : Wednesday, June 20th 2012 @ 05:52:37 AM
Error Date : Wednesday, June 20th 2012 @ 05:52:37 AM
Script : http://www.urbanatrophy.com/forum/
Referrer :
IP Address : 70.91.82.217
Username : Dan
Classname : vB_Database
MySQL Version : 4.1.14

I have tried these things with no luck:
-emptying session table (lasts a day or so)
-set session timeout to 300
-reverted all tables
-checked logs and hourly cleanup #1 and #2 are running as scheduled

My forum has very little real traffic and some robots daily.

Any help would be greatly appreciated.

Thanks

[borbole]

Can you try to delete the session table altogether and then recreate it again? If you will get that full message then talk to your host about it so they can check things on their end.

[SpookyDan]

Can you try to delete the session table altogether and then recreate it again? If you will get that full message then talk to your host about it so they can check things on their end.

Can you give me a quick rundown on how to do that? If so I will give it a try.

[borbole]

Can you give me a quick rundown on how to do that? If so I will give it a try.

Simply drop the session table from the database then take its structure from the mysql-schema.php file inside the install folder and then run the query that adds the table at the db that your forum resides on. Hope it helps.

[SpookyDan]

Is this what I need to run in SQL:

CREATE TABLE " . TABLE_PREFIX . "session (
sessionhash CHAR(32) NOT NULL DEFAULT '',
userid INT UNSIGNED NOT NULL DEFAULT '0',
host CHAR(15) NOT NULL DEFAULT '',
idhash CHAR(32) NOT NULL DEFAULT '',
lastactivity INT UNSIGNED NOT NULL DEFAULT '0',
location CHAR(255) NOT NULL DEFAULT '',
useragent CHAR(100) NOT NULL DEFAULT '',
styleid SMALLINT UNSIGNED NOT NULL DEFAULT '0',
languageid SMALLINT UNSIGNED NOT NULL DEFAULT '0',
loggedin SMALLINT UNSIGNED NOT NULL DEFAULT '0',
inforum SMALLINT UNSIGNED NOT NULL DEFAULT '0',
inthread INT UNSIGNED NOT NULL DEFAULT '0',
incalendar SMALLINT UNSIGNED NOT NULL DEFAULT '0',
badlocation SMALLINT UNSIGNED NOT NULL DEFAULT '0',
bypass TINYINT NOT NULL DEFAULT '0',
profileupdate SMALLINT UNSIGNED NOT NULL DEFAULT '0',
apiclientid INT UNSIGNED NOT NULL DEFAULT '0',
apiaccesstoken VARCHAR(32) NOT NULL DEFAULT '',
isbot TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (sessionhash),
KEY last_activity USING BTREE (lastactivity),
KEY user_activity USING BTREE (userid, lastactivity),
KEY guest_lookup (idhash, host, userid),
KEY apiaccesstoken (apiaccesstoken)
) ENGINE = $sessionengine

I tried and got this error.

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" . TABLE_PREFIX . "session (
sessionhash CHAR(32) NOT NULL DEFAULT '',
user' at line 1

[Wayne Luke]

Well that is PHP and not a complete SQL Query.

There is no reason to delete the Session table and Recreate it. That won't resolve any issue that using truncate table session; would resolve.

Your most likely problem is that your scheduled tasks are running for some reason. There are a couple reasons for this..

1) The system is set on a single task because it can't complete. Usually due to a database error. Tasks have their errors suppressed on the front-end. Check all your tables to see if any are crashed. If there are crashed tables, repair them. Eventually all your tasks will finish running.

2) You removed the CRONIMAGE variable from your footer template. This needs to be there for the system to operate properly. The code looks like this:

    <!-- Do not remove cronimage or your scheduled tasks will cease to function -->
    {vb:raw cronimage}
    <!-- Do not remove cronimage or your scheduled tasks will cease to function -->

Other reasons for the Session table to fill include not having large enough memory buffers configured in MySQL. The Session table is stored in memory and never written to disk. You can try changing the type to MyISAM or INNODB but this may have adverse affects on the speed of your site. Also if you have more than 65,535 concurrent users for some reason, it can fill the table.

The only times you want to delete and recreate a table is if the FRM file is missing, the table is crashed and unrecoverable or it is missing completely.

[SpookyDan]

I ran a check and came back with these:
Problems with indexes of table `activitystream`
More than one INDEX key was created for column `typeid`
Problems with indexes of table `contentread`
PRIMARY and INDEX keys should not both be set for column `contenttypeid`
Problems with indexes of table `post`
More than one INDEX key was created for column `threadid`
More than one INDEX key was created for column `userid`
Problems with indexes of table `searchlog`
More than one INDEX key was created for column `userid`
Problems with indexes of table `socialgroupmember`
PRIMARY and INDEX keys should not both be set for column `groupid`
Problems with indexes of table `thread`
More than one INDEX key was created for column `forumid`
Problems with indexes of table `userlist`
More than one INDEX key was created for column `relationid`

I repaired these tables. Will wait and see if that works.

As for the CRONIMAGE I still see that in the footer template so we are good there.

- - - Updated - - -

Just got the full error again so none of the above has worked.

[SpookyDan]

I manually emptied my session table this morning and it is already up to 4.2 TB! How is this filling up so fast on such an inactive board.

[Wayne Luke]

You'll need to open a support ticket and provide access to the Admin CP and the Database. https://www.vbulletin.com/go/techsupport

[Zachery]

Just incase anyone else comes across it, this is not a vBulletin issue, there is no way for our vBulletin session table with 0 entries to be that large, or even with less than several hundred thousand entries. There is something going on with the webhost.