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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Incomplete
Affects Version/s: 10.3(EOL)
Fix Version/s: N/A
Component/s: Data Manipulation - Update
Labels:
- crash
Environment:
AlmaLinux release 8.8 (Sapphire Caracal)
Linux 4.18.0-425.3.1.el8.x86_64

Description

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.

Attachments

Activity

Ascending order - Click to sort in descending order

Simon Andrews created issue - 2023-08-01 13:13

Simon Andrews made changes - 2023-08-01 13:15

Field	Original Value	New Value
Description	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.	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.

Sergei Golubchik made changes - 2023-09-10 17:36

Description

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.

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:
{noformat}
mysql -u cgiuser -h server2
{noformat}
Then execute:
{code:sql}
use databasename;

UPDATE Job set description="grant x on ()xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" WHERE id=6393;
{code}
The statement fails with:
{noformat}
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 50
Current database: databasename
{noformat}
Doing the same thing but this time running:
{code:sql}
UPDATE Job set description="grant x on ()xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" WHERE id=6393;
{code}
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.
{noformat}
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 | | | |
+--------------------+--------------+------+-----+---------+----------------+
{noformat}

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;
{noformat}
2023-08-01 11:51:56 50 [Warning] Aborted connection 50 to db: 'Helpdesk' user: 'cgiuser' host: 'server1' (Got an error reading communication packets)
{noformat}
Turning on query logging on the server shows:
{noformat}
/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
{noformat}
..but doesn't log the SQL command.

Sergei Golubchik made changes - 2023-09-10 17:37

Affects Version/s		10.3 [ 22126 ]
Affects Version/s	5.5 [ 15800 ]

Sergei Golubchik added a comment - 2023-09-30 16:30

s_andrews_babraham, we weren't able to repeat this bug. May be there is some detail that is important for reproducing it, that we don't know, something special to your environment. Could you please try to create a complete test case? Something that can be run with

mariadb < test.sql

Sergei Golubchik added a comment - 2023-09-30 16:30 s_andrews_babraham , we weren't able to repeat this bug. May be there is some detail that is important for reproducing it, that we don't know, something special to your environment. Could you please try to create a complete test case? Something that can be run with mariadb < test.sql

Sergei Golubchik made changes - 2023-09-30 16:30

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Sergei Golubchik made changes - 2023-10-30 11:05

Fix Version/s		N/A [ 14700 ]
Resolution		Incomplete [ 4 ]
Status	Needs Feedback [ 10501 ]	Closed [ 6 ]

People

Assignee:: Unassigned

Reporter:: Simon Andrews

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-08-01 13:13

Updated:: 2023-10-30 11:05

Resolved:: 2023-10-30 11:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.