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