WordPress Sphinx Search Plugin
Plugin fails due to missing tables (31 posts)

  1. m00dawg
    Posted 5 years ago #

    The plugin is trying to use wp_sph_counter, which does not exist (and is not created by the plugin). I suspect this is an old table that was removed in 3.1?

    root@BlogTest2:/srv/blog.photobiz.com/sphinx# /usr/bin/indexer --rotate --all
    Sphinx (r1533)
    Copyright (c) 2001-2008, Andrew Aksyonoff

    using config file '/etc/sphinxsearch/sphinx.conf'...
    indexing index 'wp_main'...
    ERROR: index 'wp_main': sql_query_pre[1]: Table 'photobiz_blog.wp_sph_counter' doesn't exist (DSN=mysql://pbblogweb:***@localhost:3306/photobiz_blog).
    total 0 docs, 0 bytes
    total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec
    indexing index 'wp_delta'...
    ERROR: index 'wp_delta': sql_query_range: : range-query failed: Table 'photobiz_blog.wp_sph_counter' doesn't exist (DSN=mysql://pbblogweb:***@localhost:3306/photobiz_blog).
    total 0 docs, 0 bytes
    total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec
    distributed index 'wp_' can not be directly indexed; skipping.

    Also, there is no option in the plugin to simply generate the config for a system-wide install. I have to do that manually by running 'php sphinx.conf' which seems a bit silly. I also don't like that Apache can read the Sphinx indexes - there should be an option to skip that (no need if using the system wide installation of Sphinx).

  2. Yaroslav Vorozhko
    Posted 5 years ago #

    Hello m00dawg,
    the table structure wp_sph_counter is described in the readme.txt. This table should created by plugin, try deactivate and activate plugin.

    You shouldn't run 'php sphinx.conf' - it is incorrect command. Use it as usual:
    /usr/bin/indexer --rotate --all -c /etc/sphinxsearch/sphinx.conf

    Using Configuration Wizard you may setup installation path inside of document root to hide index data from public access.

  3. m00dawg
    Posted 5 years ago #

    That's odd, I thought I tried that before but the deactivating and re-activiating the plugin did create the tables. The original problem was that I did not give the WordPress MySQL user privileges to create tables during the original install.

    After that and realizing I had to run 'indexer --all' before starting the daemon, all seems to be working.

    I am still using the standard system install and just pulled what I needed from the custom-config. I just don't like the idea of having WordPress control the instance and prefer to handle it myself (plus I can use init scripts and things that way).

    Otherwise, works great on 3.1 so far!

  4. Yaroslav Vorozhko
    Posted 5 years ago #

    Using the Configuration Wizard you can specify which binaries to use: systems or install new one. Also you can specify path to the configuration file at settings page.

    Btw, I think you are talking about version 2.1, version 3 is in testing stage yet and not available in public access.
    But, will be released soon with a lot of enhancements in usability and performance with a bunch of SEO and analytic features.

  5. m00dawg
    Posted 5 years ago #

    Sorry I meant to say WordPress 3.1 - oops!

    I know I can specify the config path, but does that work if using the standard Sphinx system-wide init script? I noticed that the config has PHP wrapped around it so I wasn't sure how to use that with the Sphinx system-wide instance?

    By the way, I noticed that this plugin seems to work flawlessly with 'Search Everything' (which we use for search term highlighting) so that's very cool!

  6. Yaroslav Vorozhko
    Posted 5 years ago #

    Don't worry about PHP inside sphinx.conf - it is so called shebung syntax which supported by Sphinx.
    PHP inside the sphinx.conf loaded WP database connection parameters and table prefix. You can hard coded this parameters, remove the PHP code and use this configuration as system wide.

    You can find more about scripting in sphinx.conf in this article: Scripting in Sphinx config

  7. m00dawg
    Posted 5 years ago #

    Oh wow, I didn't know that. That's very very cool! I'll go ahead and give that a go. Thank you very much for all the help!

  8. m00dawg
    Posted 5 years ago #

    Hmm I'm having issues with the latest plugin it seems. It installs fine but when I go to search for something on the blog, I get no results. The content is in Sphinx because I can see it run through pages in the indexer and can query search terms via the command-line 'search' tool.

    I don't see any queries in the Sphinx query log so that makes me thing that the blog isn't issuing the search terms to Sphinx. The PHP error log doesn't show anything. Is there any way I can troubleshoot this more directly?

    Note that I have tried a "standard" install (using the PHP generated sphinx.conf as we've been talking about previously in this thread) as well as a static install using my own config file and locations. Neither seems to work through the plugin.

  9. Yaroslav Vorozhko
    Posted 5 years ago #

    Make sure searchd is running.
    Check connection parameters port and host.
    Run configuration wizard if necessary.

  10. m00dawg
    Posted 5 years ago #

    searchd is indeed running and the plugin can see that it is as well:

    root@Blog:~# netstat -nptl | grep search
    tcp 0 0* LISTEN 23419/searchd

    I have tried running the wizard several times. Everything appears all good as far as the plugin is concerned. I thought maybe it was a 'localhost' versus '' thing so I tried that but no dice.

  11. Yaroslav Vorozhko
    Posted 5 years ago #

    Hi m00dawg,
    without looking at sphinx and plugin configuration I don't know what to suggest.
    Contact us and we definitely will solve your problem.

  12. m00dawg
    Posted 5 years ago #

    Fair enough. I went ahead and fired off an e-mail. For the public arena, to elaborate a bit, I tried the WordPress 3.1.1 update and that did not fix things. I also confirmed that the old version of the Sphinx plugin *did* work correctly. So it's something to do with the new version not working with our current setup, though I'm not sure what that is.

  13. Yaroslav Vorozhko
    Posted 5 years ago #

    Did you have status field in the wp_sph_stats table?

    If status field exists, read installation instructions (readme.txt) once again, maybe you find the answer there.

  14. m00dawg
    Posted 5 years ago #

    I do indeed have the status field:

    mysql> show create table wp_sph_stats\G
    *************************** 1. row ***************************
    Table: wp_sph_stats
    Create Table: CREATE TABLE wp_sph_stats (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    keywords varchar(255) NOT NULL DEFAULT '',
    date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    keywords_full varchar(255) NOT NULL DEFAULT '',
    status tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    KEY keywords (keywords),
    FULLTEXT KEY ft_keywords (keywords)
    1 row in set (0.00 sec)

    The above was from the live blog and was a new install of the latest plugin. Dev looks identical though (and I did see the alert in the plugin page to alter the table). I do see that the FULLTEXT index is there but reading the docs it sounds like that is no longer needed (which is preferred since we use InnoDB across the board). Otherwise I didn't really find much in the readme.

    I haven't modified our template to use the custom sphinx functions - I didn't have to do that in the older plugin and was hoping to avoid that case (for now anyway) - could that be the issue?

  15. Yaroslav Vorozhko
    Posted 5 years ago #

    Do have any other activate wordpress search plugins among with Sphinx Search?

  16. m00dawg
    Posted 5 years ago #

    We have the Search Everything plugin. The old Sphinx plugin seemed to co-exist with that one though I have tried disabling it and leaving it enabled with the new Sphinx plugin - same results. I haven't yet tried totally removing the Search Everything plugin though I would have thought just disabling would have been sufficient?

  17. Yaroslav Vorozhko
    Posted 5 years ago #

    Which Sphinx Search version are you using?

  18. m00dawg
    Posted 5 years ago # (the version that ships with Ubuntu 10.04)

  19. Yaroslav Vorozhko
    Posted 5 years ago #

    New version required 0.9.9 at least.
    Setup it and all should start working.

  20. m00dawg
    Posted 5 years ago #

    Ah that explains that. Sure enough, upgraded and it works like a champ! Thanks!

    I did notice one issue, and perhaps it's just my setup or WordPress itself, but I am trying to add:

    <span style="text-align: #ffff00;">

    To the "Before keyword" settings and something is escaping the quotes and mangling the field in the admin panel. I haven't looked into the cause yet so it could very well be my setup (though magic_quotes_gpc is off) but wanted to share.

    Otherwise, it's working great and is SOOOO much faster than the built-in search features. Thanks for all your help!

  21. Yaroslav Vorozhko
    Posted 5 years ago #

    Yes, maybe there are some problem with escaping.
    As workaround I suggest to try without quotes:

    <span class=coloryellow>

    Add class coloryellow to the styles.css:

    color: #ffff00;

    BTW, you used text-align and specify the colour as parameter - it's not correct.

  22. m00dawg
    Posted 5 years ago #

    Hah yes you're absolutely right. I meant 'text-color'. The work-around seems to work though only for the headers. Something is up with trying to use the span within content but that's likely to do with our (very customized) theme.

    Everything is looking great thanks again! If you're going to be at the MySQL Conference next week, we should meet up some time - I'll buy you a beer ;)

  23. Yaroslav Vorozhko
    Posted 5 years ago #

    I'll create an issue about quotes.
    I would like to be there, but I am far away.
    Anyway thanks!

  24. Leo Plaw
    Posted 5 years ago #

    I have compiled and installed Sphinx here at /usr/local/sphinx
    But the plugin fails to detect it when looking for the binaries and only gives me the option to install to the WordPress upload directory. I want it to use system install. Suggestions please.

  25. Leo Plaw
    Posted 5 years ago #

    OK,I thought I would be tricky and soft link sphinx to where your plugin expects to find it.


    However the plugin still claims it can not find indexer or searchd.

    I have checked and they both exist at that location now under /bin.

    So it is still not working. 8(

  26. Yaroslav Vorozhko
    Posted 5 years ago #

    Hi Leo,
    what about plugin folder, did you install it also us soft link ?

  27. Yaroslav Vorozhko
    Posted 5 years ago #

    Also, you can try to copy searchd and indexer into /usr/bin directory.
    Then run wizard again, it should detect it as system binaries.

  28. Leo Plaw
    Posted 5 years ago #

    Hi Yaroslav. No, the plugin was installed normally via upload with FTP to the plugins directory and then activated through the WP admin.

    I also followed your suggestion and created a soft link to indexer and searchd in /usr/bin. Still no success.

    What now?

  29. Leo Plaw
    Posted 5 years ago #

    ah, OK, I've figured out why your plugin is failing.

    escapeshellcmd() and exec() have been disabled for security reasons.

    It would be good if you rewrote your plugin to allow people to manually set the path when they know the exact location when the automatic search or install fails. This would prevent the setup failing when the server security prevents certain PHP commands from being used.

    Now, how do I set your plugin's configuration without the wizard?

  30. Leo Plaw
    Posted 5 years ago #

    I temporarily edited the php.ini and allowed escapeshellcmd() and exec().
    I ran the wizard. Yay! past step 2.
    However I then had to manually create /sphinx/var and child directories where the wizard was expecting them.
    Now I'm at the point where I have the following error. "Indexer: configuration files not found."
    So reading your "readme.txt" and the question and answer, I've arrived at the conclusion that escapeshellcmd() and exec() will have to be permanently allowed in PHP for your plugin to work... correct?

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • WordPress Sphinx Search Plugin
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic