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

Fixed Delayed MySQL Queries

Discussion in 'Resolved Bug Reports' started by Ghan_04, Feb 12, 2013.

  1. Ghan_04

    Ghan_04 Active Member

    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.
    jeffwidman likes this.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
  3. Ghan_04

    Ghan_04 Active Member

    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.
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Can you post the exact error?
  5. Ghan_04

    Ghan_04 Active Member

    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"
  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Try this:


    Add the red code:

    	protected function _pushToIndex($record)
    		if (is_array($record))
    			$record = implode(',', $record);
    		if (!$record)
    			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)
    				' . $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.
    p4guru likes this.
  7. Ghan_04

    Ghan_04 Active Member

    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.
    MattW and Jake Bunce like this.
  8. Brent W

    Brent W Well-Known Member

    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.
  9. Chris D

    Chris D XenForo Developer Staff Member

    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?
  10. Brent W

    Brent W Well-Known Member

    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.
    Chris D likes this.
  11. Mike

    Mike XenForo Developer Staff Member

  12. RoldanLT

    RoldanLT Well-Known Member

    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.
  13. HWS

    HWS Well-Known Member

    Yes, but you also need to disable the "Insert delayed" option in your ACP.
    jeffwidman and RoldanLT like this.
  14. digitalpoint

    digitalpoint Well-Known Member

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

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

    jeffwidman, Xon, Liam W and 3 others like this.
  15. Icekodi

    Icekodi New Member

    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
  16. Chris D

    Chris D XenForo Developer Staff Member

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

Share This Page