Bug #26646 ERROR 2006 (HY000): MySQL server has gone away after executing query
Submitted: 26 Feb 2007 20:54 Modified: 15 Mar 2007 13:42
Reporter: Aldo Gonzalez Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.33 OS:FreeBSD (FreeBSD)
Assigned to: CPU Architecture:Any

[26 Feb 2007 20:54] Aldo Gonzalez
Description:
I ran the script below using mysql-client 5.0.33 on mysql-server 5.0.33.
Also happens on mysql 5.1.15-beta on FreeBSD 6.2 using mysql 5.1.15-beta client
I expected for the client to remain connected after executing the select statement.
After executing the query and attempting another query I receive:
ERROR 2006 (HY000): MySQL server has gone away
No Connection. Trying to reconnect...

Version Information:
Compiled on FreeBSD 6.2-RELEASE-p1 using:
cd /usr/ports/database/mysql50-server && make WITH_OPENSSL=yes WITHOUT_INNODB=yes install clean

My my.cnf file is empty.

mysql> show variables LIKE 'version%';
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| version                 | 5.0.33-log                                                 |
| version_bdb             | Sleepycat Software: Berkeley DB 4.1.24: (January  9, 2007) |
| version_comment         | FreeBSD port: mysql-server-5.0.33                          |
| version_compile_machine | i386                                                       |
| version_compile_os      | portbld-freebsd6.2                                         |
+-------------------------+------------------------------------------------------------+
5 rows in set (0.00 sec)

Workaround:
Remove one of the ORDER BY fields

How to repeat:
DROP DATABASE IF EXISTS Bug;
CREATE DATABASE Bug;
USE Bug;
CREATE TABLE `A` (
  `aID` int(10) unsigned NOT NULL auto_increment,
  `aData` varchar(15) NOT NULL default '',
  `aPhase` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`aID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `B` (
  `bID` int(10) unsigned NOT NULL auto_increment,
  `baID` int(10) unsigned NOT NULL default '0',  
  PRIMARY KEY  (`bID`),
  KEY `baID` (`baID`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `A` VALUES (306863,'01133935022',-10),(306864,'',-10);
INSERT INTO `B` VALUES (1532595,306863),(1532597,306864);

/* Run this multiple times to drop connections to server */
SELECT (SELECT SUM(aID) FROM B WHERE baID = aID), COUNT(DISTINCT(bID))
FROM A c
LEFT JOIN B ON aID = baID
WHERE aPhase = -10
AND (SELECT COUNT(*) FROM A dup WHERE dup.aPhase = -10 AND c.aData = dup.aData) > 0
GROUP BY aID
ORDER BY aData, aID;
[12 Mar 2007 13:20] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer versions, 5.0.37 and 5.1.16. I was not able to repeat with latest 5.0.38-BK on Linux, trying to execute your query many times, hence the request.
[15 Mar 2007 13:42] Aldo Gonzalez
I have executed my test case along with the original query causing the issue on my production database on both mysql 5.0.37 and 5.1.16 and the problem no longer arises.

Thank you.