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

Fixed Error when rebuild search index

Discussion in 'Resolved Bug Reports' started by BlackMuddler, Mar 11, 2011.

  1. BlackMuddler

    BlackMuddler Active Member

    Code:
    Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Incorrect integer value: '' for column 'user_id' at row 26 - library/Zend/Db/Statement/Mysqli.php:214
    This error occur in ACP/Tools/Rebuild Caches/Rebuild Search Index.

    As toll by Jaxel, I have look at all my table were column user_id do exist. I cant find any empty or null records in user_id anywhere. All have value 0 and up to 11.

    So what do I do from here?
     
  2. Mike

    Mike XenForo Developer Staff Member

    I can't say that I see how this could happen. Do you have any modifications that integrate into the search?

    I can see how to workaround it, but it's best to get the reason its happening first.
     
  3. BlackMuddler

    BlackMuddler Active Member

    Hi Mike ..

    No - I only use Jaxels add-ons I think integrade with xenForo search - Have following his advice and checked some stuff as stated in this thread - but nothing found ..

    My original post
    Jaxels answer

    My other site with same code, same add-ons do not have this issues.. And I do not make code modification my own..
     
  4. Mike

    Mike XenForo Developer Staff Member

    Well based on the data of the request, the error is happening in the media rebuild. I'm not familiar with that code to say how it might be triggered though (possibly with a failed user left join).

    Regardless, I have made a workaround.
     
  5. BlackMuddler

    BlackMuddler Active Member

    Ok Mike, sounds nice - Do Jaxel get that workaround or me? ..
     
  6. Brogan

    Brogan XenForo Moderator Staff Member

    The workaround has been done in the XenForo code.

    It might be worth making Jaxel aware of this thread though.
     
    BlackMuddler likes this.
  7. Jaxel

    Jaxel Well-Known Member

    I'm not sure how this can be happening... below is my searchHandler, and my model...
    PHP:
    <?php

    class EWRmedio_SearchHandler_Media extends XenForo_Search_DataHandler_Abstract
    {
        protected 
    $_mediaModel;

        protected function 
    _insertIntoIndex(XenForo_Search_Indexer $indexer, array $data, array $parentData null)
        {
            
    $metaData = array();

            
    $indexer->insertIntoIndex(
                
    'media'$data['media_id'], $data['media_title'], $data['media_description'],
                
    $data['media_date'], $data['user_id'], 0$metaData
            
    );
        }

        protected function 
    _updateIndex(XenForo_Search_Indexer $indexer, array $data, array $fieldUpdates)
        {
            
    $indexer->updateIndex('media'$data['media_id'], $fieldUpdates);
        }

        protected function 
    _deleteFromIndex(XenForo_Search_Indexer $indexer, array $dataList)
        {
            
    $mediaIDs = array();
            foreach (
    $dataList as $data)
            {
                
    $mediaIDs[] = $data['media_id'];
            }

            
    $indexer->deleteFromIndex('media'$mediaIDs);
        }

        public function 
    rebuildIndex(XenForo_Search_Indexer $indexer$lastId$batchSize)
        {
            
    $mediaIDs $this->_getMediaModel()->getMediaIDsInRange($lastId$batchSize);
            if (!
    $mediaIDs)
            {
                return 
    false;
            }

            
    $this->quickIndex($indexer$mediaIDs);

            return 
    max($mediaIDs);
        }

        public function 
    quickIndex(XenForo_Search_Indexer $indexer, array $contentIds)
        {
            
    $medias $this->_getMediaModel()->getMediasByIDs($contentIds);
            
    $mediaIDs = array();

            foreach (
    $medias as $mediaID => $media)
            {
                
    $mediaIDs[] = $mediaID;
                
    $this->insertIntoIndex($indexer$media);
            }

            return 
    $mediaIDs;
        }

        public function 
    getDataForResults(array $ids, array $viewingUser, array $resultsGrouped)
        {
            return 
    $this->_getMediaModel()->getMediasByIDs($ids);
        }

        public function 
    canViewResult(array $result, array $viewingUser)
        {
            return 
    true;
        }

        public function 
    prepareResult(array $result, array $viewingUser)
        {
            return 
    $result;
        }

        public function 
    getResultDate(array $result)
        {
            return 
    $result['media_date'];
        }

        public function 
    renderResult(XenForo_View $view, array $result, array $search)
        {
            return 
    $view->createTemplateObject('EWRmedio_Search_Result', array(
                
    'media' => $result,
                
    'search' => $search,
            ));
        }

        public function 
    getSearchContentTypes()
        {
            return array(
    'media');
        }

        public function 
    getSearchFormControllerResponse(XenForo_ControllerPublic_Abstract $controllerXenForo_Input $input, array $viewParams)
        {
            return 
    $controller->responseView('EWRmedio_ViewPublic_Search''EWRmedio_Search_Form'$viewParams);
        }

        protected function 
    _getMediaModel()
        {
            if (!
    $this->_mediaModel)
            {
                
    $this->_mediaModel XenForo_Model::create('EWRmedio_Model_Media');
            }

            return 
    $this->_mediaModel;
        }
    }
    PHP:
        public function getMediaIDsInRange($mediaID$limit)
        {
            return 
    $this->_getDb()->fetchCol($this->_getDb()->limit('
                SELECT media_id
                FROM EWRmedio_media
                WHERE media_id > ?
                ORDER BY media_id
            '
    $limit), $mediaID);
        }

        public function 
    getMediasByIDs($mediaIDs)
        {
            if (!
    $medias $this->fetchAllKeyed("
                SELECT *, IF(xf_user.username IS NULL, EWRmedio_media.username, xf_user.username) AS username
                    FROM EWRmedio_media
                    LEFT JOIN EWRmedio_categories ON (EWRmedio_categories.category_id = EWRmedio_media.category_id)
                    LEFT JOIN xf_user ON (xf_user.user_id = EWRmedio_media.user_id)
                WHERE media_id IN (" 
    $this->_getDb()->quote($mediaIDs) . ")
            "
    'media_id'))
            {
                return array();
            }

            return 
    $medias;
        }
     
  8. Mike

    Mike XenForo Developer Staff Member

    It's as I suggested - if the user left join fails (think deleted user), it will output null for the user_id. This doesn't come in threads or posts here as we don't do joins, but the same thing would happen to us if we did (and the workaround will prevent it).

    So for your code, you just need to intval() the user_id before passing it to the index function.
     
  9. BlackMuddler

    BlackMuddler Active Member

    How you two talking wierd [​IMG] but [​IMG] anyways ..

    I hope to get it all fixed .. Thanks to your both..
     
  10. BlackMuddler

    BlackMuddler Active Member

    Now friends .. I think I got it right.. Have looked at all post in table EWRmedio_media again - there was nothing wrong with user_id (have looked after NULL/empty info), seing with my eyes. But I found a lot af entries of youtube videos with should have been deleted because user is deleted, the category including entries is deleted...

    I manually removed these 36 records of youtube videos and BINGO - the Re-build Search Index runs smooth..

    So maybe some check in code somewhere is necessary? ..
     

Share This Page