How to insert the data in RT index from MySQL

General Discussions - English
1

Hi
As there is no data source in manticore.config file for RT index,How to insert the data into the RT index from MySQL…?

2

you have to create RT index with fields and attributes you need when use INSERT statements to populate data there

3

One option is to create a ‘plain’ disk index with the same schema. Use ‘indexer’ tool to build the index (which runs the queries from its source)

… then use ‘ATTACH INDEX’ to ‘convert’ the new index to a RT index. Then can continue to use the RT index going forward inserting new rows as needed directly.

otherwise can just build a script that fetches the data from MySQL, and then inserts into the index.

4

Also indexer --print-rt can be used - https://docs.manticoresearch.com/latest/html/command_line_tools_reference/indexer_command_reference.html?highlight=print-rt

5

could you please give an example for the below i.e PRINT-RT

sudo -u manticore indexer -c manticore.conf --print-rt indexrt indexplain > dump.sql

6

Here’s an example:

snikolaev@dev:~$ ./indexer -c min.conf idx --print-rt rt
#
# === source min ts 1584515403
# Wed Mar 18 08:10:03 2020
#
#	rt_field = doc # field 0
#	rt_attr_string = doc # attr 2
#	rt_attr_uint = group_id # attr 3
#	rt_attr_string = color # attr 4
#	rt_attr_uint = size # attr 5
#

INSERT INTO rt VALUES (1,'dog','dog',1,'red',3),(2,'dog','dog',1,'red',8),(3,'dog','dog',2,'green',1),(4,'dog','dog',2,'green',3),(5,'dog','dog',3,'red',9),(6,'dog','dog',4,'blue',3),(7,'dog > <','dog > <',5,'red',3);

The config is:

snikolaev@dev:~$ cat min.conf
source min {
    type = mysql
    sql_host = localhost
    sql_user = test
    sql_pass =
    sql_db = test
    sql_query = select 1, 'dog' doc, 1 group_id, 'red' color, 3 size \
    union select 2, 'dog' doc, 1 group_id, 'red' color, 8 size \
    union select 3, 'dog' doc, 2 group_id, 'green' color, 1 size \
    union select 4, 'dog' doc, 2 group_id, 'green' color, 3 size \
    union select 5, 'dog' doc, 3 group_id, 'red' color, 9 size \
    union select 6, 'dog' doc, 4 group_id, 'blue' color, 3 size \
    union select 7, 'dog > <' doc, 5 group_id, 'red' color, 3 size
    sql_field_string = doc
    sql_attr_uint = group_id
    sql_attr_string = color
    sql_attr_uint = size
}

index idx {
    path = idx
    source = min
}

searchd {
    listen = 9314:mysql41
    log = sphinx_min.log
    pid_file = /home/snikolaev/9314.pid
    binlog_path =
}

There is a known issue of a crash of indexer --print-rt. It’s fixed in https://github.com/manticoresoftware/manticoresearch/commit/e426950a536382a142e2a42ed207c341149ff685 and will be included into Manticore 3.4.0 which we are going to release within a week.