MySQL Bugs: #15810: Error: 2013

Bug #15810	Error: 2013
Submitted:	16 Dec 2005 11:59	Modified:	3 Jan 2006 15:45
Reporter:	Hans Wolters	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Linux (Linux RHEL 4.x)
Assigned to:		CPU Architecture:	Any

Description:
After running a specific query which is send from a webapplication running on another server the connection gets lost.

How to repeat:
We have the following table.

+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| kabelId    | int(11)      |      | PRI | 0                 |       |
| kabelGroup | int(11)      |      |     | 0                 |       |
| header     | varchar(255) | YES  | MUL | NULL              |       |
| text       | blob         | YES  |     | NULL              |       |
| picture    | varchar(255) | YES  |     | NULL              |       |
| category   | char(2)      |      | PRI |                   |       |
| updatetime | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
| datum      | varchar(20)  |      | PRI |                   |       |
+------------+--------------+------+-----+-------------------+-------+

The following query looses the connection.

select kabelId, kabelGroup, replace(header,'<br>',' ') AS aaa, left(replace(text,'<br>','  '),200) AS bbb, category from kabelkrant where category = "N" group by  category,   kabelGroup order by kabelId;

Although we are aware that it is a bad query it shouldn't loose the connection. The customer is in the proces of rewriting their solutions.

This was tested within the perl code of the customer, the mysql client on the remote host and on the mysql client on the database server. Once you leave out the order by it runs fine, if you leave out the replace functions it runs fine too.

Thank you for a problem report. Please, send the SHOW CREATE TABLE results for that table (I need to know is it MyISAM or InnoDB, and what indexes are defined). How many rows are there in that table? Can you repeat the problem on another table with the same structure? Have you tried to check or analyze that table?

And, finally, can you, please, upgrade to some newer version? Your 4.1.7 is really old, and many bugs had been fixed since then.

Hi,

As requested:

| kabelkrant | CREATE TABLE `kabelkrant` (
  `kabelId` int(11) NOT NULL default '0',
  `kabelGroup` int(11) NOT NULL default '0',
  `header` varchar(255) default NULL,
  `text` blob,
  `picture` varchar(255) default NULL,
  `category` char(2) NOT NULL default '',
  `updatetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TI          MESTAMP,
  `datum` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`datum`,`kabelId`,`category`),
  KEY `idx_headerfull` (`header`),
  KEY `idx_categoty` (`category`),
  KEY `idx_index` (`category`,`kabelGroup`,`kabelId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Currently we are running the version RHEL is providing. Updates are a little difficult since the server it is running on (ESX) only supports RHEL 3.x. RHEL 3 is shipping mysql 3.23.58-16. Therefor we are planning to move to mysql 5.x in a few weeks since we need it for another application too.

Regards,

Hans

Thank you for the additional information. I've tried to create that table and perform the query on latest 4.1-BK build. It works OK:

mysql> CREATE TABLE `kabelkrant` (
    ->   `kabelId` int(11) NOT NULL default '0',
    ->   `kabelGroup` int(11) NOT NULL default '0',
    ->   `header` varchar(255) default NULL,
    ->   `text` blob,
    ->   `picture` varchar(255) default NULL,
    ->   `category` char(2) NOT NULL default '',
    ->   `updatetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `datum` varchar(20) NOT NULL default '',
    ->   PRIMARY KEY  (`datum`,`kabelId`,`category`),
    ->   KEY `idx_headerfull` (`header`),
    ->   KEY `idx_categoty` (`category`),
    ->   KEY `idx_index` (`category`,`kabelGroup`,`kabelId`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> select kabelId, kabelGroup, replace(header,'<br>',' ') AS aaa,
    -> left(replace(text,'<br>','  '),200) AS bbb, category from kabelkrant where
    -> category = "N" group by  category,   kabelGroup order by kabelId;
Empty set (0.08 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.17    |
+-----------+
1 row in set (0.00 sec)

So, I have to repeat my questions. Can you repeat the problem on another table with the same structure? Have you tried to check or analyze that table? How many rows are there in it?

Do you have any ideas on how to repeat than 'Lost connection' error?

Ok, we did some additional testing but are not finished yet.

The tables are not very large, in fact, some of them only have around 70 rows. What worries us is that it reloads the mysql daemon. It does this on more queries now so we are checking filesystems, etc.. for corrupted/bad blocks. The final test will be done tomorrow.

As for the version. Red Hat EL 4 does not supply any newer version. Using binairies from 
mysql.com might be a solution there, we will test this too.

Regards,

Hans

Thank you for the additional information. Please, inform about the results after consistency checks will be finished and/or when you try newer version of binaries from mysql.com.

More information, we tried to update the mysql server with the packages supplied by mysql.com but it started to complain about libz:

warning: MySQL-client-standard-4.1.16-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
        libz.so.0 is needed by MySQL-shared-standard-4.1.16-0.rhel4.i386

Yes, it's a known problem of 4.1.16 (bug #15223). So, please, try 4.1.15 or build yourself from sources. Please, inform about the results.

Hi,

Currently we have solved it by adding a limit to the query. This might still be a bug but I will have to wait until we are planning the update before I can test on the ESX server. That might take a while.

Regards,

Hans