XF 1.5 This error just happened...

[Wildcat Media]

Haven't done anything for months on the server, but this morning we're greeted with this right on the forum home page, and of course the XF error log is full of these. Yet I can get into the Admin side with no problem.

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : - library/Zend/Db/Statement/Mysqli.php:214

#0 /usr/www/users/xxxx/yyyyyy/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /usr/www/users/xxxx/yyyyyy/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /usr/www/users/xxxx/yyyyyy/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /usr/www/users/xxxx/yyyyyy/library/XenForo/Session.php(856): Zend_Db_Adapter_Abstract->insert('xf_session', Array)
#4 /usr/www/users/xxxx/yyyyyy/library/XenForo/Session.php(574): XenForo_Session->saveSessionToSource('9cdaa536e01df7a...', false)
#5 /usr/www/users/xxxx/yyyyyy/library/XenForo/Controller.php(414): XenForo_Session->save()
#6 /usr/www/users/xxxx/yyyyyy/library/XenForo/Controller.php(358): XenForo_Controller->updateSession(Object(XenForo_ControllerResponse_Redirect), 'XenForo_Control...', 'Index')
#7 /usr/www/users/xxxx/yyyyyy/library/XenForo/FrontController.php(358): XenForo_Controller->postDispatch(Object(XenForo_ControllerResponse_Redirect), 'XenForo_Control...', 'Index')
#8 /usr/www/users/xxxx/yyyyyy/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#9 /usr/www/users/xxxx/yyyyyy/index.php(13): XenForo_FrontController->run()
#10 {main}

Corrupt sessions table?

 

[Wildcat Media]

Yep...



Since it's session data, how do I fix/rebuild, or do I delete since they are a temporary/memory table?

 

[Wildcat Media]

Wait...so I fixed it. xf_session is a MyISAM table? I thought they would all be InnoDB. (This crashed table is the reason I don't like MyISAM.) I see that xf_search_index, xf_session and xf_session_admin are all MyISAM. This was a new forum install going back to November 2012, from an import from vB. Can these be converted to InnoDB safely?

 

[Chris D]

They should be MyISAM and shouldn't be change to InnoDB.

You should be able to simply truncate/empty the xf_session table to restore normality. If issues continue you may need to entirely delete and recreate the table but truncating would usually fix it.

 

[Wildcat Media]

I ran a repair on the table prior to my second post, so it is working.

On another site of mine, much lower traffic, the tables are InnoDB and work fine. (It runs on MariaDB but that should be functionally equivalent, I would think.) I suppose there is a technical reason for needing MyISAM. Which is probably over my head.

 

[Chris D]

There’s a much simpler reason they should be MyISAM and that reason is this is what we set them to when we created the database. You really should switch them back.

There should be some MEMORY tables too.

For the most part InnoDB should be ok though we still don’t recommend changing things beyond the defaults we set. More significantly the xf_search_index table should be kept as MyISAM as otherwise searching will likely just not work at all unless you’re using Enhanced Search.

 

[Wildcat Media]

We do use Enhanced Search. And the memory tables I leave as-is.

I'm thinking of what happens if we need to move or backup the database, and then restore it elsewhere. I haven't done a mysqldump in a while and forget whether or not it preserves the table types. (I'm thinking it does, though.)

 

[Chris D]

It should do by default, yes.