CentOS7 Amazon EC2

Please try with 2.2.5.

pramod.mahto@mariadb.com Please try and see if this still occurs with 2.2.5.

Also, is it verified that use_sql_variables_in=all solves the problem?

Just saw this again.

MaxScale is 2.2.13 on RHEL-7.5. Master/slave cluster with read/write split. Client software is Matomo (previously Piwik, web stats collector) which runs aggregation jobs via cron.

Without use_sql_variables_in in config (i.e. default value) some queries fail with the following message:

"The query can't be routed to all backend servers because it includes SELECT and SQL variable modifications which is not supported. Set use_sql_variables_in=master or split the query to two, where SQL variable modifications are done in the first and the SELECT in the second one."

After several of these MaxScale got completely hosed up and was not responding to maxadmin commands, hence had to be fully restarted. I can provide the quesries inquestion (but they are up to a full page long).

After use_sql_variables_in=master is set, some queries (but we don't know whether the same as before or not) fail with:

2018-08-22 01:00:31 warning: (6742) [readwritesplit] Client requests unknown prepared statement ID '3789923308' that does not map to an internal ID
2018-08-22 01:00:31 warning: (6742) [readwritesplit] Using unknown prepared statement with ID 0

This is a live production system, so the abilities to trace and log actual queries are limited.

Pointing the client software directly to the master seems to resolve the issue, but is, of course, not a long-term solution.

I managed to load the database dump in a separate, non-production master/slave cluster with a debug version of MaxScale 2.2.13 in front of it with use_sql_variables_in=master and run the aggregation job on it several times (taking TCP dumps on the MaxScale and master and resetting the database every time):

1. The aggregation job is really just a series of SELECTs, after which the aggregated data is written to a file and LOAD DATA LOCAL INFILE is called. This is repeated dozens and dozens of times (perhaps one for each monitored web site, of which there are ~70 in this case). The client seems to only do sequential processing, so there are not parallel requests, multi-threading etc.

2. After running a dozen or two of these cycles, MaxScale crashes with SIGSEGV. For this particular dump it always crashes at the same place, i.e. the crash is deterministic. The crash occurs while LOAD DATA LOCAL INFILE is in progress (client streams the data); the crash does not occur on the first LOAD DATA LOCAL INFILE, but after many of them complete successfully.

3. The TCP dump on teh master confirms the issue is with MaxScale itself, since the master is still receiving data from MaxScale as a part of a LOAD DATA execution when MaxScale crashes and RST packet is received by the master in the middle of the data flow.

4. The logged message about prepared statment with ID=0 may be completely wrong. The client is merely a PHP CLI script which does not use any prepared statements (and has no reasons to do so, since it is a one-off job with limited duration). The TCP dump on the MaxScale does not show any single prepared statement in use. There is no indication what may trigger this log message, but it appears every time just before the crash.

5. MaxScale lacks debuginfo as a separate RPM package, hence I was nto able to get a meaningful output from gdb, not even a backtrace.

6. I was able to run MaxScale in Valgrind which did produce some reasonable output, attached here.

The crash appears to be a separate problem for which I've opened MXS-2024.