Bug #2118 Inappropriate 'Out of memory' error
Submitted: 15 Dec 2003 11:09 Modified: 17 Dec 2003 6:10
Reporter: Are you mortal Then prepare to die. Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:mysql Ver 12.21 Distrib 4.0.15a OS:V5.1 732 alpha dec-osf5.1
Assigned to: CPU Architecture:Any

[15 Dec 2003 11:09] Are you mortal Then prepare to die.
Description:
running a INSERT INTO ... SELECT query from a client onto a remote server causes an 'out of memory: needed bla' error. 

this is innapropriate, as the client doesn't need much data to confirm the sucess (or failure) of this query!

the server is blowing a fuse!

Also strange, the query alone (just the SELECT part) works fine, returning all the  relevant data to the client. When the INSERT INTO is added it freaks out and gibbers about memory.

How to repeat:

Do a big query! and insert the results into a table (the bug occurs using either a remote client or as a client on the server). The results set I get isn't too big, but the query to produce it is quite complex...

    ->      COUNT( DISTINCT
    ->               ALT_LOC1, CHAIN_ID1, RES_SEQ1, I_CODE1,
    ->               ALT_LOC2, CHAIN_ID2, RES_SEQ2, I_CODE2 ) AS COUNT,
    ->
    ->      IF(MIN(CHAIN_ID1) != MAX(CHAIN_ID1), 'CHIMERA',
    ->       IF(MIN(CHAIN_ID2) != MAX(CHAIN_ID2), 'CHIMERA',
    ->        IF(CHAIN_ID1 != CHAIN_ID2, 'INTER', 'INTRA'))) AS TYPE
    ->     FROM psimap_1_63_3.psimap_raw
    ->     WHERE DISTANCE < 7 * 1000
    ->     GROUP BY SUNID1, SUNID2

+------------+------+---------------+------+---------+------+---------+-----------------------------+
| table      | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+------------+------+---------------+------+---------+------+---------+-----------------------------+
| psimap_raw | ALL  | DISTANCE      | NULL |    NULL | NULL | 3448098 | Using where; Using filesort |
+------------+------+---------------+------+---------+------+---------+-----------------------------+

My DISTANCE parameter above is supposed to go from 3 to 10, but failed at 4. When I ran the select part alone at 4, my insert continued to work up to 6, failing at 7 (obviously cashing the results).

Running the above on the client...

8536 rows in set (18.71 sec)

and on the server...

8536 rows in set (18.33 sec)

Just tried adding the INSERT INTO after running the above SELECT parts sucessfully, but...

ERROR 5: Out of memory (Needed 3900555 bytes)

on both client and server.

Suggested fix:
Sort it out
[15 Dec 2003 11:15] MySQL Verification Team
"Out of memory" error is on server and not on client.

It can be caused by inapproprate shell limit values and bad tuning.
[15 Dec 2003 11:55] Are you mortal Then prepare to die.
Why are you so dumb?

Why the beggary would it work with no insert, and then fail on insert into?

are you some kind of fool?

of course it is a bug.

Did you even read my report?

why do I bother?
[15 Dec 2003 13:37] Sergei Golubchik
Please show the *complete* INSERT .. SELECT statement (not the part of it) and SHOW CREATE TABLE for all tables involved.

(as for the answer to "why do you bother?" - I guess, because you want to help us to fix this bug, and we appreciate it, but we do need to know more about the problem than what you provided)
[16 Dec 2003 1:46] Are you mortal Then prepare to die.
I am happy to help because I love mysql. I love the people that make it and I love the million and one ways that mysql has made my life easier. Thank you for all those things.

