Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3214

LOAD DATA LOCAL INFILE results in erroneous "unknown prepared statement ID"

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 2.4.12
    • Fix Version/s: N/A
    • Component/s: readwritesplit
    • Labels:
      None
    • Sprint:
      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?

        Attachments

        1. CREATE_TABLE.txt
          0.5 kB
          Assen Totin
        2. dump.cap
          185 kB
          Assen Totin

          Activity

            People

            Assignee:
            markus makela markus makela
            Reporter:
            assen.totin Assen Totin
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: