Bug #5323 out of memory raised after an ORDER BY

Submitted: 31 Aug 2004 19:46 Modified: 31 Aug 2004 21:13
Reporter: Quentin Ochem Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.4 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[31 Aug 2004 19:46] Quentin Ochem
Description:
When I do the query

SELECT DISTINCT * FROM employe e1
        WHERE e1.Cle != 28
           AND DateMaJ = (SELECT MAX(DateMaJ) FROM employe WHERE Cle = e1.Cle)

I get results. But, if I add ORDER BY Nom, I get the error : 

Out of memory; restart server and try again (needed 65528 bytes).

Obviously, it's a bug, since I'm think I'm not using so more memory by ordering my results.

How to repeat:
Do this query :

SELECT DISTINCT * FROM employe e1
        WHERE e1.Cle != 28
           AND DateMaJ = (SELECT MAX(DateMaJ) FROM employe WHERE Cle = e1.Cle)
        ORDER BY Nom

on the database initialized below :

# phpMyAdmin SQL Dump
# version 2.5.7-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Aug 31, 2004 at 07:45 PM
# Server version: 4.1.4
# PHP Version: 5.0.0
# 
# Database : `bug`
# 

# --------------------------------------------------------

#
# Table structure for table `employe`
#

CREATE TABLE `employe` (
  `Cle` int(11) NOT NULL default '0',
  `DateMaJ` datetime NOT NULL default '0000-00-00 00:00:00',
  `Prenom` varchar(255) NOT NULL default '',
  `Nom` varchar(255) NOT NULL default '',
  `NomJa` varchar(255) NOT NULL default '',
  `PrenomJa` varchar(255) NOT NULL default '',
  `EMail` varchar(255) NOT NULL default '',
  `Sexe` enum('masculin','feminin') NOT NULL default 'masculin',
  `DateNaissance` date default NULL,
  `DateEntree` date NOT NULL default '0000-00-00',
  `CleFonction` int(11) default NULL,
  `CleResponsable` int(11) default NULL,
  `Identifiant` varchar(255) NOT NULL default '',
  `MotDePasse` varchar(255) NOT NULL default '',
  `DelegationManagerDefaut` int(11) default NULL,
  `Responsable` enum('oui','non') NOT NULL default 'non',
  `Administrateur` enum('oui','non') NOT NULL default 'non',
  `Validant` enum('oui','non') NOT NULL default 'non',
  `JourReInitialisation` int(11) NOT NULL default '0',
  `MoisReInitialisation` int(11) NOT NULL default '0',
  `CleContrat` int(11) default NULL,
  `Langue` varchar(255) NOT NULL default '',
  `ClePresenceDefaut` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#
# Dumping data for table `employe`
#

INSERT INTO `employe` VALUES (100, '2004-08-31 18:22:53', 'Quentin', 'Ochem', '', '', '', 'masculin', NULL, '0000-00-00', NULL, NULL, 'q.ochem', 'asimov', NULL, 'oui', 'oui', 'oui', 0, 0, NULL, '', NULL);
INSERT INTO `employe` VALUES (28, '2004-08-31 15:33:23', 'Marc', 'Vente', '', '', 'marc.vente@areva.com', 'masculin', '1982-07-16', '2004-08-27', NULL, NULL, 'm.vente', 'VentE12345M', NULL, 'non', 'non', 'non', 1, 1, NULL, 'en', NULL);
[31 Aug 2004 20:08] MySQL Verification Team
What server are you using ?
I tested with a debug server which I built myself and I assume it is
from practically the same source of the package release.

I wasn't able to repeat the behavior reported.
[31 Aug 2004 20:18] Quentin Ochem
I've found another interesting behaviour : by removing the DISTINCT statement, there is no problem anymore.
[31 Aug 2004 20:20] Quentin Ochem
I'm using mysql 4.1.4 through apache 1.3.31 and php 5.0.0
[31 Aug 2004 21:13] MySQL Verification Team
I downloaded the release package and tested the server mysqld-nt.exe
with the mysql.exe client and wasn't able to repeat.

Could you please provide the my.ini/my.cnf used ?

Thanks in advance.
[31 Aug 2004 21:39] Quentin Ochem
Here is the my.ini file i'm using.

I found 4 different cnf files on bin/ folder, by I didn't found any my.cnf on my disc : my_small.cnf, my_medium.cnf, my_large.cnf and my_huge.cnf. Dont't know witch one is interresting you, or should I find someone elsewhere ?

# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
# 
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
#skip-innodb
bind-address=127.0.0.1
port=3306
#socket=MySQL
skip-locking
set-variable	= key_buffer=16K
set-variable	= max_allowed_packet=1M
set-variable	= thread_stack=64K
set-variable	= table_cache=4
set-variable	= sort_buffer=64K
set-variable	= net_buffer_length=2K
server-id	= 1

# Uncomment the following if you want to log updates
#log-bin

# Uncomment the following rows if you move the MySQL distribution to another
# location
basedir = C:/Program Files/mysql/
datadir = C:/Program Files/mysql/data/

# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:100M
#innodb_data_home_dir = c:\ibdata
#innodb_log_group_home_dir = c:\iblogs
#innodb_log_arch_dir = c:\iblogs
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable	= max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable	= key_buffer=8M
set-variable	= sort_buffer=8M

[myisamchk]
set-variable	= key_buffer=8M
set-variable	= sort_buffer=8M

[mysqlhotcopy]
interactive-timeout
[20 Feb 2007 10:22] Robert Grellmann
I had exactly this behavior with MySQL 5.0.6 on Windows XP.
After updating to MySQL 5.1.12-beta it worked correctly.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.