Fixed Delayed MySQL Queries

[Ghan_04]

So I've updated my server to MySQL 5.6, which supports InnoDB FULLTEXT indices. Unfortunately, InnoDB does NOT support the default delayed MySQL queries that Xenforo uses, and it is still trying to use them to insert data into the xf_search_index table despite having changed the appropriate setting in the Performance section of the Admin CP.
I thought perhaps that this setting was being cached somewhere, but it did not change behavior even after restarting the webserver, the MySQL server, and memcached multiple times.

Any ideas on why this still won't behave? I've converted the table back to MyISAM for now, but I'd like to change it back if possible.

 

[Jake Bunce]

The options are stored in the data registry which is served from memcache if you have that setup. Any problems with your memcache can affect the options. You can try disabling your memcache temporarily to see if that fixes the problem. Simple remove or comment the cache lines that you added to your library/config.php file.

 

[Ghan_04]

The error still occurs even after commenting out the memcache configuration settings and stopping the memcache daemon on the server. Pages load fine - it's just when there is an insert (when posting) that the error is thrown.

 

[Jake Bunce]

Can you post the exact error?

 

[Ghan_04]

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: DELAYED option not supported for table 'xf_search_index' - library/Zend/Db/Statement/Mysqli.php:77
Generated By: The Dark Wizard, Wednesday at 2:27 PM
Stack Trace
#0 /home/rpdom/public_html/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('?????REPLACE DE...')
#1 /home/rpdom/public_html/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), '?????REPLACE DE...')
#2 /home/rpdom/public_html/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('?????REPLACE DE...')
#3 /home/rpdom/public_html/library/XenForo/Search/SourceHandler/MySqlFt.php(110): Zend_Db_Adapter_Abstract->query('?????REPLACE DE...')
#4 /home/rpdom/public_html/library/XenForo/Search/SourceHandler/MySqlFt.php(82): XenForo_Search_SourceHandler_MySqlFt->_pushToIndex('('profile_post'...')
#5 /home/rpdom/public_html/library/XenForo/Search/Indexer.php(44): XenForo_Search_SourceHandler_MySqlFt->insertIntoIndex('profile_post', 17549, '', 'Test', 1360787248, 1, 17549, Array)
#6 /home/rpdom/public_html/library/XenForo/Search/DataHandler/ProfilePost.php(39): XenForo_Search_Indexer->insertIntoIndex('profile_post', 17549, '', 'Test', 1360787248, 1, 17549, Array)
#7 /home/rpdom/public_html/library/XenForo/Search/DataHandler/Abstract.php(227): XenForo_Search_DataHandler_ProfilePost->_insertIntoIndex(Object(XenForo_Search_Indexer), Array, NULL)
#8 /home/rpdom/public_html/library/XenForo/DataWriter/DiscussionMessage.php(688): XenForo_Search_DataHandler_Abstract->insertIntoIndex(Object(XenForo_Search_Indexer), Array, NULL)
#9 /home/rpdom/public_html/library/XenForo/DataWriter/DiscussionMessage.php(667): XenForo_DataWriter_DiscussionMessage->_insertOrUpdateSearchIndex()
#10 /home/rpdom/public_html/library/XenForo/DataWriter/DiscussionMessage.php(523): XenForo_DataWriter_DiscussionMessage->_indexForSearch()
#11 /home/rpdom/public_html/library/XenForo/DataWriter.php(1385): XenForo_DataWriter_DiscussionMessage->_postSave()
#12 /home/rpdom/public_html/library/RCBD/RecentStatus/DataWriter/ProfilePost.php(11): XenForo_DataWriter->save()
#13 /home/rpdom/public_html/library/XenForo/Model/UserProfile.php(243): RCBD_RecentStatus_DataWriter_ProfilePost->save()
#14 /home/rpdom/public_html/library/XenForo/ControllerPublic/Member.php(874): XenForo_Model_UserProfile->updateStatus('Test')
#15 /home/rpdom/public_html/library/XenForo/FrontController.php(312): XenForo_ControllerPublic_Member->actionPost()
#16 /home/rpdom/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#17 /home/rpdom/public_html/index.php(13): XenForo_FrontController->run()
#18 {main}
Request State
array(3) {
  ["url"] => string(51) "http://www.rpdom.com/members/the-dark-wizard.1/post"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(6) {
    ["message"] => string(4) "Test"
    ["_xfToken"] => string(53) "1,1360787244,1be65f76b50404e7b4e778c7db04cf57a5c187e0"
    ["return"] => string(1) "1"
    ["_xfRequestUri"] => string(7) "/forum/"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

 

[Jake Bunce]

Try this:

library/XenForo/Search/SourceHandler/MySqlFt.php

Add the red code:

	protected function _pushToIndex($record)
	{
		if (is_array($record))
		{
			$record = implode(',', $record);
		}

		if (!$record)
		{
			return;
		}

		$this->_getDb()->query('
			REPLACE ' . ($this->_isRebuild OR !XenForo_Application::get('options')->enableInsertDelayed ? '' : 'DELAYED') . ' INTO xf_search_index
				(content_type, content_id,
				title, message, metadata,
				item_date, user_id, discussion_id)
			VALUES
				' . $record
		);
	}

There are three queries in the software that use DELAYED. This is one of them. It is also the only one that doesn't reference that option. Probably a bug. I will move this thread to the bug forum. But first I want to make sure this code fix works for you.

 

[Ghan_04]

Bah. I had actually grep'd around in the source files looking for DELAYED, but the ones that I saw had this kind of options reference. I didn't know there was one missing it.

Yes - this DOES fix the issue. I'm now fully InnoDB and there is no issue posting.

 

[Brent W]

Is this something that could be modified by a plugin? I'd like to roll out to 5.6 and code edits make it time consuming over all my forums.

 

[Chris D]

Is this something that could be modified by a plugin? I'd like to roll out to 5.6 and code edits make it time consuming over all my forums.

I *think* it can be done by overwriting the entire XenForo_Search_SourceHandler_MySqlFt class using the search_source_create listener

But that's not much better than a code edit - well considerably less time consuming I suppose

What do you think Jake, would that work? Or is there a smarter way of doing it?

 

[Brent W]

I've been spoiled by your auto installer. Makes installing add-ons across all my sites a breeze. Hopefully your next version will support more advanced stuff like Jaxel's xenPorta.

 

[Mike]

Fixed.

 

[rdn]

Fixed.

So meaning xf_search_index can be set as Innodb now?
I still encounter the same problem when I use Innodb engine on that table.

 

[HWS]

So meaning xf_search_index can be set as Innodb now?
I still encounter the same problem when I use Innodb engine on that table.

Yes, but you also need to disable the "Insert delayed" option in your ACP.

 

[digitalpoint]

Just FYI - INSERT DELAYED is deprecated as of MySQL 5.6.6.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html

Important Change: INSERT DELAYED is now deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead. (Bug #13985071

Also, in most cases, INSERT DELAYED is going to be slower anyway...

http://dev.mysql.com/doc/refman/5.6/en/insert-delayed.html

INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

 

[Chris D]

If you require support please create a new thread in the appropriate support forum or submit a ticket from the customer area.