[Plugin: WordPress Sphinx Search Plugin] Plugin fails due to missing tables

  • m00dawg

    (@m00dawg)


    13 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 0.9.8.1-release (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).

Viewing 15 replies - 1 through 15 (of 30 total)
1 2
  • Yaroslav Vorozhko

    (@vorozhko)

    13 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.

    Thread Starter m00dawg

    (@m00dawg)

    13 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!

    Yaroslav Vorozhko

    (@vorozhko)

    13 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.

    Thread Starter m00dawg

    (@m00dawg)

    13 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!

    Yaroslav Vorozhko

    (@vorozhko)

    13 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

    Thread Starter m00dawg

    (@m00dawg)

    13 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!

    Thread Starter m00dawg

    (@m00dawg)

    13 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.

    Yaroslav Vorozhko

    (@vorozhko)

    13 years ago

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

    Thread Starter m00dawg

    (@m00dawg)

    13 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 127.0.0.1:3312 0.0.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 ‘127.0.0.1’ thing so I tried that but no dice.

    Yaroslav Vorozhko

    (@vorozhko)

    13 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.

    Thread Starter m00dawg

    (@m00dawg)

    13 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.

    Yaroslav Vorozhko

    (@vorozhko)

    13 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.

    Thread Starter m00dawg

    (@m00dawg)

    13 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)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    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?

    Yaroslav Vorozhko

    (@vorozhko)

    13 years ago

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

    Thread Starter m00dawg

    (@m00dawg)

    13 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?

Viewing 15 replies - 1 through 15 (of 30 total)
1 2
  • The topic ‘[Plugin: WordPress Sphinx Search Plugin] Plugin fails due to missing tables’ is closed to new replies.