sql_attr_multi query causes db "Aborted connection" warning

General Discussions - English
1

Hi there,

I’m using a MariaDB query as the data source for the Manticore indexer. My source config contains a multi-value field of type sql_attr_multi, which causes my MariaDB database to throw the following warning when the indexer starts building (or rebuilding) a table:

[Warning] Aborted connection 11 to db: 'dbname' user: 'user' host: 'localhost' (Got an error writing communication packets)

I first suspected this error is related to a query that is running too long. However, the warning appears at the very beginning of the indexing process. And the process finishes without any errors reported on the Manticore side.

I turned on verbose query logging in MariaDB, and I see something very odd. The indexer is executing three queries:

240617 20:47:05     11 Connect  user@localhost on dbname using Socket
                    11 Query    [Main SQL query - first run]
                    12 Connect  user@localhost on dbname using Socket
                    12 Query    [sql_attr_multi query]
240617 20:47:06     12 Quit
                    13 Connect  user@localhost on dbname using Socket
                    13 Query    [Main SQL query - second run]
240617 20:49:01     13 Quit

It starts by connecting to MariaDB and running the SQL query in the sql_query config parameter (which I labeled above as “Main SQL query”). Immediately after – and without correctly terminating that connection – it aborts and connects again to run the query contained in the sql_attr_multi parameter. That session is ended correctly. Finally, the indexer connects one last time and runs the sql_query, allowing that query to run to completion.

So, we have three Connects, and just two Quits, which explains the MariaDB warning.

As I mentioned, I get no errors in Manticore, and the generated table seems to work properly. But on the MariaDB side, the query log shows an error for that first aborted query:

2024-06-17 20:47:05 user[user] @ localhost [] ERROR 1160: Got an error writing communication packets : [Main SQL query]

If I comment out the sql_attr_multi parameter in my config, the warning goes away, and just a single query is sent to MariaDB. I’ve been able to reproduce this behavior on several systems.

I suppose this is just a cosmetic issue, but it seems rather odd. Is it something I could be concerned about, or possibly a bug in the indexer?

Thank you very much for any help!

2

Hi

I can’t reproduce like this:

snikolaev@dev2:~$ docker run -d --name mariadb -e MYSQL_ROOT_PASSWORD=mysecret -p 9307:3306 mariadb

snikolaev@dev2:~$ mysql -P9307 -h0 -u root -pmysecret -e "create database test"
mysql: [Warning] Using a password on the command line interface can be insecure.

snikolaev@dev2:~$ cat min_multi2.conf
source min {
    type = mysql
    sql_host = 127.0.0.1
    sql_user = root
    sql_pass = mysecret
    sql_port = 9307
    sql_db = test
    sql_query = select 1, 'dog' doc
    sql_attr_multi = uint m from query; select 1, 1 union select 1, 2
}

index idx {
    path = idx
    source = min
}

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

snikolaev@dev2:~$ docker logs mariadb 2>&1|tail -2
2024-06-18  3:59:52 3 [Warning] Access denied for user 'snikolaev'@'172.17.0.1' (using password: NO)
2024-06-18  4:00:18 5 [Warning] Access denied for user 'root'@'172.17.0.1' (using password: YES)

snikolaev@dev2:~$ indexer -c min_multi2.conf --rotate --all
Manticore 6.3.1 00e77610d@24052911 dev (columnar 2.3.1 4383a90@24052309) (secondary 2.3.1 4383a90@24052309) (knn 2.3.1 4383a90@24052309)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2024, Manticore Software LTD (https://manticoresearch.com)

using config file '/home/snikolaev/min_multi2.conf'...
indexing table 'idx'...
collected 1 docs, 0.0 MB
creating secondary index
creating lookup: 0.0 Kdocs, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 1 docs, 3 bytes
total 0.113 sec, 26 bytes/sec, 8.84 docs/sec
total 3 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 17 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
rotating tables: successfully sent SIGHUP to searchd (pid=2109188).

snikolaev@dev2:~$ docker logs mariadb 2>&1|tail -2
2024-06-18  3:59:52 3 [Warning] Access denied for user 'snikolaev'@'172.17.0.1' (using password: NO)
2024-06-18  4:00:18 5 [Warning] Access denied for user 'root'@'172.17.0.1' (using password: YES)

snikolaev@dev2:~$ mysql -P9315 -h0 -e "select * from idx"
+------+------+------+
| id   | doc  | m    |
+------+------+------+
|    1 | dog  | 1,2  |
+------+------+------+

Please modify this test case to show how to reproduce the issue.

3

Hi Sergey,

Thanks for the help. It seems to be a bit difficult to reproduce, I admit. :slight_smile:

I’m using MariaDB 10.5 and the latest Manticore release 6.3.0. I could not reproduce the issue using the queries you suggested above, nor could I reproduce it using queries with the benchmark() function, which I hoped would introduce some latency. After much trial and error, I can only reproduce this when reading data from a populated table. I am unsure why it makes any difference.

In your database called test, let’s create a table with some “realistic” data:

CREATE TABLE TestTable (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

We can use a procedure to generate the data. I chose to create 100,000 rows.

DELIMITER $$

CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO  -- Adjust the number of rows as needed
        INSERT INTO TestTable (id, name, age) VALUES (i, CONCAT('Name', i), 20 + (i % 50));
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;


CALL GenerateTestData();

Next, please adjust your Manticore config as follows:

sql_query = select id from TestTable

Then, run the indexer again while tailing the MariaDB log:

manticore indexer --rotate --print-queries idx

Are you successful with these instructions?

Thanks very much again for testing.

4

Yes, I can reproduce the issue now. Thanks.

5

Strange, isn’t it?

Do you think it’s anything more than cosmetic, or is it still too early to tell?

I’m confused why Manticore is sending three queries to the database instead of just two.

Thanks!

6

Hi Sergey,

Should I make a bug report for this issue?

I have not tested on the latest Manticore release (> 6.3.0), so I’m not aware if it was one of the fixes (though I suppose not).

I just don’t want to duplicate any effort, so will yield to your advice.

Thanks!

7

Hi

Feel free to make a bug report on Github. AFAIR there’s no open issue like this.

8

Related issue is sql_attr_multi query causes "Aborted connection" warning with MariaDB · Issue #2435 · manticoresoftware/manticoresearch · GitHub