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

Fixed MariaDB 10.1.x - Incorrect integer value: '' for column 'content_id' at row 1

Discussion in 'Enhanced Search Resolved Bugs' started by maxicep, Dec 28, 2015.

  1. maxicep

    maxicep Active Member

    That bug is occurred on moderation query in xenforo system.

    We are getting this errors after switch to mariadb 10.1.x and Moderators can't delete the posts or threads (some posts or threads still deleting without problem but most of threads /posts can't delete now. )

    Code:
    Server Error Log
    Error Info
    Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Incorrect integer value: '' for column 'content_id' at row 1 - library/Zend/Db/Statement/Mysqli.php:214
    Generated By: Admin, 49 minutes ago
    
    This is the output of post/thread delete page.
    Code:
    Server Error
    
    Mysqli statement execute error : Incorrect integer value: '' for column 'content_id' at row 1
    
    Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
    Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
    Zend_Db_Adapter_Abstract->query() in XenES/Model/Elasticsearch.php at line 301
    XenES_Model_Elasticsearch->logFailedIndex() in XenES/Search/SourceHandler/ElasticSearch.php at line 1012
    XenES_Search_SourceHandler_ElasticSearch->_logFailedIndex() in XenES/Search/SourceHandler/ElasticSearch.php at line 113
    XenES_Search_SourceHandler_ElasticSearch->deleteFromIndex() in XenForo/Search/Indexer.php at line 105
    XenForo_Search_Indexer->deleteFromIndex() in XenForo/Search/DataHandler/Thread.php at line 85
    XenForo_Search_DataHandler_Thread->_deleteFromIndex() in XenForo/Search/DataHandler/Abstract.php at line 315
    XenForo_Search_DataHandler_Abstract->deleteFromIndex() in XenForo/DataWriter/Discussion.php at line 725
    XenForo_DataWriter_Discussion->_deleteFromSearchIndex() in XenForo/DataWriter/Discussion.php at line 790
    XenForo_DataWriter_Discussion->_postDelete() in XenForo/DataWriter.php at line 1793
    XenForo_DataWriter->delete() in XenForo/Model/InlineMod/Thread.php at line 219
    XenForo_Model_InlineMod_Thread->deleteThreads() in XenForo/ControllerPublic/InlineMod/Thread.php at line 44
    XenForo_ControllerPublic_InlineMod_Thread->actionDelete() in XenForo/FrontController.php at line 351
    XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
    XenForo_FrontController->run() in /home/nginx/domains/domain.com/public/forum/index.php at line 13

    In my researches, If a PHP script tries to set a string value, such as an empty string, into a MySQL integer data type or column, you will likely see this error message:
    Incorrect integer value ” for column ‘name’ at row 1
    Solution is looking that edit mysql server settings (my.cnf) and apply these commands but it doesn't fix the problem now. (sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES)

    Edit:
    I have tried to Disable plugins one by one and it occur when enabled your "Xenforo Enhanced Search" plugin. So, problem should be related with it.
     
    Last edited: Dec 28, 2015
  2. maxicep

    maxicep Active Member

    I think, this problem related with this query on your xenforo enhanced search feature

    library/XenES/Model/Elasticsearch.php

    Code:
            $this->_getDb()->query("
                INSERT INTO xf_es_search_failed
                    (content_type, content_id, action, data, fail_count, reindex_date)
                VALUES
                    (?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    fail_count = VALUES(fail_count),
                    reindex_date = VALUES(reindex_date)
            ", array($contentType, $contentId, $action, serialize($record), $failCount, $reindexDate));
    
            return true;
        }
    It cause the error on mysql.
     
  3. Mike

    Mike XenForo Developer Staff Member

    I'll move this to the ES bug forum, but I think it's fundamentally related to issues with your Elasticsearch setup, as it's erroring trying to log a failed index.
     
  4. maxicep

    maxicep Active Member

    Hello Mike

    What's wrong with above SQL Query you think?

    When i look the stack trace, line #4 trying to insert null and zero values. It may occurred this problem.

    Stack Trace
    Code:
    #0 /home/nginx/domains/domain.com/public/forum/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
    #1 /home/nginx/domains/domain.com/public/forum/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
    #2 /home/nginx/domains/domain.com/public/forum/library/XenES/Model/Elasticsearch.php(301): Zend_Db_Adapter_Abstract->query('\r\n\t\t\tINSERT INT...', Array)
    #3 /home/nginx/domains/domain.com/public/forum/library/XenES/Search/SourceHandler/ElasticSearch.php(1012): XenES_Model_Elasticsearch->logFailedIndex('delete', '', '', NULL, 0)
    #4 /home/nginx/domains/domain.com/public/forum/library/XenES/Search/SourceHandler/ElasticSearch.php(113): XenES_Search_SourceHandler_ElasticSearch->_logFailedIndex('delete', '', '')
    #5 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/Indexer.php(105): XenES_Search_SourceHandler_ElasticSearch->deleteFromIndex('post', Array)
    #6 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/DataHandler/Post.php(99): XenForo_Search_Indexer->deleteFromIndex('post', Array)
    #7 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/DataHandler/Abstract.php(315): XenForo_Search_DataHandler_Post->_deleteFromIndex(Object(XenForo_Search_Indexer), Array)
    #8 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(986): XenForo_Search_DataHandler_Abstract->deleteFromIndex(Object(XenForo_Search_Indexer), Array)
    #9 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(744): XenForo_DataWriter_DiscussionMessage->_deleteFromSearchIndex()
    #10 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(585): XenForo_DataWriter_DiscussionMessage->_indexForSearch()
    #11 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter.php(1423): XenForo_DataWriter_DiscussionMessage->_postSave()
    #12 /home/nginx/domains/domain.com/public/forum/library/XenForo/Model/InlineMod/Post.php(149): XenForo_DataWriter->save()
    #13 /home/nginx/domains/domain.com/public/forum/library/XenForo/ControllerPublic/InlineMod/Abstract.php(125): XenForo_Model_InlineMod_Post->deletePosts(Array, Array, NULL)
    #14 /home/nginx/domains/domain.com/public/forum/library/XenForo/ControllerPublic/InlineMod/Post.php(56): XenForo_ControllerPublic_InlineMod_Abstract->executeInlineModAction('deletePosts', Array, Array)
    #15 /home/nginx/domains/domain.com/public/forum/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_InlineMod_Post->actionDelete()
    #16 /home/nginx/domains/domain.com/public/forum/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #17 /home/nginx/domains/domain.com/public/forum/index.php(13): XenForo_FrontController->run()
    #18 {main}
     
  5. Mike

    Mike XenForo Developer Staff Member

    I will need to attempt to reproduce it. I'm mostly just pointing out that in normal situations, this code shouldn't be running at all; it's only running because the indexing action is failing within Elasticsearch.
     
  6. maxicep

    maxicep Active Member

    Yes but if elasticsearch service occuring due any problem, your elasticsearc add-on shouldn't do this anyway. If it must write the error to database, it must without error @Mike
     
  7. Mike

    Mike XenForo Developer Staff Member

    I've found some issues with logging failed indexing actions when deleting from an index. In many cases, it wasn't actually logging any action, so I've adjusted that to log more correctly. I've also put some changes in to ensure this won't error.
     
    maxicep and thedude like this.
  8. maxicep

    maxicep Active Member

    Thank you for your fixing. So we need to wait newer version of ES add-on right ?
     
  9. Mike

    Mike XenForo Developer Staff Member

    Yes. This is mostly just covering up a different error, which is one that should be investigated as the root action (why index-related actions are failing in ElasticSearch). This just prevents the second error.
     
  10. maxicep

    maxicep Active Member

    Yes i understand.

    Btw, i fixed the problem with rebuild all search index in a night. But if any error occured on the elasticsearch service at server, xenforo will not record the errors until update your plugin. So we are waiting it.
     
  11. maxicep

    maxicep Active Member

    Mike, i have still errors about same problem and we can't delete some posts or threads at this time. So, can you please share the changes how it will be fixed ? Because i don't know when new version is ready.
     
  12. Chris D

    Chris D XenForo Developer Staff Member

    I'll start a conversation with you and attach the updated files.
     
  13. Coop1979

    Coop1979 Well-Known Member

    I can't find where this was ever released, and now I'm having issues related to my ES setup. Can you send me the updated files, as well, @Chris D?
     
  14. eagle eyes

    eagle eyes Active Member

    I need the updated files as well.
     
  15. Chris D

    Chris D XenForo Developer Staff Member

    I added you both to a conversation where I had already attached the fixes.
     
  16. The Forum Heroes

    The Forum Heroes Well-Known Member

    I need this as well @Chris D when you have a chance. Happening for several clients along with my site using MariaDB 10.1.12
     
  17. Mike

    Mike XenForo Developer Staff Member

    At this point, we'll probably just do a release along with the next XF release.

    Just to reiterate though, this does not cause any errors itself. It only comes up if you are already having underlying errors. You may be able to find details of that error in the Elasticsearch log.
     
  18. The Forum Heroes

    The Forum Heroes Well-Known Member

    Right @Mike It still works as it should. But some people don't understand that and insist it's broke simply because it triggered the ACP log..
     
  19. eagle eyes

    eagle eyes Active Member

    The impact of this error is "Moderators can't delete the posts or threads". So it does break functionality.
     
  20. cgunes

    cgunes Member

    We are experiencing the same problem. Can I have the updated files.
    Thank you
     

Share This Page