Hi,
I am getting inconsistent results when searching from my employees_index. I started getting this issue ever since we updated to version 13.x, then decided to downgrade to 10.x. I didn’t encounter any issues using 9.x though. This happens from time to time.
Below are my table configuration:
source employees_main : employees_db
{
sql_query_pre = SET CHARACTER_SET_RESULTS = utf8
sql_query_pre = SET NAMES utf8 COLLATE utf8_unicode_ci;
sql_query_pre = SET max_execution_time = 1000000;
sql_query_pre = SET net_read_timeout = 300000;
sql_query_pre = SET wait_timeout = 3000;
sql_query_pre = \
REPLACE INTO sphinx_counter \
SET entity = 'employees_main', \
last_updated = NOW(), \
server_ip = 'local';
sql_query_range = SELECT MIN(id), MAX(id) FROM employees;
sql_range_step = 10000
sql_query = \
SELECT employees.id, employees.id as employee_id, employees.*, \
accounts.id as account_id \
FROM employees \
JOIN employeelists ON employees.employeelist_id = employeelists.id \
JOIN accounts ON employeelists.account_id = accounts.id \
WHERE employees.id >= $start AND employees.id <= $end \
AND accounts.status = 1;
sql_attr_uint = employee_id
sql_query_post_index = \
REPLACE INTO sphinx_counter \
SET entity = 'employees_delta', \
server_ip = 'local', \
last_updated = ( \
SELECT last_updated \
FROM sphinx_counter sc \
WHERE entity = 'employees_main' \
AND server_ip = 'local' \
);
}
source employees_delta : employees_main
{
sql_query_pre =
sql_query_range = \
SELECT ( \
SELECT UNIX_TIMESTAMP(last_updated) \
FROM sphinx_counter \
WHERE entity = 'employees_delta' \
AND server_ip = 'local' \
) min, UNIX_TIMESTAMP() max;
sql_query_killlist = \
SELECT employees.id \
FROM employees \
WHERE employees.date_modified >= ( \
SELECT last_updated \
FROM sphinx_counter \
WHERE entity = 'employees_delta' AND server_ip = 'local' \
);
sql_query = \
SELECT employees.id, employees.id as employee_id, employees.*, \
accounts.id as account_id \
FROM employees \
JOIN employeelists ON employees.employeelist_id = employeelists.id \
JOIN accounts ON employeelists.account_id = accounts.id \
WHERE employees.date_modified >= FROM_UNIXTIME($start) AND employees.date_modified <= FROM_UNIXTIME($end) \
AND accounts.status = 1;
sql_query_post_index =
}
table employees_main
{
type = plain
source = employees_main
path = /var/lib/manticore/data/employees_main
morphology = none
min_word_len = 1
min_stemming_len = 3
min_prefix_len = 3
expand_keywords = 1
html_strip = 1
stopwords_unstemmed = 1
columnar_attrs = *
}
table employees_delta : employees_main
{
source = employees_delta
path = /var/lib/manticore/data/employees_delta
killlist_target = employees_main:kl
}
table employees_index
{
type = distributed
local = employees_main, employees_delta
}
I have a cron job to reindex employees_main and employees_delta.
Every Sunday for the employees_main:
sudo -u manticore indexer employees_main --rotate && indexer --merge employees_main employees_delta --rotate --drop-src
Every 10 minutes for employees_delta:
sudo -u manticore indexer employees_delta --rotate
Broken:
mysql> SELECT * FROM employees_index where match('((@custom_id "A5H9"/1) (@account_id "881"/1))') limit 10;
+-----------+-----------+-------------+-------------+---------------------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
| id | custom_id | first_name | middle_name | last_name | maiden_name | alt_first_name | alt_first_name_2 | alt_first_name_3 | alt_first_name_4 | alt_first_name_5 | alt_middle_name | alt_middle_name_2 | alt_middle_name_3 | alt_middle_name_4 | alt_middle_name_5 | alt_last_name | alt_last_name_2 | alt_last_name_3 | alt_last_name_4 | alt_last_name_5 | alt_business1 | alt_business2 | business | npi | ssn_last_four | upin | certification_number | certification_state | date_of_birth | account_id | employee_id |
+-----------+-----------+-------------+-------------+---------------------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
| 62400640 | A5HE | SARAH | JANE | PONCE | | | | | | | | | | | | | | | | | | | | AREALNULL | many | AREALNULL | AREALNULL | AREALNULL | 1983-10-06 | 881 | 62400640 |
| 62642704 | A5HI | ELIAS | | MOORE | | | | | | | | | | | | | | | | | | | | AREALNULL | diff | AREALNULL | AREALNULL | AREALNULL | 1955-07-17 | 881 | 62642704 |
| 142623443 | A0N8 | OLIVER | M | MENDOZA | | | | | | | | | | | | | | | | | | | | AREALNULL | ones | AREALNULL | AREALNULL | AREALNULL | 1972-03-12 | 881 | 142623443 |
| 159186378 | A5IZ | MARY | | SMITH | | | | | | | | | | | | | | | | | | | | AREALNULL | dspy | AREALNULL | AREALNULL | AREALNULL | 1981-01-14 | 881 | 159186378 |
| 159190152 | A5W4 | SEAN | | PENN | | | | | | | | | | | | | | | | | | | | AREALNULL | here | AREALNULL | AREALNULL | AREALNULL | 1965-04-04 | 881 | 159190152 |
+-----------+-----------+-------------+-------------+---------------------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
5 rows in set (0.02 sec)
--- 5 out of 5 results in 19ms ---
Fixed:
mysql> SELECT * FROM employees_index where match('((@custom_id "A5H9"/1) (@account_id "881"/1))') limit 10;
+-----------+-----------+------------+-------------+-----------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
| id | custom_id | first_name | middle_name | last_name | maiden_name | alt_first_name | alt_first_name_2 | alt_first_name_3 | alt_first_name_4 | alt_first_name_5 | alt_middle_name | alt_middle_name_2 | alt_middle_name_3 | alt_middle_name_4 | alt_middle_name_5 | alt_last_name | alt_last_name_2 | alt_last_name_3 | alt_last_name_4 | alt_last_name_5 | alt_business1 | alt_business2 | business | npi | ssn_last_four | upin | certification_number | certification_state | date_of_birth | account_id | employee_id |
+-----------+-----------+------------+-------------+-----------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
| 62400631 | A5H9 | JOHN | | GRISHAN | | | | | | | | | | | | | | | | | | | | AREALNULL | smpl | AREALNULL | AREALNULL | AREALNULL | 1973-12-04 | 881 | 62400631 |
| 62642695 | A5H9 | JOHN | | GRISHAN | | | | | | | | | | | | | | | | | | | | AREALNULL | smpl | AREALNULL | AREALNULL | AREALNULL | 1973-12-04 | 881 | 62642695 |
| 142623417 | A5H9 | JOHN | | GRISHAN | | | | | | | | | | | | | | | | | | | | AREALNULL | smpl | AREALNULL | AREALNULL | AREALNULL | 1973-12-04 | 881 | 142623417 |
| 159186335 | A5H9 | JOHN | | GRISHAN | | | | | | | | | | | | | | | | | | | | AREALNULL | smpl | AREALNULL | AREALNULL | AREALNULL | 1973-12-04 | 881 | 159186335 |
| 159190109 | A5H9 | JOHN | | GRISHAN | | | | | | | | | | | | | | | | | | | | AREALNULL | smpl | AREALNULL | AREALNULL | AREALNULL | 1973-12-04 | 881 | 159190109 |
+-----------+-----------+------------+-------------+-----------+-------------+----------------+------------------+------------------+------------------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+---------------+-----------------+-----------------+-----------------+-----------------+---------------+---------------+----------+-----------+---------------+-----------+----------------------+---------------------+---------------+------------+-------------+
5 rows in set (0.03 sec)
--- 5 out of 5 results in 34ms ---
The Fixed version only gets “fixed” after manually running a rotate against employees_main.
What could be wrong this time? No other configuration changes were made.