[#MDEV-31820] UPDATE statement with specific string causes disconnect from remote server

I was getting database errors from one of our web applications when constructing an update statement with one particular string. No other strings triggered the bug. After doing a lot of trial an error I narrowed it down to the following test case which seems to reproduce it.

Client server is system1
Database server is system2

Both are running Server version: 5.5.5-10.3.35-MariaDB MariaDB Server on AlmaLinux 8.

From server1 run:

mysql -u cgiuser -h server2

Then execute:

use databasename;

UPDATE Job set description="grant x on ()xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" WHERE id=6393;

The statement fails with:

ERROR 2013 (HY000): Lost connection to MySQL server during query

No connection. Trying to reconnect...

Connection id:    50

Current database: databasename

Doing the same thing but this time running:

UPDATE Job set description="grant x on ()xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" WHERE id=6393;

Where there is one fewer x at the end, works as expected.

The bug requires the "grant" "on" and "()" plus the total length of the query must be above a very specific threshold. It fails when updating a different record, and when updating a different table and field. In each case the affected field is a text field.

mysql> describe Job;

+--------------------+--------------+------+-----+---------+----------------+

| Field              | Type         | Null | Key | Default | Extra          |

+--------------------+--------------+------+-----+---------+----------------+

| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |

| public_id          | varchar(50)  | YES  | UNI | NULL    |                |

| person_id          | int(11)      | YES  | MUL | NULL    |                |

| assigned_person_id | int(11)      | YES  |     | NULL    |                |

| title              | varchar(200) | YES  |     | NULL    |                |

| description        | text         | YES  |     | NULL    |                |

| status             | varchar(20)  | YES  |     | NULL    |                |

| date_opened        | datetime     | YES  | MUL | NULL    |                |

| date_closed        | datetime     | YES  | MUL | NULL    |                |

| magnitude          | tinyint(4)   | YES  | MUL | NULL    |                |

| commercial         | tinyint(1)   | YES  | MUL | NULL    |                |

| budget_code        | varchar(100) | NO   |     |         |                |

+--------------------+--------------+------+-----+---------+----------------+

Running the longer query directly in the console on server2 works as expected. The failure only occurs when executing the statement remotely.

When the crash happens the server error log shows;

2023-08-01 11:51:56 50 [Warning] Aborted connection 50 to db: 'Helpdesk' user: 'cgiuser' host: 'server1' (Got an error reading communication packets)

Turning on query logging on the server shows:

/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:

Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock

Time                Id Command  Argument

230801 11:40:28      8 Connect  cgiadmin@server1 as anonymous on Helpdesk

..but doesn't log the SQL command.

[MDEV-31820] UPDATE statement with specific string causes disconnect from remote server Created: 2023-08-01 Updated: 2023-10-30 Resolved: 2023-10-30
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Update
Affects Version/s:	10.3
Fix Version/s:	N/A

[MDEV-31820] UPDATE statement with specific string causes disconnect from remote server Created: 2023-08-01 Updated: 2023-10-30 Resolved: 2023-10-30