MySQL Bugs: #27023: out of memory error with COUNT(DISTINCT IF(...))

Bug #27023	out of memory error with COUNT(DISTINCT IF(...))
Submitted:	10 Mar 2007 14:38	Modified:	16 Jan 2009 12:40
Reporter:	Travis Basevi	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.32-Debian_3-log, 5.0.67	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Every couple of weeks we've been getting Out of Memory errors, despite there being a lot of spare memory on the system. Normally it's when the system is being used and the server gets restarted and the problem goes away. It wasn't until today that I was able to investigate it. 

A command as apparently straight-forward as the following would cause the error:

mysql> SELECT
    -> mt.team_id,
    -> COUNT(DISTINCT IF(mt.result=1,mt.match_id,NULL)) AS won,
    -> COUNT(DISTINCT IF(mt.result=2,mt.match_id,NULL)) AS lost,
    -> COUNT(DISTINCT IF(mt.result=3,mt.match_id,NULL)) AS tied,
    -> COUNT(DISTINCT IF(mt.result=4,mt.match_id,NULL)) AS drawn
    -> FROM eng_match_team mt
    -> WHERE mt.team_id=9
    -> GROUP BY mt.team_id
    -> ;
ERROR 5 (HY000): Out of memory (Needed 33554404 bytes)

At the time there was over a gig of memory spare, plus another untouched 3 gigs of swap:

             total       used       free     shared    buffers     cached
Mem:       8310348    7006892    1303456          0     237356    5342048
-/+ buffers/cache:    1427488    6882860
Swap:      2931852          0    2931852

I know this is an inefficient query on face value, but I've broken down the complicated query to its most simplest forms with the error still being reproduced. eng_match_team is a relatively simple table with only 16k rows. In the above mt.team_id=9 reduces the query to 421 rows (but note the error still occurs without the WHERE clause). Removing a couple of the COUNTs makes the query work fine. It also seemed strange that no matter how complex the query was made, 33554404 bytes would always appear in the error message.

Other queries would run fine on the server, even obviously complicated ones. Although when this error has occurred in the past it would be for different queries.

FLUSH TABLES, CHECK TABLE, shutting down other processes all had no effect. The server had been up for 3 days.

How to repeat:
Almost impossible to repeat, as restarting the server would get rid of the problem.

I can supply the table if needed.

Thank you for a problem report. Please, send your my.cnf file content and the results of:

df -k
ulimit -a

Linux commands from the user that runs MySQL server. Send also the results of SHOW CREATE TABLE and SHOW TABLE STATUS for the eng_match_team table.

uname -a results are still missed. I want to check is it 32-bit or 64-bit Linux. Is it possible for you to try with a newer version, 5.0.37/5.0.38?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

uname -a results and check with 5.0.41/5.0.40 are still needed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi, I have a very similar problem with my 32bit ubuntu system.
The interesting thing is this:

mysql> SELECT COUNT(DISTINCT source_uid) AS cnt FROM fk_gamescore;ERROR 5 (HY000): Out of memory (Needed 33554364 bytes)
mysql> SELECT COUNT(*) AS cnt FROM (SELECT DISTINCT source_uid FROM fk_gamescore) t;
+-------+
| cnt   |
+-------+
| 18296 | 
+-------+
1 row in set (0.06 sec)

It seems like it's a problem specifically for COUNT(DISTINCT). 

david@serv-03:~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
david@serv-03:~$ uname -a
Linux serv-03 2.6.24-19-server #1 SMP Wed Aug 20 23:54:28 UTC 2008 i686 GNU/Linux
david@serv-03:~$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1            376800264  26054392 331756244   8% /
varrun                 2072580        72   2072508   1% /var/run
varlock                2072580         0   2072580   0% /var/lock
udev                   2072580        32   2072548   1% /dev
devshm                 2072580         0   2072580   0% /dev/shm

david@serv-03:~$ mysqld --version
mysqld  Ver 5.0.51a-3ubuntu5.4-log for debian-linux-gnu on i486 ((Ubuntu))

Please, try to repeat with a newer version, 5.0.67, and inform about the results.

Same thing.

mysql> SELECT COUNT(DISTINCT source_uid) AS cnt FROM fk_gamescore;
ERROR 5 (HY000): Out of memory (Needed 33554364 bytes)
mysql> SELECT COUNT(*) AS cnt FROM (SELECT DISTINCT source_uid FROM fk_gamescore) t;
+-------+
| cnt   |
+-------+
| 18683 | 
+-------+
1 row in set (0.08 sec)

