MySQL Bugs: #43962: "Packets out of order" calling a SHOW TABLE STATUS

Bug #43962	"Packets out of order" calling a SHOW TABLE STATUS
Submitted:	30 Mar 2009 14:47	Modified:	16 May 2009 0:04
Reporter:	Luca Zavarella	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.30, 5.1.32, 5.1.34-bzr	OS:	Any (Vista Business)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	packets out of order, regression, show table status

Description:
MySql raise the "Packets out of order" error when I call more times a Stored Procedure with "SHOW TABLE STATUS LIKE '...'"

How to repeat:
CREATE DEFINER = 'root'@'localhost' PROCEDURE `order_get_table_info`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	SHOW TABLE STATUS LIKE 'orderheader';
END;

then I call the stored procedure 2 times and I get the error

Thank you for the problem report. Please, try to repeat with a newer version, 5.1.32, and inform about the results. 

Do you have the orderheader table in current schema?

Yes, "orderheader" is in my current schema.
I'll install the 5.1.32 version and try it.

Thanks.
Luca

I can not repeat this when refers to existing table in 5.1.32, hence the request.

The orderheader schema is:

CREATE TABLE `orderheader` (
  `OrderId` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `OrderDate` DATETIME NOT NULL,
  `DueDate` DATETIME NOT NULL,
  `ShipDate` DATETIME DEFAULT NULL,
  `CustomerId` VARCHAR(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `SalesPersonId` BIGINT(20) NOT NULL,
  `SubTotal` DECIMAL(19,4) NOT NULL,
  `TaxAmt` DECIMAL(19,4) NOT NULL,
  `Freight` DECIMAL(19,4) NOT NULL,
  `TotalDue` DECIMAL(19,4) NOT NULL,
  `Note` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
  `ModifiedDate` DATETIME NOT NULL,
  PRIMARY KEY (`OrderId`),
  KEY `CustomerId` (`CustomerId`),
  KEY `SalesPersonId` (`SalesPersonId`),
  CONSTRAINT `FK_OrderHeader_Customer` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`),
  CONSTRAINT `FK_OrderHeader_UserAccount` FOREIGN KEY (`SalesPersonId`) REFERENCES `useraccount` (`UserAccountId`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Do you need "customer" and "useraccount" schemas too?

I need to know if the problem is repeatable on 5.1.32, for the beginning. 5.1.30 can not be fixed even if there is a bug in it...

I got the same error on 5.1.32 version

Thank you for the report.

I assume you get this error in you application.

Have you read at http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html about "Multiple-result processing also is required if you execute CALL statements for stored procedures..."?

If so and if you use multiple-result processing please provide repeatable test case (example program), so we can repeat the problem.

OK. From mysql command line client, after calling this procedure several times (sometimes two, sometimes 4 or 5 - that's why I was not able to repeat with only two calls yesterday) I get (with 5.1.32 on XP):

mysql> call order_get_table_info();
ERROR 2013 (HY000): Lost connection to MySQL server during query

and Aborted_clients status variable is increased.

Note that client from 5.0.79 was used in both cases, and the problem is NOT repeatable when the same procedure is created and executed on 5.0.79.

Same results with mysql client from 5.1.32. So, the problem is in server.

This is what I've got with recent 5.1.34-debug from bzr:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table orderheader(c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter //
mysql> CREATE DEFINER = 'root'@'localhost' PROCEDURE `order_get_table_info`()
    ->     NOT DETERMINISTIC
    ->     CONTAINS SQL
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    -> SHOW TABLE STATUS LIKE 'orderheader';
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> call order_get_table_info()//
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| orderheader | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |           NULL | 2009-07-06 20:09:41 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> call order_get_table_info()//
Note: net_clear() skipped 11 bytes from file: socket (3)
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| orderheader | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |           NULL | 2009-07-06 20:09:41 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> call order_get_table_info()//
Note: net_clear() skipped 11 bytes from file: socket (3)
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| orderheader | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |           NULL | 2009-07-06 20:09:41 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> call order_get_table_info()//
Error: Packets out of order (Found: 23, expected 1)
mysql: net.c:910: my_real_read: Assertion `0' failed.
Aborted

Any news on this bug?
Thank you.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72896

2873 Sergey Glukhov	2009-04-28
      Bug#43962 "Packets out of order" calling a SHOW TABLE STATUS
      Error happens because sp_head::MULTI_RESULTS is not set for SP
      which has 'show table status' command.
      The fix is to add a SQLCOM_SHOW_TABLE_STATUS case into
      sp_get_flags_for_command() func.
     @ mysql-test/r/sp.result
        test result
     @ mysql-test/t/sp.test
        test case
     @ sql/sp_head.cc
        Error happens because sp_head::MULTI_RESULTS is not set for SP
        which has 'show table status' command.
        The fix is to add a SQLCOM_SHOW_TABLE_STATUS case into
        sp_get_flags_for_command() func.

I think, the patch is Ok.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73122

2881 Sergey Glukhov	2009-04-30
      Bug#43962 "Packets out of order" calling a SHOW TABLE STATUS
      Error happens because sp_head::MULTI_RESULTS is not set for SP
      which has 'show table status' command.
      The fix is to add a SQLCOM_SHOW_TABLE_STATUS case into
      sp_get_flags_for_command() func.
     @ mysql-test/r/sp.result
        test result
     @ mysql-test/t/sp.test
        test case
     @ sql/sp_head.cc
        Error happens because sp_head::MULTI_RESULTS is not set for SP
        which has 'show table status' command.
        The fix is to add a SQLCOM_SHOW_TABLE_STATUS case into
        sp_get_flags_for_command() func.

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:sergey.glukhov@sun.com-20090430101432-4gn94a5lgn3gte45) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.1.35, 6.0.12 changelogs.

Invoking SHOW TABLE STATUS from within a stored procedure could cause
a "Packets out of order" error.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)