for ( @distances ){
 
  print "Doing: $_\n";
   
  doQuery("
    DELETE FROM $psiDb.psimap
    WHERE DIST = $_
  ") or die;
   

  my $QUERY = "
    INSERT INTO $psiDb.psimap
    SELECT
     PDB,
     SUNID1,
     SUNID2,
     SCCS1,
     SCCS2,
     $_,
     COUNT( DISTINCT ALT_LOC1, RES_NAME1, CHAIN_ID1, RES_),
                                                                                
     IF(MIN(CHAIN_ID1) != MAX(CHAIN_ID1), 'CHIMERA',
      IF(MIN(CHAIN_ID2) != MAX(CHAIN_ID2), 'CHIMERA',
       IF(CHAIN_ID1 != CHAIN_ID2, 'INTER', 'INTRA'))) AS TYPE
    FROM $psiDb.psimap_raw
    WHERE DISTANCE < $_ * 1000
    GROUP BY SUNID1, SUNID2
  ";
}

CREATE TABLE psimap (
  PDB char(4) NOT NULL default '',
  SUNID1 mediumint(8) unsigned NOT NULL default '0',
  SUNID2 mediumint(8) unsigned NOT NULL default '0',
  SCCS1 bigint(20) unsigned NOT NULL default '0',
  SCCS2 bigint(20) unsigned NOT NULL default '0',
  DIST tinyint(3) unsigned NOT NULL default '0',
  COUNT smallint(5) unsigned NOT NULL default '0',
  INTERACTION enum('INTRA','INTER','CHIMERA') NOT NULL default 'INTRA',
  PRIMARY KEY  (SUNID1,SUNID2,DIST),
  KEY SUNID2 (SUNID2),
  KEY SCCS1 (SCCS1,SCCS2),
  KEY SCCS2 (SCCS2),
  KEY DIST (DIST),
  KEY COUNT (COUNT),
  KEY INTERACTION (INTERACTION)
) TYPE=MyISAM;
 
--
-- Table structure for table `psimap_raw`
--
 
CREATE TABLE psimap_raw (
  PDB char(4) NOT NULL default '',
  SUNID1 mediumint(8) unsigned NOT NULL default '0',
  SUNID2 mediumint(8) unsigned NOT NULL default '0',
  SCCS1 bigint(20) unsigned NOT NULL default '0',
  SCCS2 bigint(20) unsigned NOT NULL default '0',
  ALT_LOC1 char(1) NOT NULL default '',
  RES_NAME1 char(3) NOT NULL default '',
  CHAIN_ID1 char(1) NOT NULL default '',
  RES_SEQ1 smallint(6) NOT NULL default '0',
  I_CODE1 char(1) NOT NULL default '',
  OCCUPANCY1 tinyint(3) unsigned NOT NULL default '0',
  ALT_LOC2 char(1) NOT NULL default '',
  RES_NAME2 char(3) NOT NULL default '',
  CHAIN_ID2 char(1) NOT NULL default '',
  RES_SEQ2 smallint(6) NOT NULL default '0',
  I_CODE2 char(1) NOT NULL default '',
  OCCUPANCY2 tinyint(3) unsigned NOT NULL default '0',
  INTERACTION enum('RR','CC','RC','CR') NOT NULL default 'RR',
  DISTANCE mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (ALT_LOC1,CHAIN_ID1,RES_SEQ1,I_CODE1,ALT_LOC2,CHAIN_ID2,RES_SEQ2,I_CODE2,INTERACTION),
  KEY PDB (PDB),
  KEY SUNID1 (SUNID1,SUNID2),
  KEY SUNID2 (SUNID2),
  KEY SCCS1 (SCCS1),
  KEY SCCS2 (SCCS2),
  KEY ALT_LOC1 (ALT_LOC1),
  KEY ALT_LOC2 (ALT_LOC2),
  KEY RES_NAME1 (RES_NAME1),
  KEY RES_NAME2 (RES_NAME2),
  KEY CHAIN_ID1 (CHAIN_ID1),
  KEY CHAIN_ID2 (CHAIN_ID2),
  KEY RES_SEQ1 (RES_SEQ1),
  KEY RES_SEQ2 (RES_SEQ2),
  KEY I_CODE1 (I_CODE1),
  KEY I_CODE2 (I_CODE2),
  KEY OCCUPANCY (OCCUPANCY1),
  KEY OCCUPANCY2 (OCCUPANCY2),
  KEY INTERACTION (INTERACTION),
  KEY DISTANCE (DISTANCE)
) TYPE=MyISAM;
 
mysql> select count(*) from psimap_raw;
+----------+
| count(*) |
+----------+
|  3448098 |
+----------+
1 row in set (0.01 sec)
[16 Dec 2003 1:53] Are you mortal Then prepare to die.
Just for the record, I can repeat this bug very easily.
[16 Dec 2003 10:55] Are you mortal Then prepare to die.
See also 

http://www.geocrawler.com/archives/3/8/1997/8/0/51385/

This appears to be a problem on ALPHA machines.

The server spews up an 'out of memory' warning on certain selects.
[17 Dec 2003 6:10] Sergei Golubchik
see bug #2136 for more information
http://bugs.mysql.com/bug.php?id=2136