sphinx_catalogsearch_fulltext doesn't seem to contain anything

I installed this module and seems not to work because the table sphinx_catalogsearch_fulltext doesn't fill up but a sphinx_catalogsearch_fulltext_tmp table was created when I cleared the catalogsearch_fulltext then did a Catalog Search Index reindex.

I have sphinx setup it working properly and below is my configuration for the module

I want to get the idea why we need a temporary table :)

I use magento 1.9.2.1

[manticorp]

Hi,

You need to make sure the MySQL user that magneto is using has the sufficient permissions to delete, create and truncate tables. This might be the issue.

Thanks

Sent from my iPhone

On 8 Sep 2015, at 17:22, Mahan notifications@github.com wrote:

I installed this module and seems not to work because the table sphinx_catalogsearch_fulltext doesn't fill up but a sphinx_catalogsearch_fulltext_tmp table was created when I cleared the catalogsearch_fulltext then did a Catalog Search Index reindex.

I have sphinx setup it working properly and below is my configuration for the module

—
Reply to this email directly or view it on GitHub.

it seems to be irrelevant. the mysql user that my magento uses has a full priviledge on all operation on my db. do you mean the mysql account that sphinx is using?

[manticorp]

Hmm no, the Sphinx user only needs read access. I'm not sure what's up, can you check the query logs? Is this in a dev server or a live server?

Sorry but it's been a long time since I set this up for myself. I seem to remember having the same problem though, being able to create and write to the temp table but not able to do the switch to the live table.

I'm a long way away from home right now on a holiday so this is kinda hard for me to investigate - the full text table doesn't get populated no?

Here's what the module does, to prevent locking issues, it creates a temp table to store all the data, then copies that data over to the main table (after truncating that table),
Then it should work as planned.

For whatever reason, your temp table isn't getting copied over to the actual
Table.

If you can investigate more and share with me the MySQL logs (or even the system.log files after doing a Magento index) I might be able to help you some more :)

Sent from my iPhone

On 9 Sep 2015, at 00:04, Mahan notifications@github.com wrote:

it seems to be irrelevant. the mysql user that my magento uses has a full priviledge on all operation on my db. do you mean the mysql account that sphinx is using?

—
Reply to this email directly or view it on GitHub.

Thanks for the reply :)

Here's what the module does, to prevent locking issues, it creates a temp table to store all the data, then copies that data over to the main table (after truncating that table),
Then it should work as planned.

When thus it copies to the main table?

Sorry but it's been a long time since I set this up for myself. I seem to remember having the same problem though, being able to create and write to the temp table but not able to do the switch to the live table.

Please you can remember it! :)

Do I need to setup magento search by fulltextor by like or combined?

[phrazer]

any update on this one? i have same error, table exists but never gets populated.

[phrazer]

I debuged it, seems to be something with RENAME table. No I personaly dont understand why complicating with temp table and copying data.

Anyway quick solution is that you change this line in sphinx.conf
sql_query = SELECT ... FROM sphinx_catalogsearch_fulltext

and just add "_tmp" to end:
sql_query = SELECT ... FROM sphinx_catalogsearch_fulltext_tmp

Then everything works O.K.

Would be cool to have this index used also on admin search.

[manticorp]

Hi, sorry it's taken me a while to reply, I've not been by a computer much:

I debuged it, seems to be something with RENAME table. No I personaly dont understand why complicating with temp table and copying data.

Well, the reason for this was that I found it would sometimes create a lot of locks on the site when reindexing, meaning customers couldn't search, pages would take forever to load and the admin was apallingly unresponsive. By storing the index in a temporary table until it was completed, then renaming the tables, this completely mitigated that issue.

I'm not sure why it's not working on your system...I would check again that the database user has rename privileges on that database, that the tables and database have the right collation, that the table is being created as MyISAM (both of them)...

If you're still having trouble, try the following:

1. Turn logging on:

   mysql> SHOW VARIABLES LIKE "general_log%";
   
   +------------------+----------------------------+
   | Variable_name    | Value                      |
   +------------------+----------------------------+
   | general_log      | OFF                        |
   | general_log_file | /var/run/mysqld/mysqld.log |
   +------------------+----------------------------+
   
   mysql> SET GLOBAL general_log = 'ON';
   

2. Start a reindex

3. Turn logging off:
   `
   mysql> SET GLOBAL general_log = 'OFF';
   ``

4. Check your log file (the variable general_log_file shows it, in this case /var/run/mysqld/mysqld.log) to see if anything strange is happening.

5. Also check the error log, given by:

   mysql> SHOW VARIABLES LIKE "log_error%";
   +---------------+--------------------------------------------------------------+
   | Variable_name | Value                                                        |
   +---------------+--------------------------------------------------------------+
   | log_error     | /var/run/mysqld/mysql_error.log                              |
   +---------------+--------------------------------------------------------------+
   

This might help you to find if there's something happening.

[joolswills]

I can reproduce this - rebuildAllIndexes() in magento/app/code/community/Manticorp/SphinxSearch/Model/Resource/Fulltext.php doesn't seem to get called at all? (which calls the swapTables code)

[joolswills]

I suspect there is some missing code from last year when this was changed - eg something like this to call the function when rebuilding all the tables.

diff --git a/app/code/community/Manticorp/SphinxSearch/Model/Resource/Fulltext.php b/app/code/community/Manticorp/SphinxSearch/Model/Resource/Fulltext.php
index e3235b4..053c207 100644
--- a/app/code/community/Manticorp/SphinxSearch/Model/Resource/Fulltext.php
+++ b/app/code/community/Manticorp/SphinxSearch/Model/Resource/Fulltext.php
@@ -72,6 +72,11 @@ class Manticorp_SphinxSearch_Model_Resource_Fulltext extends Mage_CatalogSearch_
     public function rebuildIndex($storeId = null, $productIds = null)
     {

+        if ($storeId == null) {
+            $this->rebuildAllIndexes();
+            return $this;
+        }
+
         // Use the parent rebuild method
         parent::rebuildIndex($storeId, $productIds);

but there might be other changes needed too (and this may not be the correct place to call the other function)

[doctea]

@joolswills thank you so much for this -- it seemed to solve the problem on my installation!

[manticorp]

#6 Can anyone here confirm if this PR solves the issues?