Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
2.4.12
-
None
-
MXS-SPRINT-120
Description
This is an old bug, observed at least since 2.2 - see MXS-1760. Encountered it again in clearer situation.
0. The client (Matomo web analytics software) is written in PHP using their standard classes and runs in PHP-7.2 Connections are made ad-hoc (no pool is used of any kind). The particular job is run via cron and executes several statements in a single session (no parallel connections are used).
1. The client extracts some data from a MariaDB database and writes a local delimited file.
2. The client (unaware that it is remote) tries LOAD DATA INFILE and fails with "permission denied".
3. The client retries, this time with LOAD DATA LOCAL INFILE.
4. The client receives MySQL TABULAR packet with the name of the file to upload.
5. The client uploads the file.
6. Instead of passing the file to the MariaDB backed, MaxScale tries to somehow parse it and then to execute a prepared statement with a non-existent ID.
7. The backend having no such prepared statement ID returns an error and MaxScale closes the connection to the client.
MaxScale is configured with read-write split.
[172.16.70.4-Service]
|
type=service
|
router=readwritesplit
|
servers=172.16.70.2,172.16.70.3,172.16.70.5
|
use_sql_variables_in=master
|
max_slave_connections=1
|
max_slave_replication_lag=10
|
causal_reads=true
|
connection_timeout=3600s
|
This is a shared system with many third-party applications, so "use_sql_variables_in=master" was needed at some point.
The actual LOAD statement is:
LOAD DATA INFILE
|
'/var/www/p1-matomo/tmp/assets/matomo_archive_blob_2020_09-aa51b581443d5d623822acf17a92d78a.csv'
|
REPLACE
|
INTO TABLE
|
`matomo_archive_blob_2020_09` CHARACTER SET latin1
|
FIELDS TERMINATED BY '\t'
|
ENCLOSED BY '"'
|
ESCAPED BY '\\'
|
LINES TERMINATED BY '\r\n'
|
(idarchive,idsite,date1,date2,period,ts_archived,name,value)
|
MaxScale error log file entries:
2020-09-29 19:00:02 warning: (1224773) Client requests unknown prepared statement ID '2087188530' that does not map to an internal ID
|
2020-09-29 19:00:02 warning: (1224773) Using unknown prepared statement with ID 0
|
2020-09-29 19:00:02 error : (1224773) [readwritesplit] Could not find valid server for target type TARGET_MASTER, closing connection.
|
2020-09-29 19:00:02 error : (1224773) [readwritesplit] Lost connection to the master server '172.16.70.2', closing session. Lost connection to
|
master server while connection was idle. Connection has been idle for 0.0 seconds. Error caused by: #HY000: Lost connection to backend server: network error (104, Connection reset by peer). Last close reason: <none>. Last error: Got packets out of order
|
Unfortunately, I cannot get a copy of the actual file as Matomo deletes it immediately after the error, but the tcpdump and the table name both suggest there are some BLOB values there. The dump is available if interesting to you.
The same client job runs against the primary node without a problem.
How can we make MaxScale just pass the uploaded file to the primary node and not try to parse it, deriving a non-existent prepared statement ID?