MySQL Bugs: #13855: Server crash after a select query (lack of memory)

Bug #13855	Server crash after a select query (lack of memory)
Submitted:	7 Oct 2005 19:38	Modified:	19 Oct 2005 13:50
Reporter:	Ilya Pyatigorskiy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.0.26/4.0 BK source	OS:	Windows (Windows, Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
While developing a system, experienced a strange behavior of mysql. It crashed on a not very complex query, which included a very small amount of data.

The most strange part is: I've found a way to fix the problem - just take away "DISTINCT" word from query (ie SELECT instead of SELECT DISTINCT).

After a chrash I can find in my log file this record: "051007 14:49:28 Out of memory;  Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space". But it makes no sense, as the query I issue doesn't need a lot of memory.

PS works fine on 4.1 version

How to repeat:
After some of experiments I've got the minimum required set of data, needed to repeat the crash:

SELECT DISTINCT t_Test.t_TestId AS TestId, COUNT( DISTINCT APending.t_AnswerId ) > 0 AS ToExam
FROM t_Test
LEFT JOIN t_Result ON ( t_Test.t_TestId = t_Result.t_TestId && t_Result.Attempt =0 ) 
LEFT JOIN t_Answer AS APending ON ( APending.t_ResultId = t_Result.t_ResultId && APending.AnswerState =10 ) 
LEFT JOIN Login_has_t_Test AS L ON ( L.t_TestId = t_Test.t_TestId && L.LoginId =21 && L.t_RoleId !=1 ) 
WHERE NOW( ) > t_Test.StartTime
GROUP BY t_Test.t_TestId

CREATE TABLE `Login_has_t_Test` (
  `LoginId` int(10) unsigned NOT NULL default '0',
  `t_TestId` int(10) unsigned NOT NULL default '0',
  `t_RoleId` int(10) unsigned NOT NULL default '1',
  PRIMARY KEY  (`LoginId`,`t_TestId`,`t_RoleId`)
) TYPE=MyISAM;
INSERT INTO `Login_has_t_Test` VALUES (21, 2, 1);
INSERT INTO `Login_has_t_Test` VALUES (21, 2, 2);

CREATE TABLE `t_Answer` (
  `t_AnswerId` int(10) unsigned NOT NULL auto_increment,
  `t_ResultId` int(10) unsigned NOT NULL default '0',
  `AnswerState` int(10) unsigned default NULL,
  `BeginTime` datetime default NULL,
  `EndTime` datetime default NULL,
  `TutorId` int(10) unsigned default NULL,
  `TutorComment` text,
  `Mark` float default NULL,
  PRIMARY KEY  (`t_AnswerId`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
INSERT INTO `t_Answer` VALUES (1, 1, 20, '2005-10-06 00:43:08', '2005-10-06 00:43:18', NULL, NULL, 2);

CREATE TABLE `t_Result` (
  `t_ResultId` int(10) unsigned NOT NULL auto_increment,
  `t_TestId` int(10) unsigned NOT NULL default '0',
  `StudentId` int(10) unsigned NOT NULL default '0',
  `Attempt` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`t_ResultId`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
INSERT INTO `t_Result` VALUES (1, 2, 21, 0);

CREATE TABLE `t_Test` (
  `t_TestId` int(10) unsigned NOT NULL auto_increment,
  `Title` varchar(128) NOT NULL default '',
  `t_QDBId` int(10) unsigned NOT NULL default '0',
  `IsPermanent` tinyint(3) unsigned NOT NULL default '0',
  `StartTime` datetime default NULL,
  `Deadline` datetime default NULL,
  `QuestNum` int(10) unsigned NOT NULL default '0',
  `IsMarkable` tinyint(3) unsigned NOT NULL default '0',
  `Threshold` tinyint(3) unsigned default NULL,
  `MaxTime` int(10) unsigned NOT NULL default '0',
  `MaxAttempts` tinyint(3) unsigned NOT NULL default '0',
  `ShouldBeApproved` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`t_TestId`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
INSERT INTO `t_Test` VALUES (2, 'testtest', 5, 0, '2005-10-03 00:00:00', '2005-10-27 00:00:00', 2, 0, 0, 10, 1, 0);

Suggested fix:
Something is wrong with DISTINCT keyword. When set to DISTINCT mysql returns completely unpredictable results. For example, for such a request (it's the one, everything began from):
SELECT DISTINCT t_Test.Title AS TestTitle, t_Test.t_TestId AS TestId, t_Test.QuestNum, t_QDB.QType, COUNT( DISTINCT AMarked.t_AnswerId ) AS MarkedNum, COUNT( DISTINCT APending.t_AnswerId ) AS PendingNum
FROM t_QDB
LEFT JOIN t_Test
USING ( t_QDBId ) 
LEFT JOIN t_Result ON ( t_Test.t_TestId = t_Result.t_TestId && t_Result.Attempt =0 ) 
LEFT JOIN t_Answer AS AMarked ON ( AMarked.t_ResultId = t_Result.t_ResultId && AMarked.AnswerState =20 ) 
LEFT JOIN t_Answer AS APending ON ( APending.t_ResultId = t_Result.t_ResultId && APending.AnswerState =10 ) 
LEFT JOIN Login_has_t_Test AS L ON ( L.t_TestId = t_Test.t_TestId && L.LoginId =21 && L.t_RoleId !=1 ) 
LEFT JOIN LoginGroup_has_t_Test AS LG ON ( LG.t_TestId = t_Test.t_TestId && LG.LoginGroupId
IN ( 5 ) && LG.t_RoleId !=1 ) 
WHERE NOW( ) > t_Test.StartTime && ( L.t_TestId IS NOT NULL || LG.t_TestId IS NOT NULL ) 
GROUP BY t_QDB.t_QDBId, TestId
HAVING PendingNum !=0

it returned a row, containing PendingNum = 0, though in request it's clearly stated "HAVING PendingNum !=0"

Thank you for the bug report.

This bug not affects 4.1 and 5.0 versions.

mysql> SELECT DISTINCT t_Test.t_TestId AS TestId, COUNT( DISTINCT APending.t_AnswerId )
    -> > 0 AS ToExam
    -> FROM t_Test
    -> LEFT JOIN t_Result ON ( t_Test.t_TestId = t_Result.t_TestId && t_Result.Attempt
    -> =0 ) 
    -> LEFT JOIN t_Answer AS APending ON ( APending.t_ResultId = t_Result.t_ResultId &&
    -> APending.AnswerState =10 ) 
    -> LEFT JOIN Login_has_t_Test AS L ON ( L.t_TestId = t_Test.t_TestId && L.LoginId
    -> =21 && L.t_RoleId !=1 ) 
    -> WHERE NOW( ) > t_Test.StartTime
    -> GROUP BY t_Test.t_TestId;
ERROR 2013: Lost connection to MySQL server during query

/home/miguel/dbs/4.0/libexec/mysqld: ready for connections.
Version: '4.0.26-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1158192048 (LWP 32218)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1158192048 (LWP 32218)]
0x40254ac7 in memset () from /lib/tls/libc.so.6
(gdb) bt full
#0  0x40254ac7 in memset () from /lib/tls/libc.so.6
No symbol table info available.
#1  0xfffffff8 in ?? ()
No symbol table info available.
#2  0x083b6baa in _mymalloc (size=145764352, filename=0x8458223 "my_alloc.c", lineno=160, MyFlags=16) at safemalloc.c:195
        irem = (struct st_irem *) 0x8ac47c0
        data = 0x8ac47d8 '¥' <repeats 200 times>...
        _db_func_ = 0x450884b8 "è\204\bE^À<\b"
        _db_file_ = 0x401650c0 "]Ã\215\001è\2070"
        _db_level_ = 1158186216
        _db_framep_ = (char **) 0x83cc05e
#3  0x083b6599 in alloc_root (mem_root=0x8ad99dc, Size=4294967272) at my_alloc.c:160
        get_size = 4294967288
        block_size = <value optimized out>
        point = <value optimized out>
        next = <value optimized out>
        prev = (USED_MEM **) 0x8ae5308
#4  0x080fbd60 in sql_alloc (Size=0) at thr_malloc.cc:42
        root = <value optimized out>
        ptr = 0xa5a5a5a5 ""

mysql> SELECT DISTINCT t_Test.t_TestId AS TestId, COUNT( DISTINCT APending.t_AnswerId )
    -> > 0 AS ToExam
    -> FROM t_Test
    -> LEFT JOIN t_Result ON ( t_Test.t_TestId = t_Result.t_TestId && t_Result.Attempt
    -> =0 ) 
    -> LEFT JOIN t_Answer AS APending ON ( APending.t_ResultId = t_Result.t_ResultId &&
    -> APending.AnswerState =10 ) 
    -> LEFT JOIN Login_has_t_Test AS L ON ( L.t_TestId = t_Test.t_TestId && L.LoginId
    -> =21 && L.t_RoleId !=1 ) 
    -> WHERE NOW( ) > t_Test.StartTime
    -> GROUP BY t_Test.t_TestId
    -> ;
+--------+--------+
| TestId | ToExam |
+--------+--------+
|      2 |      0 |
+--------+--------+
1 row in set (0.00 sec)

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

mysql> SELECT DISTINCT t_Test.t_TestId AS TestId, COUNT( DISTINCT APending.t_AnswerId )
    -> > 0 AS ToExam
    -> FROM t_Test
    -> LEFT JOIN t_Result ON ( t_Test.t_TestId = t_Result.t_TestId && t_Result.Attempt
    -> =0 ) 
    -> LEFT JOIN t_Answer AS APending ON ( APending.t_ResultId = t_Result.t_ResultId &&
    -> APending.AnswerState =10 ) 
    -> LEFT JOIN Login_has_t_Test AS L ON ( L.t_TestId = t_Test.t_TestId && L.LoginId
    -> =21 && L.t_RoleId !=1 ) 
    -> WHERE NOW( ) > t_Test.StartTime
    -> GROUP BY t_Test.t_TestId;
+--------+--------+
| TestId | ToExam |
+--------+--------+
|      2 |      0 |
+--------+--------+
1 row in set (0.03 sec)

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

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/internals/31074

DISTINCT wasn't optimized away and caused creation of tmp table in wrong
case. This result in integer overrun and running out of memory.

Fixed in 4.0.27, cset 1.2164.1.1

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 4.0.27 changelog. Closed.

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/717