CODEWITHSUNDEEP
sphinx
vanchester
vanchester

Reputation: 297

Sphinx: no field found in schema after ATTACH index

I have a table in MySQL

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `article` varchar(50) NOT NULL,
  `oem` varchar(50) NOT NULL,
  `size` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 ;

INSERT INTO `test` (`id`, `article`, `oem`, `size`) VALUES
(1, '92S400', '11223', '300x200x200'),
(2, '92S400', '11224', '300x150x200');

and indexes in Sphinx

source s_test
{
    type            = mysql

    sql_host        = localhost
    sql_user        = root
    sql_pass        = 123
    sql_db          = auto
    sql_port        = 3306

    sql_query_pre   = SET NAMES cp1251
    sql_query_pre   = SET CHARACTER SET cp1251

    sql_query       = \
        SELECT \
            id `id`, \
            'stub' as `stub`, \
            article `article`, \
            oem `oem`, \
            size `size` \
        FROM \
            test

    sql_attr_string      = article
    sql_attr_string      = oem
    sql_attr_string      = size

    sql_query_info       = SELECT * FROM test WHERE id=$id
}

index i_test
{
    source            = s_test

    path            =  /var/lib/sphinx/i_test

    min_word_len        = 1
    enable_star         = 0
    min_prefix_len      = 3
    expand_keywords     = 1
    index_sp = 0
    stopword_step = 1
    dict=crc
}

index rt_test
{
    type                = rt
    rt_mem_limit        = 2047M

    path                = /var/lib/sphinx/test
    enable_star         = 1
    min_prefix_len      = 3
    expand_keywords     = 1
    dict                = keywords
    morphology          = stem_en

    charset_table = 0..9, A..Z->a..z, _, a..z, U+A8->U+E5, U+B8->U+E5, U+C0..U+DF->U+E0..U+FF, U+E0..U+FF

    rt_field        = article
    rt_field            = oem

    rt_attr_string      = size
}

I run commands in shell:

searchd --stop
rm /var/lib/sphinx/*
indexer --all
searchd

If insert into rt_test data with 'INSERT' command, for example

INSERT INTO rt_test VALUES (1, '92S400', '11223', '300x200x200'),
(2, '92S400', '11224', '300x150x200');

there is no problems with 'select .. MATCH':

SELECT * FROM rt_test WHERE MATCH('@oem 11223')\G;

*************************** 1. row ***************************
id: 1
weight: 1643
  size: 300x200x200

But if I remove files, re-run indexer and run attach command

ATTACH INDEX i_test TO RTINDEX rt_test;

Sphinx shows error on previous 'SELECT .. MATCH' query:

ERROR 1064 (42000): index rt_test: query error: no field 'oem' found in schema

The data from mysql table exists in rt_index. Why disappear indexes rt_oem and rt_article?

PS Sphinx 2.0.8-release (r3831), Xubuntu x64

Upvotes: 0

Views: 3273

Answers (2)

barryhunter
barryhunter

Reputation: 21081

Doing a direct comparison of your index definitons, they arent the same. 

        'stub' as `stub`, \
        article `article`, \
        oem `oem`, \
        size `size` \

sql_attr_string      = article
sql_attr_string      = oem
sql_attr_string      = size

====================

    rt_field        = article
    rt_field            = oem 
rt_attr_string      = size

So your disk index, has the stub as a field, and article, oem and size as attributes.

But your RT index, has article and oem as fields, and size as an attribute.

So a standalone RT index created directly would have a field called oem. But a index converted from the disk index, would have oem as a attribute.

To clarify why this is so, all ATTACH really does is take the disk index, and rename its files, so they become the first chunk of a RT index. The disk index no longer exists, and have a new RT index based on the disk index. The index settings themselves are all take from the disk index (the header file from the disk index, becomes the header file for the RT index).

It doesnt try to morph the content of the disk index to match the config of the RT index, because most/many morphs would be impossible, creating an index from data is a lossy process, it cant be undone (so it could be redone with different settings).

... so as result of this, most of the settings (fields/attributes, and tokenizing settings etc) are ignored from the RT index definition. The new RT index is different to the config. For the most part the only real setting actually used is the path, so it knows where to put the index files.

Upvotes: 1

Maksym Polshcha
Maksym Polshcha

Reputation: 18368

The documentation says:

As of 2.0.2-beta, ATTACH INDEX comes with a number of restrictions. Most notably, the target RT index is currently required to be empty, making ATTACH INDEX a one-time conversion operation only. Those restrictions may be lifted in future releases, as we add the needed functionality to the RT indexes. The complete list is as follows.

  • Target RT index needs to be empty.
  • Source disk index needs to have index_sp=0, boundary_step=0, stopword_step=1, dict=crc settings.
  • Source disk index needs to have an empty index_zones setting.

See http://sphinxsearch.com/docs/2.0.8/sphinxql-attach-index.html

In your case rt_test was not empty initially. I suspect that was the reason of the issue.

UPADTE

You can work around the issue by adding "@@relaxed" to the very beginning of query to turn this into a warning.

Upvotes: 0

Related Questions