Bug #60808 Duplicate entry for key 'group_key'; Can't write, duplicate key in table
Submitted: 8 Apr 2011 15:06 Modified: 8 Apr 2011 16:35
Reporter: Thomas Mayer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.5.11, 5.1.54, 5.1.41 OS:Any (win7 64bit, ubuntu 10.04 64 bit)
Assigned to: CPU Architecture:Any
Tags: can't write, Duplicate entry, GROUP BY, group_key, write

[8 Apr 2011 15:06] Thomas Mayer
Description:
tested with
5.5.11 (win7, 64bit)
5.1.54 (win7, 64bit)
5.1.54 (ubuntu 10.04, 64bit)

Mysql throws errors
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_67f_0'
ERROR 1062 (23000): Duplicate entry '1290-0107-OSLO-160' for key 'group_key'
when executing a GROUP by statements.

Both errors should not occur because the keys are only used internally by MySQL.
It seems as if the error is affected by
- the number of rows which should be in the resultset
- the number of attributes which are in the GROUP BY
- adding an aggregate function

The errors occur on both MyIsam (5.1, 5.5) and InnoDb (5.1)

The errors do not occur any more when
- decreasing the number of affected rows
- decreasing the number of attributes in the GROUP BY
- increasing the number of attributes in the GROUP BY (sometimes)
- selecting the rows which belong to entry reported by ERROR 1062

possibly affected bugs: #59952, #58081

How to repeat:
mysql> show create table test6;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test6 | CREATE TABLE `test6` (
  `org` int(11) DEFAULT NULL,
  `postcode` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select org, postcode, city, country from test.test6 group by 1,2,3,4 limit 1;
+------+----------+-----------+---------+
| org  | postcode | city      | country |
+------+----------+-----------+---------+
| 1105 | 000      | HONG KONG |      96 |
+------+----------+-----------+---------+
1 row in set (0.00 sec)

mysql> select org, postcode, city, country, count(*) from test.test6 group by 1,2,3,4 limit 1;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_67f_0'

mysql> select org, postcode, city, country, 'asdfbasdf' from test.test6 group by 1,2,3,4 limit 1;
+------+----------+-----------+---------+-----------+
| org  | postcode | city      | country | asdfbasdf |
+------+----------+-----------+---------+-----------+
| 1105 | 000      | HONG KONG |      96 | asdfbasdf |
+------+----------+-----------+---------+-----------+
1 row in set (3.12 sec)

mysql> select org, postcode, city, country, md5(city) from test.test6 group by 1,2,3,4 limit 1;
ERROR 1062 (23000): Duplicate entry '1290-0107-OSLO-160' for key 'group_key'
mysql> 
mysql> select * from test6 where org=1290 and postcode='0107' and city='OSLO' and country=160;
+------+----------+------+---------+
| org  | postcode | city | country |
+------+----------+------+---------+
| 1290 | 0107     | OSLO |     160 |
| 1290 | 0107     | OSLO |     160 |
| 1290 | 0107     | OSLO |     160 |
+------+----------+------+---------+
3 rows in set (0.20 sec)

mysql> -- when adding md5(city) to the GROUP BY it works
mysql> select org, postcode, city, country, md5(city) from test.test6 group by 1,2,3,4,5 limit 1;
+------+----------+-----------+---------+----------------------------------+
| org  | postcode | city      | country | md5(city)                        |
+------+----------+-----------+---------+----------------------------------+
| 1105 | 000      | HONG KONG |      96 | 57566997ecc042908c284a50714d0b67 |
+------+----------+-----------+---------+----------------------------------+
1 row in set (0.00 sec)

mysql> -- also works when selecting less rows
mysql> select org, postcode, city, country, md5(city) from test.test6 where org between 1200 and 1500 group by 1,2,3,4 limit 1;
+------+----------+---------+---------+----------------------------------+
| org  | postcode | city    | country | md5(city)                        |
+------+----------+---------+---------+----------------------------------+
| 1200 | 02451    | WALTHAM |     226 | 84ecb0a557402575a00a1f13d352d9ce |
+------+----------+---------+---------+----------------------------------+
1 row in set (2.97 sec)

mysql> -- also works when using a constant
mysql>select org, postcode, city, country, '84ecb0a557402575a00a1f13d352d9ce' from test.test6 group by 1,2,3,4 limit 1;
+------+----------+-----------+---------+----------------------------------+
| org  | postcode | city      | country | 84ecb0a557402575a00a1f13d352d9ce |
+------+----------+-----------+---------+----------------------------------+
| 1105 | 000      | HONG KONG |      96 | 84ecb0a557402575a00a1f13d352d9ce |
+------+----------+-----------+---------+----------------------------------+
1 row in set (3.11 sec)

mysql> -- also occurs on mysql 5.1 innodb
mysql> create table test8 select * from test6;
Query OK, 1032524 rows affected (0.71 sec)
Records: 1032524  Duplicates: 0  Warnings: 0

mysql> select org, postcode, city, country, count(*) from test.test6 group by 1,2,3,4 limit 1;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_67f_0'

mysql> alter table test8 engine=innodb;
Query OK, 1032524 rows affected (18.01 sec)
Records: 1032524  Duplicates: 0  Warnings: 0

mysql> select org, postcode, city, country, count(*) from test.test6 group by 1,2,3,4 limit 1;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_67f_0'

Suggested fix:
Both errors should not occur.

--
http://www.2bis10.de
[8 Apr 2011 15:31] Thomas Mayer
added 'group by' in tags
[8 Apr 2011 15:51] Valeriy Kravchuk
This is a duplicate of Bug #58081 probably.
[8 Apr 2011 16:35] Thomas Mayer
My last statement should use table test8 instead of test6:
select org, postcode, city, country, count(*) from test.test8 group by 1,2,3,4 limit 1;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_67f_0'

But it does not make a difference and the error occurs on InnoDb too.