MySQL Bugs: #44170: Unexpected errors with certain selects

Bug #44170	Unexpected errors with certain selects
Submitted:	8 Apr 2009 22:55	Modified:	28 May 2009 9:44
Reporter:	Robin McMillon	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Solaris (OpenSolaris December 2008 build)
Assigned to:		CPU Architecture:	Any

Description:
I am getting unexpected errors when querying one column (su_lastname) but have no issues when querying a different  but similarly defined column (su_firstname) in the same table (submission).

Submission Table Definition
---------------------------
CREATE TABLE `submission` (
  `su_id` int(11) NOT NULL AUTO_INCREMENT,
  `su_dct_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `su_date` datetime NOT NULL,
  `su_ip_address` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_ip_country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_referring_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_olm_mid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_olm_cid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_cwp_pid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_country` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_firstname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_lastname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_company` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_job_role` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_department` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_industry` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_street` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_zip` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_city` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_fax` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_company_size` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `su_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `su_created_by` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'import-job',
  PRIMARY KEY (`su_id`),
  KEY `ix_su_dct_id` (`su_dct_id`,`su_date`),
  KEY `ix_su_created_at` (`su_created_at`)
) ENGINE=MyISAM AUTO_INCREMENT=1454848 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

Problem queries:
----------------
mysql> select distinct(su_lastname) from submission;
ERROR 1062 (23000): Duplicate entry 'XXX' for key 'group_key'

This query always succeeds on low cardinality data sets and fails intermittently on larger (>440000 entries) data sets if there are multiple occurrences of a surname.

mysql> select su_lastname, count(su_lastname) from submission group by su_lastname;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql_6ed7_0'

This query also succeeds on small data sets and larger homogeneous data sets but appears to fail consistently on the real data when run against a large data set.

These queries succeed every time when run against su_firstname (defined the same as su_lastname except for column name).  

How to repeat:
I can duplicate this regularly against our production data but have been unable to create a test data set it fails on.

Thank you for the bug report. Did you try a CHECK TABLE table_name in the offended database?. Thanks in advance.

Output from CHECK TABLE:

mysql> check table submission;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| dct.submission | check | status   | OK       | 
+----------------+-------+----------+----------+
1 row in set (6.16 sec)

The problems with the SELECT statements still exist.

Thank you for the feedback.

This looks like bug #40953 fixed in version 5.1.31. Please upgrade to current version 5.1.33, try with it and inform us if problem still exists. Would be good if you could provide dump problem repeatable with.

I have upgraded the server to 5.1.33, but the problems persist.

mysql> select su_lastname, count(su_lastname) from submission group by su_lastname;
ERROR 1022 (23000): Can't write; duplicate key in table '/tmp/#sql1cc1_10985_0'

mysql> select distinct(su_lastname) from submission;
ERROR 1062 (23000): Duplicate entry 'Μπούσιος' for key 'group_key'

Thank you for the feedback.

Yes, we can guarantee data would not be outsourced. Also we destroy it after bug is fixed.

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".