HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Full text data search stopped working after update


#1

Hi, I’m not sure what is going on, but after updating to version 4.0.19, we have noticed that our Full text data search is no longer working. The only other update we did besides this was attach a new hard drive and then point the attachments to the new hard drive for more space since we were getting close on space. I moved all the older files to the new location and removed the older files to clean up space on our server. Not sure that would affect the full text search or not.

We do have the sphinxsearch service running. I’ve tested the cron jobs and they are running. One thing I had to do today was to fix the permissions delta_index_shell_script.sh for it to run properly after upgrading. I had already been on version 4 in the previous version before the update and the search was working.


#2

I found this in /var/www/data/logs/helpspot.log

[2015-12-30 07:15:12] production.ERROR: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 index requests_history_ndx,requests_ndx: sort-by attribute 'created_at' not found' in /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:301
Stack trace:
#0 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(301): PDOStatement->execute(Array)
#1 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(617): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Query\Expression), Array)
#2 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(581): Illuminate\Database\Connection->runQueryCallback(Object(Illuminate\Database\Query\Expression), Array, Object(Closure))
#3 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(304): Illuminate\Database\Connection->run(Object(Illuminate\Database\Query\Expression), Array, Object(Closure))
#4 /var/www/helpspot/HS/Search/Sphinx/Connection.php(36): Illuminate\Database\Connection->select()
#5 /var/www/helpspot/HS/Search/Sphinx/QueryBuilder.php(92): HS\Search\Sphinx\Connection->query()
#6 /var/www/helpspot/HS/Search/Sphinx/Search.php(104): HS\Search\Sphinx\QueryBuilder->get()
#7 /var/www/helpspot/pages/ajax_gateway.php(2319): HS\Search\Sphinx\Search->requests()
#8 /var/www/helpspot/pages/ajax_gateway.php(1644): searchRequests(Object(HS\Search\Sphinx\Query), Object(HS\Search\Sphinx\Search), Object(HS\View\BladeRenderer))
#9 /var/www/admin.php(445): unknown()
#10 {main}

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 index requests_history_ndx,requests_ndx: sort-by attribute 'created_at' not found (SQL: SELECT * FROM requests_ndx, requests_history_ndx WHERE MATCH("a") GROUP BY xRequest ORDER BY created_at DESC LIMIT 15000)' in /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:625
Stack trace:
#0 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(581): Illuminate\Database\Connection->runQueryCallback(Object(Illuminate\Database\Query\Expression), Array, Object(Closure))
#1 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(304): Illuminate\Database\Connection->run(Object(Illuminate\Database\Query\Expression), Array, Object(Closure))
#2 /var/www/helpspot/HS/Search/Sphinx/Connection.php(36): Illuminate\Database\Connection->select()
#3 /var/www/helpspot/HS/Search/Sphinx/QueryBuilder.php(92): HS\Search\Sphinx\Connection->query()
#4 /var/www/helpspot/HS/Search/Sphinx/Search.php(104): HS\Search\Sphinx\QueryBuilder->get()
#5 /var/www/helpspot/pages/ajax_gateway.php(2319): HS\Search\Sphinx\Search->requests()
#6 /var/www/helpspot/pages/ajax_gateway.php(1644): searchRequests(Object(HS\Search\Sphinx\Query), Object(HS\Search\Sphinx\Search), Object(HS\View\BladeRenderer))
#7 /var/www/admin.php(445): unknown()
#8 {main} [] []

Full Index search - I broke it!
#3

Hello!

Thanks for the errors there - this looks like a bug that was fixed in the recently released (yesterday!) 4.0.20.

What happened was that a date field configured to be indexed was labeled improperly within the generated sphinx.conf file.

Two paths to fixing this:

One:

  • Update HelpSpot to 4.0.20
  • Regenerate the sphinx.conf file (php hs search:config command)
  • Re-index the database (sudo indexer --all --rotate)

Two:

  • Manually edit the sphinx.conf file, find instances of "dtGMTOpened" and "dtGMTChange" (with double quotes around them) and change them to NOT have double quotes around them: dtGMTOpened and dtGMTChange
  • Re-index the database: sudo indexer --all --rotate
  • Screenshot: http://drop.userscape.com/1aEWJ/1RiRnLKg

Let us know if neither of those work!


#4

Awesome! I’ll try it out now! Thanks chrisfidao!


#5

Ok, well, looks like I didn’t update my sphinx.conf configuration from the update from before. I didn’t even have dtGMT or any dt inside my old sphinx.conf file. Due to this, I’ll have to setup a change control (using helpspot :slightly_smiling:) to push our change in. Thanks again!


#6

Gotcha!

What we do in our HelpSpot Cloud is generate the sphinx.conf file, which puts it in the helpspot data directory (data/sphinx.conf) and then we move it to the default location that Sphinx uses by default - one of these two:

  • /etc/sphinx/sphinx.conf (redhat/centos)
  • /etc/sphinxsearch/sphinx.conf (debian/ubuntu)

That looks something like this:

# Generate new sphinx.conf file
cd /var/www/helpspot
php hs search:config # puts file in /var/www/helpspot/data/sphinx.conf

# Put new conf file in default location for Sphinx
sudo mv data/sphinx.conf /etc/sphinxsearch/sphinx.conf

# Re-index the database for good measure
sudo indexer --all --rotate

#7

Ok, I updated to the latest version, updated the sphinx.conf and ran the indexer command. It initially failed, but I found out it was pointing to the wrong spot for the pid file. I was getting a permission denied fatal error when it pointed to the default created location of /var/run/searchd.pid. I changed it to where it was previously which is /var/run/sphinxsearch/searchd.pid, and then everything is working again. Thanks for all your help with this!