Cant connect to manticore mysql client

Bloodpriest · January 13, 2024, 4:27am

This is my docker-compose.yml

version: '2.2'

services:
  manticore:
    container_name: manticore
    image: manticoresearch/manticore
    environment:
      - EXTRA=1
    restart: always
    ports:
      - 9306:9306
    ulimits:
      nproc: 65535
      nofile:
         soft: 65535
         hard: 65535
      memlock:
        soft: -1
        hard: -1
    volumes:
      - ./data:/var/lib/manticore
    networks:
      - manticore-network
      
  mysql-client:
    image: mysql:latest
    command: ["tail", "-f", "/dev/null"]
    networks:
      - manticore-network
    depends_on:
      - manticore

networks:
  manticore-network:
    driver: bridge

The outputs I get are as follows-

sudo docker-compose up
[+] Running 3/3
 ✔ Network myapp_manticore-network  Created                                                                                                                          1.1s 
 ✔ Container manticore              Recreated                                                                                                                        7.5s 
 ✔ Container myapp-mysql-client-1   Created                                                                                                                          4.8s 
Attaching to manticore, mysql-client-1
manticore       | Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
manticore       | Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
manticore       | [Sat Jan 13 04:12:55.472 2024] [1] using config file '/etc/manticoresearch/manticore.conf' (9278 chars)...
manticore       | starting daemon version '6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)' ...
manticore       | listening on all interfaces for mysql, port=9306
manticore       | listening on UNIX socket /var/run/mysqld/mysqld.sock
manticore       | listening on 172.21.0.2:9312 for sphinx and http(s)
manticore       | listening on all interfaces for sphinx and http(s), port=9308

sudo docker-compose exec mysql-client mysql -h manticore -P 9306
ERROR 2003 (HY000): Can't connect to MySQL server on 'manticore:9306' (111)

sudo docker-compose exec mysql-client mysql -P 9306
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

sudo docker-compose exec manticore mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

How do I get this to run?

Sergey · January 13, 2024, 5:35am

sudo docker-compose exec mysql-client mysql -h manticore -P 9306 and sudo docker-compose exec manticore mysql work fine for me with exactly same docker-compose file.

sudo docker-compose exec mysql-client mysql -P 9306 shouldn’t work.

Please provide your full log of docker compose up, perhaps there’s some error.

Bloodpriest · January 13, 2024, 8:00am

This is what I get-

sudo docker-compose logs
[sudo] password for venny: 
Verification successful
manticore  | Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
manticore  | Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
manticore  | [Sat Jan 13 04:12:55.472 2024] [1] using config file '/etc/manticoresearch/manticore.conf' (9278 chars)...
manticore  | starting daemon version '6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)' ...
manticore  | listening on all interfaces for mysql, port=9306
manticore  | listening on UNIX socket /var/run/mysqld/mysqld.sock
manticore  | listening on 172.21.0.2:9312 for sphinx and http(s)
manticore  | listening on all interfaces for sphinx and http(s), port=9308

Bloodpriest · January 14, 2024, 2:24am

I tried everything yesterday but its not working. Any ideaas?

Also wanted to inform you that in the docs, if we insert - ./manticore.conf:/etc/manticoresearch/manticore.conf in the docker compose it gives an error about trying to mount a directory over a file.
I think the correct statement should be ./manticore.conf:/etc/manticoresearch

Sergey · January 15, 2024, 6:50am

Does just running Manticore in docker without docker-compose work?

Bloodpriest · January 15, 2024, 7:20am

Yup. I asked some1, & he said that the problem is with mysql since docker & mysql dont work together properly. So they suggested, to have a local mysql installation & use manticore from docker as usual to connect. It sounds promising, but im yet to check that approach

Bloodpriest · January 26, 2024, 12:46am

Ok so im following this article- Using Manticore/Sphinx Search with MySQL

& ive done exactly what hes done. created a product table, populated it with dummy values.
Heres my manticore.conf file

source product_data
{
  type        = mysql
  sql_host  = localhost
  sql_user  = root
  sql_pass  = root
  sql_port  = 9306
  sql_db = testdb
  sql_query_pre = SET NAMES utf8
  sql_query_pre = SET SESSION wait_timeout=3600
  sql_query_pre = REPLACE INTO product_search_status (id, value) VALUES ('last_indexed_time', UNIX_TIMESTAMP())
  sql_query_range = SELECT MIN(id), MAX(id) FROM product
  sql_range_step = 10000
  sql_query = SELECT \
                   id, \
                   name AS name_ft, \
                   categories AS categories_ft, \
                   name \
             FROM product \
             WHERE id >= $start AND id <= $end
  sql_query_post_index = REPLACE INTO product_search_status (id, value) VALUES ('last_indexed_id', $maxid)
  sql_attr_string = name
}

index product
{
  path = ./data/product
  source = product_data
  morphology = none
  min_word_len  = 3
  min_prefix_len = 3
}

searchd
{
  engine = columnar
  listen = localhost:9306:mysql
  listen = /var/run/manticore/manticore.s:mysql
  pid_file = /var/run/manticore/searchd.pid
}

& my compose-

version: '1.0'

services:
  manticore:
    container_name: manticore
    image: manticoresearch/manticore
    environment:
      - EXTRA=1
    restart: always
    ports:
      - 127.0.0.1:9306:9306
    ulimits:
      nproc: 65535
      nofile:
         soft: 65535
         hard: 65535
      memlock:
        soft: -1
        hard: -1
    volumes:
      - ./data:/var/lib/manticore
      - ./manticore.conf:/etc/manticoresearch/manticore.conf

When I try sudo docker exec -it manticore indexer --rotate product

