| 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: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.4 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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);