• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Fixed Delayed MySQL Queries

Ghan_04

Active member
#1
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

XenForo moderator
Staff member
#2
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

Active member
#3
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.
 

Ghan_04

Active member
#5
Code:
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

XenForo moderator
Staff member
#6
Try this:

library/XenForo/Search/SourceHandler/MySqlFt.php

Add the red code:

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

Active member
#7
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

Well-known member
#8
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

XenForo developer
Staff member
#9
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

Well-known member
#10
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.
 

digitalpoint

Well-known member
#14
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.
 
#15
can some one tell me how to fix this Error

The following error occurred:
Mysqli prepare error: DELAYED option not supported for table 'xf_search_index'
  1. Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
  2. Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
  3. Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
  4. Zend_Db_Adapter_Abstract->query() in XenForo/Search/SourceHandler/MySqlFt.php at line 129
  5. XenForo_Search_SourceHandler_MySqlFt->_pushToIndex() in XenForo/Search/SourceHandler/MySqlFt.php at line 101
  6. XenForo_Search_SourceHandler_MySqlFt->insertIntoIndex() in XenForo/Search/Indexer.php at line 44
  7. XenForo_Search_Indexer->insertIntoIndex() in XenForo/Search/DataHandler/Post.php at line 73
  8. XenForo_Search_DataHandler_Post->_insertIntoIndex() in XenForo/Search/DataHandler/Abstract.php at line 275
  9. XenForo_Search_DataHandler_Abstract->insertIntoIndex() in XenForo/DataWriter/DiscussionMessage.php at line 759
  10. XenForo_DataWriter_DiscussionMessage->_insertOrUpdateSearchIndex() in XenForo/DataWriter/DiscussionMessage.php at line 738
  11. XenForo_DataWriter_DiscussionMessage->_indexForSearch() in XenForo/DataWriter/DiscussionMessage.php at line 584
  12. XenForo_DataWriter_DiscussionMessage->_postSave() in XenForo/DataWriter.php at line 1423
  13. XenForo_DataWriter->save() in XenForo/DataWriter/Discussion.php at line 491
  14. XenForo_DataWriter_Discussion->_saveFirstMessageDw() in XenForo/DataWriter/Discussion.php at line 435
  15. XenForo_DataWriter_Discussion->_postSave() in XenForo/DataWriter.php at line 1423
  16. XenForo_DataWriter->save() in XenForo/ControllerPublic/Forum.php at line 830
  17. XenForo_ControllerPublic_Forum->actionAddThread() in EWRporta2/ControllerPublic/Forum.php at line 22
  18. EWRporta2_ControllerPublic_Forum->actionAddThread() in XenForo/FrontController.php at line 351
  19. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
  20. XenForo_FrontController->run() in /home/icekodio/anonymouse.website/index.php at line 13
 

Chris D

XenForo developer
Staff member
#16
If you require support please create a new thread in the appropriate support forum or submit a ticket from the customer area.