I get -

Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2023, Manticore Software LTD (https://manticoresearch.com)

using config file '/etc/manticoresearch/manticore.conf'...
indexing table 'product'...
FATAL: failed to open ./data/product.tmp.spl: No such file or directory, will not index. Try --rotate option.

any ideas?

Sergey · January 26, 2024, 7:42am

First off, path = ./data/product is wrong since there’s no ./data/ inside the container, you map it to /var/lib/manticore.

Also, use gosu as shown here - GitHub - manticoresoftware/docker: Official docker for Manticore Search

Bloodpriest · January 26, 2024, 10:45am

Ive made the changes per your suggestion-
with this

sudo docker-compose exec -it manticore gosu manticore indexer --all --rotate
Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2023, Manticore Software LTD (https://manticoresearch.com)

using config file '/etc/manticoresearch/manticore.conf'...
indexing table 'product'...
FATAL: failed to open /var/lib/manticore.tmp.spl: Permission denied, will not index. Try --rotate option.

& this

sudo docker-compose exec -it manticore indexer --all --rotate
Manticore 6.2.12 dc5144d35@230822 (columnar 2.2.4 5aec342@230822) (secondary 2.2.4 5aec342@230822)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2023, Manticore Software LTD (https://manticoresearch.com)

using config file '/etc/manticoresearch/manticore.conf'...
indexing table 'product'...
ERROR: table 'product': sql_connect: Can't connect to MySQL server on '127.0.0.1:9306' (111) (DSN=mysql://root:***@127.0.0.1:9306/testdb).
total 0 docs, 0 bytes
total 0.002 sec, 0 bytes/sec, 0.00 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

using docker/docker-compose yields same result. Sorry for constantly asking for help but im stuck on this for like forever now, & I need some help.
Much appreciated

Sergey · January 26, 2024, 11:08am

sql_host = localhost
sql_port = 9306

is wrong. You are supposed to connect to mysql there which is not available at localhost and it usually listens not at port 9306.

Bloodpriest · January 26, 2024, 11:14am

ok so correct me if im wrong because this is a bit confusing- mysql localhost:9306 is just a clients wherein we write queries for manticore to work, but for creation of tables, insertions etc, we use a different host & port? & why do we do this? As in whats the structure or flow pattern of the execution of this?
Any articles I can refer to or any examples that work (including mysql commands)?

Sergey · January 26, 2024, 11:44am

sql_host/sql_port are what indexer will connect to in order to fetch data from your external database. Otherwise it just can’t know where to execute your sql_query, you have to give the source all the credentials including proper host/port to connect to the mysql instance.

So, you need to run another mysql container alongside with the manticore container or if there’s already a running one, you can map it’s port to the manticore container in the docker-compose YAML and then use that instead of 9306 in sql_port.

Bloodpriest · January 26, 2024, 11:51am

ok…ill try to do this, not quite sure I understand whats going on yet, but ill give it a shot.
My main db is cassandra actually. so im gonna have to populate the same data in mysql too for indexing ig. Thankyou for bearing with me

Sergey · January 27, 2024, 5:35am

My main db is cassandra actually. so im gonna have to populate the same data in mysql too for indexing

Instead of copying data from Cassandra to Mysql you can consider copying it directly to Manticore then using a real-time table

Bloodpriest · January 27, 2024, 5:38am

ive read that plain tables make it easier for indexing in bulk. The architecture is - store data in a message queue & as soon as say 100 messages are obtained, insert them in bulk to be indexed. Cassandra is used for persistence but the data that cassandra stores needs to be real time. But for searches, since no user is going to search for something just recently posted, the time delay is acceptable.
Thats the approach I thought of?

Sergey · January 30, 2024, 10:14am

It’s up to you. I would do it differently:

insert/replce/delete in Manticore in the same place where you insert/replace/delete in Cassandra
write a small script to read from Cassandra and insert into a Manticore real-time table in batches. It looks like you already have such a script, but it now writes first into mysql and then you run indexer to copy the data into Manticore. If this is true, then it’s non-efficient.

Bloodpriest · January 30, 2024, 10:24am

oh. thats some valuable insights. Honestly, i dont have any scripts yet because m priority rn is to first have manticore work atleast without any errors. But yeah, i am a bit confused about how manticore works so I didnt know what im doing apart from following a few blogs.
Honestly, I can make a latency tradeoff because searches not being immediately available is no big deal. But I need this entire thing to run as cheaply as possible, in terms of memory, (thus going for columnar, ig). & offer fast reads too ofc which manticore is quite decent at. I dont want to store any data in manticore since dont wanna pay for extra storage. Only 1 db for storage, so will have to index data everytime ig.
With this architecture consideration, what would you recommend?
& what exactly do you mean by -

insert/replce/delete in Manticore in the same place where you insert/replace/delete in Cassandra

afaik, in the docs it only connects to some sql & odbc dbs. How do I get manticore to access the cassandra db?

Bloodpriest · February 3, 2024, 6:39am

im sorry for the trouble but could you explain why the current method is inefficient & whats the most cheap method I can use in terms of memory. I dont want to persist the data anywhere btw except cassandra.

Sergey · February 6, 2024, 7:42am

whats the most cheap method I can use in terms of memory. I dont want to persist the data anywhere btw except cassandra.

In this case:

you don’t need mysql since it also requires RAM
write a script which reads from Cassandra and writes to Manticore batch by batch (e.g. 1000 docs in each batch). Previously you asked:

& what exactly do you mean by - “insert/replce/delete in Manticore in the same place where you insert/replace/delete in Cassandra”

Please read about RT mode and RT tables in the docs. E.g take this course Manticore Introduction in RealTime tables about RT tables in Plain mode and look through the quick start guide. Then when you learn how to insert data into a Manticore real-time table, consider updating your app, so you write not only to Cassandra, but to Manticore too.