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;