sql_attr_multi query causes "Aborted connection" warning with MariaDB

[aj-potc]

Bug Description:

The Bug

Originally posted here: https://forum.manticoresearch.com/t/sql-attr-multi-query-causes-db-aborted-connection-warning/1988

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.

Steps to reproduce

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

Create a test database: snikolaev@dev2:~$ mysql -P9307 -h0 -u root -pmysecret -e "create database test" Populate it with data: In the 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();

Set up the Manticore config:

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 id from TestTable
    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 =
}

Tail the MariaDB log: snikolaev@dev2:~$ docker logs mariadb 2>&1|tail -2

Rotate the index: snikolaev@dev2:~$ indexer -c min_multi2.conf --rotate --print-queries idx

The errors I reported above will appear.

Manticore Search Version:

6.3.0

Operating System Version:

Rocky Linux 9

Have you tried the latest development version?

No

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation reviewed - [ ] Changelog updated

[sanikolaev]

MRE

docker run --rm -d --name mariadb -e MYSQL_ROOT_PASSWORD=mysecret -p 9307:3306 mariadb
docker logs -f mariadb 2>&1|grep -qm1 "ready for connections"

cat <<'EOF' > multi.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 id from t
    sql_attr_multi = uint m from query; select 1, 1
}

index idx {
    path = idx
    source = min
}
EOF

mysql -P9307 -h0 -u root -pmysecret -e "create database test"
docker exec -i mariadb mariadb -uroot -pmysecret test < t.sql

indexer -c multi.conf idx

snikolaev@dev2:~$ docker logs mariadb 2>&1|tail -1
2024-07-22  6:43:11 13 [Warning] Aborted connection 13 to db: 'test' user: 'root' host: '172.17.0.1' (Got an error writing communication packets)

t.sql is here

Expected: no Aborted connection.

Reproduced in Manticore 6.3.3 fb447b264@24071413 dev (columnar 2.3.1 42f2b06@24070110) (secondary 2.3.1 42f2b06@24070110) (knn 2.3.1 42f2b06@24070110)