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
Triage: Triaged: D2 (Serious)

[30 Mar 2009 14:47] Luca Zavarella
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
[30 Mar 2009 14:56] Valeriy Kravchuk
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?
[30 Mar 2009 15:03] Luca Zavarella
Yes, "orderheader" is in my current schema.
I'll install the 5.1.32 version and try it.

Thanks.
Luca
[30 Mar 2009 15:17] Valeriy Kravchuk
I can not repeat this when refers to existing table in 5.1.32, hence the request.
[30 Mar 2009 15:22] Luca Zavarella
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?
[30 Mar 2009 15:31] Valeriy Kravchuk
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...
[30 Mar 2009 19:04] Luca Zavarella
I got the same error on 5.1.32 version
[31 Mar 2009 6:02] Sveta Smirnova
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.
[31 Mar 2009 6:06] Valeriy Kravchuk
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.
[31 Mar 2009 6:10] Valeriy Kravchuk
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.
[31 Mar 2009 6:12] Valeriy Kravchuk
Same results with mysql client from 5.1.32. So, the problem is in server.
[31 Mar 2009 6:18] Valeriy Kravchuk
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
[20 Apr 2009 7:47] Luca Zavarella
Any news on this bug?
Thank you.
[28 Apr 2009 10:50] Bugs System
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.
[29 Apr 2009 9:52] Alexander Nozdrin
I think, the patch is Ok.
[30 Apr 2009 12:19] Bugs System
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.
[5 May 2009 19:38] Bugs System
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)
[6 May 2009 14:06] Bugs System
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)
[16 May 2009 0:04] Paul Dubois
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.
[15 Jun 2009 8:24] Bugs System
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)
[15 Jun 2009 9:04] Bugs System
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)
[15 Jun 2009 9:44] Bugs System
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)