• 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

#1
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?
 

Mike

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

Mike

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

Jaxel

Well-known member
#7
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 $controller, XenForo_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;
	}
 

Mike

XenForo developer
Staff member
#8
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.
 
#10
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? ..