[MDEV-17610] Unexpected connection abort after certain operations from within stored procedure - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.24, 5.5.64, 10.1.39, 10.3.15, 10.4.4
Component/s: Prepared Statements, Scripts & Clients, Stored routines
Labels:
- affects-tests

Description

CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;

CALL sp;

SELECT 1;

# Cleanup

DROP PROCEDURE sp;

Actual result
MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CALL sp;
+-------------------------+----------+----------+-----------------------------------------------+
\| Table \| Op \| Msg_type \| Msg_text \|
+-------------------------+----------+----------+-----------------------------------------------+
\| test.non_existing_table \| optimize \| Error \| Table 'test.non_existing_table' doesn't exist \|
\| test.non_existing_table \| optimize \| status \| Operation failed \|
+-------------------------+----------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Another example:

CREATE PROCEDURE sp() SHOW USER_STATISTICS;

CALL sp;

SELECT "Still here" AS check_alive;

# Cleanup

DROP PROCEDURE sp;

The server doesn't crash, it's really just a connection loss.
The problem is also reproducible with 10.1 client + 10.2+ server, but does not seem to be reproducible with 10.2 client + 10.1 server (or 10.2 + 10.2 and higher).

However, 10.2+ shows a different problem on the same test case, but with --ps-protocol:

CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;

CALL sp;

Table	Op	Msg_type	Msg_text

test.non_existing_table	optimize	Error	Table 'test.non_existing_table' doesn't exist

test.non_existing_table	optimize	status	Operation failed

SELECT 1;

bug2.ps1d                                [ fail ]

        Test ended at 2019-03-14 19:19:43

CURRENT_TEST: bug2.ps1d

mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement

Attachments

Issue Links

relates to

MDEV-15800 Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed in update_auto_increment() or unexpected ER_LOCK_DEADLOCK upon LOAD or INSERT

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2018-11-03 19:59

Elena Stepanova made changes - 2018-11-03 20:00

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-15800~~ [ ~~MDEV-15800~~ ]

Elena Stepanova added a comment - 2018-12-25 17:41

Assigning to serg for dispatching, I don't know to whom it should belong, if anyone.

Elena Stepanova added a comment - 2018-12-25 17:41 Assigning to serg for dispatching, I don't know to whom it should belong, if anyone.

Elena Stepanova made changes - 2018-12-25 17:41

Assignee		Sergei Golubchik [ serg ]
Description	{code:sql} CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; SELECT 1; # Cleanup DROP PROCEDURE sp; {code} {code:sql\|title=Actual result} MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; Query OK, 0 rows affected (0.04 sec) MariaDB [test]> CALL sp; +-------------------------+----------+----------+-----------------------------------------------+ \| Table \| Op \| Msg_type \| Msg_text \| +-------------------------+----------+----------+-----------------------------------------------+ \| test.non_existing_table \| optimize \| Error \| Table 'test.non_existing_table' doesn't exist \| \| test.non_existing_table \| optimize \| status \| Operation failed \| +-------------------------+----------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [test]> select 1; ERROR 2013 (HY000): Lost connection to MySQL server during query {code} The server doesn't crash, it's really just a connection loss. The problem is also reproducible with 10.1 client + 10.2 server, but does not seem to be reproducible with 10.2 client + 10.1 server (or 10.2 + 10.2 and higher).	{code:sql} CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; SELECT 1; # Cleanup DROP PROCEDURE sp; {code} {code:sql\|title=Actual result} MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; Query OK, 0 rows affected (0.04 sec) MariaDB [test]> CALL sp; +-------------------------+----------+----------+-----------------------------------------------+ \| Table \| Op \| Msg_type \| Msg_text \| +-------------------------+----------+----------+-----------------------------------------------+ \| test.non_existing_table \| optimize \| Error \| Table 'test.non_existing_table' doesn't exist \| \| test.non_existing_table \| optimize \| status \| Operation failed \| +-------------------------+----------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [test]> select 1; ERROR 2013 (HY000): Lost connection to MySQL server during query {code} The server doesn't crash, it's really just a connection loss. The problem is also reproducible with 10.1 client + 10.2+ server, but does not seem to be reproducible with 10.2 client + 10.1 server (or 10.2 + 10.2 and higher). Another example: {code:sql} CREATE PROCEDURE sp() SHOW USER_STATISTICS; CALL sp; SELECT "Still here" AS check_alive; # Cleanup DROP PROCEDURE sp; {code}
Summary	Unexpected connection abort after failed OPTIMIZE PARTITION within stored procedure	Unexpected connection abort after failed certain operations from within stored procedure

Elena Stepanova made changes - 2018-12-25 17:48

Summary

Unexpected connection abort after failed certain operations from within stored procedure

Unexpected connection abort after certain operations from within stored procedure

