XF 1.2 Mysqli prepare error: Table './xf_forums/xf_session' is marked as crashed and should be repaired

[Zantetsuken]

Help! We've just started getting this error on the site which is preventing the entire forum from working. Any help appreciated.

Mysqli prepare error: Table './xf_forums/xf_session' is marked as crashed and should be repaired

Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 574
Zend_Db_Adapter_Abstract->insert() in XenForo/Session.php at line 760
XenForo_Session->saveSessionToSource() in XenForo/Session.php at line 501
XenForo_Session->save() in XenForo/Controller.php at line 412
XenForo_Controller->updateSession() in XenForo/Controller.php at line 356
XenForo_Controller->postDispatch() in XenForo/FrontController.php at line 344
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
XenForo_FrontController->run() in /var/www/vhosts/precursorgames.com/httpdocs/forums/index.php at line 13

 

[Paul B]

Truncate or empty the xf_session table using phpMyAdmin, if it can't be repaired via your DB module in cPanel.

 

[Daniel Hood]

Any idea what causes this? I've gotten it in the past. Fixed it no problem but it's odd.

 

[Veer]

I've also gotten it, almost every 2nd week I got this, and always have to repair the table to fix it

 

[FredC]

I get this on occasion also. I've concluded it's a memory issue due to my server running a backup script. Which is memory intense. Of course that's my conclusion and not been officially verified.

 

[Zantetsuken]

Truncate or empty the xf_session table using phpMyAdmin, if it can't be repaired via your DB module in cPanel.

Thanks, I'm not even seeing any db's listed in the cp (actually it's the plesk panel which frankly I'm hating so far...) I've never used phpMyAdmin, how would I go about setting that up to see the XF db's?

 

[Paul B]

I have never used Plesk so I'm not familiar with it at all.

 

[Luke F]

MyISAM tables crashing seemingly at random indicates aggressive bitrot in my experience, e.g. from vibration on desktop-grade hard disks

 

[Zantetsuken]

I have never used Plesk so I'm not familiar with it at all.

Ok thanks, can you let me know if it's possible to bring save the DB locally to my laptop and repair then re-upload? If so, what's the path to the db?

 

[Paul B]

I don't know what the path is to your DB.

You will need to ask your host for help.

 

[HWS]

Since xf_session is a memory table I assume it has something to do with how server providers handle memory in a shared hosting environment.

You may try to convert xf_session into a InnoDB table (or MyISAM) to see if that solves the issue. For smaller forums the conversion should not result in a slower website.

 

[Paul B]

xf_session isn't a memory table, it is MyISAM.

 

[HWS]

xf_session isn't a memory table, it is MyISAM.

Ah, yes, I see. xf_session_activity is memory. In that case I would try if it helps converting it to memory.

 

[HWS]

Thanks, I'm not even seeing any db's listed in the cp (actually it's the plesk panel which frankly I'm hating so far...) I've never used phpMyAdmin, how would I go about setting that up to see the XF db's?

Plesk uses phpMyAdmin as it's database admin interface.
So, if you click at "manage database" you are automatically transfered to a phpMyAdmin interface where you can truncate the table.

 

[Zantetsuken]

all sorted now - thanks for the help. It turns out the webhost hadn't given me full access to the panel so I couldn't see the DB options. Anyways got that resolved and was able to truncate.

 

[ProCom]

I've recently started getting these errors. The fix is relatively easy, I just go into PhpMyAdmin and repair the xf_session table.

The first time I figured it was just a fluke, but then it happened again about 4 days later. Now I'm worried that it will keep happening since the error pretty much kills my forum until I can login and repair the table.

So, any other ideas what might be causing this problem?

Some background: I never had the problem before, but a month ago I moved to Linode. Then, about a week ago, I had to change some of my server settings to resolve an upload issue as mentioned here: https://xenforo.com/community/threads/attachment-upload-stuck-100.18087/#post-1046824

I changed the following:

php.ini
memory_limit = from 32M to 158M

my.cnf
max_allowed_packet=268435456 to max_allowed_packet=64M
wait_timeout=10 to 45​

Is there anything in those settings that might contribute to this xf_session being corrupted / crashed / error?

Any other ideas on how I can prevent this in the future?

 

[Paul B]

It's typically due to an unstable server or MySQL shutting down unexpectedly.

Check with your host whether there have been any unscheduled restarts.

 

[ProCom]

Thanks @Brogan !

My host (Linode) doesn't show any restarts at all (unscheduled or otherwise). I host about five XF forums on the same VPS, and the problem seems to happen to one and then later to another and later to another. I haven't seen multiple session tables crash all at the same time. Wouldn't more than one site have this problem if the MySQL server shut down vs. just one at a time?

Any chance that one of the changes I made in php.ini or my.cnf could be causing the session table to crash?

php.ini
memory_limit = from 32M to 158M

my.cnf
max_allowed_packet=268435456 to max_allowed_packet=64M
wait_timeout=10 to 45

Thanks in advance for any help you can provide!

 

[Paul B]

Those php.ini or my.cnf changes are unlikely to be related.

If it's happening to multiple sites on the same server then the common factor is the server and it's going to require monitoring and investigation to determine the underlying cause.

If you have the option, it may be worth implementing Memcached and opting in to session caching.
It may help.

 

[ProCom]

Thanks @Brogan ! My support tech guys did more digging and found this:

"While investigating MYSQL issue on the server it seems mysql service on the server is might goes down due to the insufficient memory resources.

I have found that server has around 2GB memory and there is not enough memory resources available on the server which caused mysql service stopped working.

==
total used free shared buff/cache available
Mem: 1999 674 83 62 1241 1197
Swap: 511 281 230
==
server kernel: Out of memory: Kill process 32696 (mysqld) score 200 or sacrifice child
server kernel: Killed process 32696 (mysqld) total-vm:1430364kB, anon
==

You can increased swap memory on your server and limit mysql connection in your my.cnf from current value of 2000 for further check. "​

I have no idea how max_user_connections & max_connections got set to 2,000!?!

Do you think that's the issue, or swap memory, or that I just need to bump up my Linode VPS from 2GB to 4GB?