Duplicate entry for key 'group_key'

Question

I'm running a query in MySQL where I'm grouping by four columns and selecting a lot of things, including the four values I'm grouping by. But I get this error when I run the query:

Duplicate entry '92-0-10-7' for key 'group_key'

The 92, 0, 10, and 7 are all values of the four grouped-by columns for a particular row (actually, I checked, and there are 63 rows that fit those parameters). But I have no idea what that means, or how to run my query properly.

My query:

Create table table2 as
Select round(x, 0) as x, round(a, 0) as a, round(b, 0) as b, c, d, e, f
from table1
group by round(x, 0), round(a, 0), round(b, 0), c

The 92 would fit x, the 0 a, the 10 b, and the 7 c.

I have no indices on this table.

Thanks!

Answer 1

This is usually disk space issue. Specially temporary space (in linux /tmp). Check you database server Disk space.

Answer 2

Your issue seems like a MySQL bug. I was reviewing the logs for our production server the other day and the error logs were filled with this error. I used the workaround below and the errors are gone.

SET SESSION max_heap_table_size=536870912;

SET SESSION tmp_table_size=536870912;

Source: http://bugs.mysql.com/bug.php?id=58081

Answer 3

You should put the aliases in the group buy like this:

Create table table2 as
Select round(x, 0) as x, round(a, 0) as a, round(b, 0) as b, c, d, e, f
from table1
group by x, a, b, c;