Elena Stepanova made changes - 2019-03-14 17:19

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Description	{code:sql} CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; SELECT 1; # Cleanup DROP PROCEDURE sp; {code} {code:sql\|title=Actual result} MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; Query OK, 0 rows affected (0.04 sec) MariaDB [test]> CALL sp; +-------------------------+----------+----------+-----------------------------------------------+ \| Table \| Op \| Msg_type \| Msg_text \| +-------------------------+----------+----------+-----------------------------------------------+ \| test.non_existing_table \| optimize \| Error \| Table 'test.non_existing_table' doesn't exist \| \| test.non_existing_table \| optimize \| status \| Operation failed \| +-------------------------+----------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [test]> select 1; ERROR 2013 (HY000): Lost connection to MySQL server during query {code} The server doesn't crash, it's really just a connection loss. The problem is also reproducible with 10.1 client + 10.2+ server, but does not seem to be reproducible with 10.2 client + 10.1 server (or 10.2 + 10.2 and higher). Another example: {code:sql} CREATE PROCEDURE sp() SHOW USER_STATISTICS; CALL sp; SELECT "Still here" AS check_alive; # Cleanup DROP PROCEDURE sp; {code}	{code:sql} CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; SELECT 1; # Cleanup DROP PROCEDURE sp; {code} {code:sql\|title=Actual result} MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; Query OK, 0 rows affected (0.04 sec) MariaDB [test]> CALL sp; +-------------------------+----------+----------+-----------------------------------------------+ \| Table \| Op \| Msg_type \| Msg_text \| +-------------------------+----------+----------+-----------------------------------------------+ \| test.non_existing_table \| optimize \| Error \| Table 'test.non_existing_table' doesn't exist \| \| test.non_existing_table \| optimize \| status \| Operation failed \| +-------------------------+----------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [test]> select 1; ERROR 2013 (HY000): Lost connection to MySQL server during query {code} Another example: {code:sql} CREATE PROCEDURE sp() SHOW USER_STATISTICS; CALL sp; SELECT "Still here" AS check_alive; # Cleanup DROP PROCEDURE sp; {code} The server doesn't crash, it's really just a connection loss. The problem is also reproducible with 10.1 client + 10.2+ server, but does not seem to be reproducible with 10.2 client + 10.1 server (or 10.2 + 10.2 and higher). However, 10.2+ shows a different problem on the same test case, but with {{--ps-protocol}}: {code:sql} CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; Table Op Msg_type Msg_text test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist test.non_existing_table optimize status Operation failed SELECT 1; bug2.ps1d [ fail ] Test ended at 2019-03-14 19:19:43 CURRENT_TEST: bug2.ps1d mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement {code}

Elena Stepanova made changes - 2019-03-14 17:20

Description

{code:sql}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
SELECT 1;

# Cleanup
DROP PROCEDURE sp;
{code}

{code:sql|title=Actual result}
MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CALL sp;
+-------------------------+----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+----------+----------+-----------------------------------------------+
| test.non_existing_table | optimize | Error | Table 'test.non_existing_table' doesn't exist |
| test.non_existing_table | optimize | status | Operation failed |
+-------------------------+----------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
{code}

Another example:
{code:sql}
CREATE PROCEDURE sp() SHOW USER_STATISTICS;
CALL sp;

SELECT "Still here" AS check_alive;

# Cleanup
DROP PROCEDURE sp;
{code}

The server doesn't crash, it's really just a connection loss.
The problem is also reproducible with 10.1 client + 10.2+ server, but *does not seem to be reproducible with 10.2 client* + 10.1 server (or 10.2 + 10.2 and higher).

However, 10.2+ shows a different problem on the same test case, but with {{--ps-protocol}}:
{code:sql}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
Table Op Msg_type Msg_text
test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist
test.non_existing_table optimize status Operation failed
SELECT 1;
bug2.ps1d [ fail ]
Test ended at 2019-03-14 19:19:43

CURRENT_TEST: bug2.ps1d
mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement
{code}

{code:sql}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
SELECT 1;

# Cleanup
DROP PROCEDURE sp;
{code}

{code:sql|title=Actual result}
MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CALL sp;
+-------------------------+----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+----------+----------+-----------------------------------------------+
| test.non_existing_table | optimize | Error | Table 'test.non_existing_table' doesn't exist |
| test.non_existing_table | optimize | status | Operation failed |
+-------------------------+----------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
{code}

Another example:
{code:sql}
CREATE PROCEDURE sp() SHOW USER_STATISTICS;
CALL sp;

SELECT "Still here" AS check_alive;

# Cleanup
DROP PROCEDURE sp;
{code}

The server doesn't crash, it's really just a connection loss.
The problem is also reproducible with 10.1 client + 10.2+ server, but *does not seem to be reproducible with 10.2 client* + 10.1 server (or 10.2 + 10.2 and higher).

However, 10.2+ shows a different problem on the same test case, but with {{--ps-protocol}}:
{noformat}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
Table Op Msg_type Msg_text
test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist
test.non_existing_table optimize status Operation failed
SELECT 1;
bug2.ps1d [ fail ]
Test ended at 2019-03-14 19:19:43

CURRENT_TEST: bug2.ps1d
mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement
{noformat}

Elena Stepanova made changes - 2019-03-14 17:20

Description

{code:sql}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
SELECT 1;

# Cleanup
DROP PROCEDURE sp;
{code}