mysql> Bye
david@serv-03:~$ mysqld --version
081202  9:30:58 [Warning] option 'wait_timeout': unsigned value 34560000 adjusted to 31536000
mysqld  Ver 5.0.67-0ubuntu6-log for debian-linux-gnu on i486 ((Ubuntu))

I once had a similar problem: bug #31590 but that should be fixed by now

No! bug #31590 said "Documented bugfix in the 5.0.60".
This bug exists in 5.0.67!!!!

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug is not fixed and I have had feedback.  Please mark it open.

I couldn't repeat this issue with current source tree. Please wait for next release and re-open or comment here if you still get the same problem:

mysql 5.0 > SELECT COUNT(DISTINCT source_uid) AS cnt FROM fk_gamescore;
+-------+
| cnt   |
+-------+
| 65536 |
+-------+
1 row in set (0.14 sec)

mysql 5.0 > SELECT COUNT(*) AS cnt FROM (SELECT DISTINCT source_uid FROM fk_gamescore) t;
+-------+
| cnt   |
+-------+
| 65536 |
+-------+
1 row in set (0.11 sec)

mysql 5.0 >

I have the same problem with mysql 5.0.58. The machine has been uptime for 40 days. 
The queries that spawn the error "ERROR 5 (HY000): Out of memory (Needed 65535968 bytes)" are those with select count(distinct row) from table, here's a simple example: "select count(distinct row1) from (select 3 as row1) as table1;"

I found this bug in version 5.1.32 & 5.1.33

I am getting the same on 5.0.46

mysql> SELECT count(distinct SRCMEMBER) FROM `ARCHIVEEVENT` `A0` WHERE `A0`.`JDOCLASSX` = 'net.playtxt.eventarchive.ActivateAccountEvent' AND `A0`.`DATE` >= "2010-10-01 00:00:00.637" AND `A0`.`DATE` < "2010-10-13 00:00:00.639";
ERROR 5 (HY000): Out of memory (Needed 33554404 bytes)

Note, happens on select(distinct x) and the Needed bytes are EXACTLY the same as a previous post which suggests its nothing to do with the particular table SQL. The table being queried is a relatively large innodb table with 9 million rows.

I hope someone can track this down.

All, current versions are 5.0.91,5.1.51, and testing ancient versions makes no sense.

Then, please set the following variables in my.cnf, or per session:

set session read_buffer_size=128*1024;
set session sort_buffer_size=256*1024;
set session tmp_table_size=384*1024;
set session max_heap_table_size=512*1024;

and run the query and check the output.

The trouble with this bug is its hard to repeat. Its happening for us on a server that has been running uninterrupted for about a year. The same server version running on almost the same DB on another server but for less time does not have this problem - the same queries work fine on that server.

Could it be you're running a 32 bits server, but with more than 4Go of ram ?

I just ran into this problem, and the correction has been to lower some values of mysql configuration (tmp and max_head in our case were at 1Go, lowering to 512M pushed mysql to NOT try to go over 4Go).

I face the same problem with a query that return just 56 rows: SELECT COUNT(DISTINCT d.codigo) FROM ped_disciplina as d inner join ped_curso_disciplina c on (c.disciplina = d.codigo) WHERE c.curso = 59.
When I takes out the distinct clause from the query, the problem don't shows up again.
Sorry about my english mistakes

This is happening all of a sudden on Windows 2008 Server every 15 minutes to 4 hours. It never happened before yesterday and I had not made any changes. Running version 5.5.9.

Here is the query:

SELECT COUNT(distinct(multiple_list_number)) as row_count FROM residential left join mls_images ON Multiple_List_Number = mls_number AND primary_img = 1 WHERE (Status = 'Active' OR (Status = 'Pending' AND Continue_To_Show = 'Yes')) AND Town = 'Stonington'

Forgot to mention that restarting MySQL is what fixes it temporarily until it happens again and brings down all my searches.

Steve, we cannot realistically do anything about intermittent problems in 5.5.9.  If you have same issues on 5.5.41 or 5.6.22  then we will re-investigate of course.

I just upgraded to 5.6.23 (Windows Server 2008 32) and the problem with COUNT(DISTINCT... out of memory is even worse now. This definitely seems to still be a bug. The problem is that it's not consistently repeatable, but once it starts, all queries with a SELECT COUNT(DISTINCT... fail while others still work.

Sorry - it's version 5.6.22. Here is the error in my application log:

[MySQL][ODBC 3.51 Driver][mysqld-5.6.22-log]Out of memory (Needed 1048548 bytes)

I'm sorry - it turns out I had not run mysql_upgrade when I upgraded from 5.5 to 5.6. After doing that I am not getting errors. However, I also had removed all the queries that included COUNT(DISTINCT..., so I'm not sure if it was the upgrade or removing the queries that fixed it.