Bug #59952 distinct ,distinct_key,group_key
Submitted: 4 Feb 2011 17:20 Modified: 11 Mar 2011 20:29
Reporter: Miran Cvenkel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.55-community, 5.6.3 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[4 Feb 2011 17:20] Miran Cvenkel
Description:
2 machines, win, linux

W: 5.1.31-community
L: 5.1.31-community-log

Without distinct all works OK

select distinct g.id_galery_obfuscated,t.term from galery_1 g join tezaver t on g.id_tezaver = t.id_tezaver

-->/* SQL Error (1062): Duplicate entry '-24672854d7d7407d82debca64ed61a35-Knylhavre' for key 'distinct_key' */

As I noticed that limit 30000 works, I guesses that increasing
tmp_table_size would help, and it did on win machine.

If I put additional keys on any of tables I have to increase tmp_table_size further to avoid distinct problems.

On linux machine I'm getting nowhere with this. Copyed entire tables to win from linux and viceversa, so they are 100% identical.

if I try workarounding:

select distinct * from
(
select g.id_galery_obfuscated,t.term from galery_1 g join tezaver t on g.id_tezaver = t.id_tezaver
) as a

-->/* SQL Error (1062): Duplicate entry '24672854d7d7407d82debca64ed61a35-Knylhavre' for key 'group_key' */

if I add "left" join into sql, same thing, on different record.

Tables:

CREATE TABLE  `test`.`galery_1` (
  `id_galery` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000' COMMENT '',
  `id_galery_obfuscated` varchar(36) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT 'zamegljen id_galery, da se ne da parse download',
  `id_member` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT '',
  `author` varchar(200) COLLATE utf8_slovenian_ci NOT NULL COMMENT '',
  `author_link` varchar(200) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `original_file_name` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `disk_file_name` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '''''',
  `title` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `DSP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `width_px` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `height_px` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `width_px_thumb` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `height_px_thumb` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_category` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '',
  `id_category_tree_path` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_tezaver` bigint(20) NOT NULL DEFAULT '-1' COMMENT '',
  `home_jsp` datetime DEFAULT NULL COMMENT '',
  `id_license` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_galery`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

CREATE TABLE  `test`.`tezaver` (
  `unique_row_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_tezaver` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_tezaver_obfuscated` varchar(36) COLLATE utf8_slovenian_ci NOT NULL,
  `l2` varchar(2) COLLATE utf8_slovenian_ci NOT NULL DEFAULT ' ',
  `term` varchar(100) COLLATE utf8_slovenian_ci NOT NULL DEFAULT ' ',
  `term_gender` enum('m','f','n','m_o','f_o','n_o','m_pl','f_pl','n_pl','m_o_pl','f_o_pl','n_o_pl') COLLATE utf8_slovenian_ci NOT NULL DEFAULT 'm' COMMENT 'm male, f femine, pl plural, o_m offspring male, o_f offspring femine, o_pl offspring plural',
  `DSP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `term_description` varchar(2000) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `term_link` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `term_group` varchar(400) COLLATE utf8_slovenian_ci DEFAULT ' ',
  `member_id` varchar(40) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT 'info samo za admina',
  `edit_locked` int(10) unsigned DEFAULT '0' COMMENT '',
  `user_ip` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_galery` int(10) unsigned zerofill DEFAULT NULL COMMENT '',
  `data_source` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  PRIMARY KEY (`term`,`term_gender`,`id_tezaver`,`l2`) USING BTREE,
  KEY `id_l2` (`id_tezaver`,`l2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC;

How to repeat:
I guess it appears at certain amount of data/keys in tables
[4 Feb 2011 17:48] Miguel Solorzano
Thank you for the bug report. Your server version is quite older (today version released is 5.1.54) could you please upgrade and comment the results. Thanks in advance.
[11 Feb 2011 1:32] Miran Cvenkel
1.ver 5.1.55-community , windows
2.test the above sql, all OK.
3.set tmp_table_size=18M, from previously 54M. resstart instance.
4.test the above sql -->
5.SQL Error (1062): Duplicate entry '-82e74d9c3b464aedbd9979c0f417489e-Fliegen-Arten Mauer' for key 'distinct_key'

So, the problem is confirmed on latest version.
[11 Feb 2011 7:59] Miran Cvenkel
Note: if I create new tables via create table as select ... and select only keyed columns, set indexes on that new tables same as on original, then sql on that new tables works.
[11 Feb 2011 8:10] Miran Cvenkel
ignore last post, it is not correct ...
[13 Feb 2011 10:27] Miran Cvenkel
Any detailed instructions how to submit file > 500kb to developers ?
I can't figure it out.
[14 Feb 2011 11:41] Valeriy Kravchuk
You can upload big files to ftp://ftp.mysql.com/pub/mysql/upload/. Use bug # (59952) in the file name and add a comment when done. Note that you will not be able to see your file at that FTP site.
[14 Feb 2011 16:59] Miran Cvenkel
if I do in dos:
ftp ftp://ftp.mysql.com/pub/mysql/upload/
I get:
Unknown host ftp://ftp.mysql.com/pub/mysql/upload/.

?
[14 Feb 2011 17:11] Miran Cvenkel
OK, managed to do it by some miracle via fireFTP.
File: bug_(59952).zip
Forgot to mention in readme.txt that forinstance limit 30000 "solves" thing
[11 Mar 2011 20:29] Sveta Smirnova
Thank you for the test data.

Verified as described.