{code:sql|title=Actual result}
MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CALL sp;
+-------------------------+----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+----------+----------+-----------------------------------------------+
| test.non_existing_table | optimize | Error | Table 'test.non_existing_table' doesn't exist |
| test.non_existing_table | optimize | status | Operation failed |
+-------------------------+----------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
{code}

Another example:
{code:sql}
CREATE PROCEDURE sp() SHOW USER_STATISTICS;
CALL sp;

SELECT "Still here" AS check_alive;

# Cleanup
DROP PROCEDURE sp;
{code}

The server doesn't crash, it's really just a connection loss.
The problem is also reproducible with 10.1 client + 10.2+ server, but *does not seem to be reproducible with 10.2 client* + 10.1 server (or 10.2 + 10.2 and higher).

However, 10.2+ shows a different problem on the same test case, but with {{--ps-protocol}}:
{noformat}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
Table Op Msg_type Msg_text
test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist
test.non_existing_table optimize status Operation failed
SELECT 1;
bug2.ps1d [ fail ]
Test ended at 2019-03-14 19:19:43

CURRENT_TEST: bug2.ps1d
mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement
{noformat}

{code:sql}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
SELECT 1;

# Cleanup
DROP PROCEDURE sp;
{code}

{code:sql|title=Actual result}
MariaDB [test]> CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CALL sp;
+-------------------------+----------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+----------+----------+-----------------------------------------------+
| test.non_existing_table | optimize | Error | Table 'test.non_existing_table' doesn't exist |
| test.non_existing_table | optimize | status | Operation failed |
+-------------------------+----------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
{code}

Another example:
{code:sql}
CREATE PROCEDURE sp() SHOW USER_STATISTICS;
CALL sp;

SELECT "Still here" AS check_alive;

# Cleanup
DROP PROCEDURE sp;
{code}

The server doesn't crash, it's really just a connection loss.
The problem is also reproducible with 10.1 client + 10.2+ server, but *does not seem to be reproducible with 10.2 client* + 10.1 server (or 10.2 + 10.2 and higher).

However, *10.2+ shows a different problem* on the same test case, but with {{--ps-protocol}}:
{noformat}
CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
CALL sp;
Table Op Msg_type Msg_text
test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist
test.non_existing_table optimize status Operation failed
SELECT 1;
bug2.ps1d [ fail ]
Test ended at 2019-03-14 19:19:43

CURRENT_TEST: bug2.ps1d
mysqltest: At line 3: query 'SELECT 1' failed: 2031: No data supplied for parameters in prepared statement
{noformat}

Elena Stepanova made changes - 2019-03-14 17:20

Component/s

Prepared Statements [ 10804 ]

Elena Stepanova made changes - 2019-03-14 17:23

Labels

affects-tests

Sergei Golubchik made changes - 2019-03-14 17:36

Assignee

Sergei Golubchik [ serg ]

Vladislav Vaintroub [ wlad ]

Vladislav Vaintroub added a comment - 2019-04-03 19:26 - edited

there is an extra OK packet sent by the server as part of CALL sp
i.e there is

a resultset that does not end with STATUS_MORE_RESULTS_EXIST
an OK packet after this, that client does not expect

client, after SELECT "Still here"

gets an OK packet it does not expect (from the previous CALL sp)
pkt_nr in this packet is 27 . In 5.5, client chokes on that, as it expects pkt_nr=1, and closes connection. In 10.2+, client does not close connection, but it still does not expect this OK packet.

Vladislav Vaintroub added a comment - 2019-04-03 19:26 - edited there is an extra OK packet sent by the server as part of CALL sp i.e there is a resultset that does not end with STATUS_MORE_RESULTS_EXIST an OK packet after this, that client does not expect client, after SELECT "Still here" gets an OK packet it does not expect (from the previous CALL sp) pkt_nr in this packet is 27 . In 5.5, client chokes on that, as it expects pkt_nr=1, and closes connection. In 10.2+, client does not close connection, but it still does not expect this OK packet.

Vladislav Vaintroub added a comment - 2019-04-04 07:56

Ok, the actual problem is the missing MORE_RESULTS_EXIST flag after result set

Vladislav Vaintroub added a comment - 2019-04-04 07:56 Ok, the actual problem is the missing MORE_RESULTS_EXIST flag after result set

Vladislav Vaintroub made changes - 2019-04-04 14:52

issue.field.resolutiondate

2019-04-04 14:52:45.0

2019-04-04 14:52:45.813

Vladislav Vaintroub made changes - 2019-04-04 14:52

Fix Version/s		5.5.64 [ 23303 ]
Fix Version/s		10.1.39 [ 23305 ]
Fix Version/s		10.2.24 [ 23308 ]
Fix Version/s		10.3.15 [ 23309 ]
Fix Version/s		10.4.4 [ 23310 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 90413 ]

MariaDB v4 [ 155147 ]

People

Assignee:: Vladislav Vaintroub

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-11-03 19:59

Updated:: 2019-09-03 09:24

Resolved:: 2019-04-04 14:52

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration