MySQL Bugs: #26126: "Unknown error" during a long running query.

Bug #26126	"Unknown error" during a long running query.
Submitted:	6 Feb 2007 21:20	Modified:	26 Jul 2008 13:54
Reporter:	Mark Protas	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	4.1.22	OS:	Linux (Linux, 2.6.9 kernel)
Assigned to:		CPU Architecture:	Any
Tags:	unknown error

Description:
During a long running query that inserts into a temporary table I am intermittently encountering an "Unknown error" from MySQL.  I have ruled out a JDBC bug, as I was able to reproduce the "Unknown error" message when running the same query via a Perl script.  The query that causes the "Unknown error" message is:

INSERT IGNORE INTO MailingRecipsDedupe (MailingID, RecipID) SELECT  2431, Recips.RecipID FROM Recips LEFT JOIN SuppressionTemp ON (md5(lower(Recips.EmailAddr))=SuppressionTemp.EmailCrypt) join ListRecips on Recips.RecipID=ListRecips.RecipID  WHERE Recips.SiteID=2 AND ((ListRecips.ListID = 1)) AND ListRecips.Status = 'NORMAL' AND Recips.Domain NOT IN ('aim.com','aol.com','tstc.edu','hotmail.com','compuserve.com','wmconnect.com','netscape.com','msn.com','cs.com','netscape.net') AND Recips.Status='NORMAL' AND SuppressionTemp.EmailCrypt IS NULL  ORDER BY Recips.SourceSignupDate DESC  LIMIT 87300

All tables in question are InnoDB.  This issue is intermittent but it happens on approximately 50-60% of the 25 systems that are running this query.  The size of these tables varies across the various systems, but the Recips table is in the general range of 10 million to 20 million rows, ListRecips 4 million to 20 million rows, and SuppressionTemp is typically 500,000 to 2 million rows.  The schema for the tables in question is:

CREATE TEMPORARY TABLE IF NOT EXISTS MailingRecipsDedupe (\
  MailingID int(10) unsigned NOT NULL,\
  RecipID int(10) unsigned NOT NULL,\
  PRIMARY KEY  (MailingID,RecipID)\
) TYPE=InnoDB

CREATE TEMPORARY TABLE IF NOT EXISTS SuppressionTemp (EmailCrypt char(32) NOT NULL,KEY(EmailCrypt))

CREATE TABLE ListRecips (
  ListID int(10) unsigned NOT NULL,
  RecipID int(10) unsigned NOT NULL,
  Status enum('NORMAL','UNSUB','INVITE','INVITE_SENT','VACATION','SUMMARY','DIGEST','IM','NOMAIL') NOT NULL default 'NORMAL',
  DateJoined int(11) NOT NULL,
  DateUnsub int(11) NULL,
  Confirmed tinyint(1) NOT NULL default 0,
  SourceID varchar(255) NULL,
  PRIMARY KEY  (ListID,RecipID),
  KEY recip_lists (RecipID,ListID),
  KEY lst_sts (ListID,Status)
) TYPE=InnoDB;

CREATE TABLE Recips (
  RecipID int(10) unsigned NOT NULL auto_increment,
  EmailAddr varchar(255) NOT NULL,
  Domain varchar(255) NOT NULL,
  ReverseEmailAddr varchar(255) NOT NULL,
  SiteID int(11) NOT NULL,
  ImportID int(10) unsigned NULL,
  Status enum('NORMAL','UNSUB','HELD','INVALID','OPTOUT','QUARANTINE','QUARANTINE_SENT','QUARANTINE_FAILED','CONFIRM','MARKED','BLOCKED','LIST_SEED','SITE_SEED','SERVER_SEED','TEMP_SEED','INVALID','HARD_BOUNCE','SOFT_BOUNCE','DOMAIN_COMPLAINT','INVITE','INVITE_SENT','DOPTOUT','MARKED1','MARKED2','MARKED3','MARKED4','MARKED5','COMPLAINT','CONFIRM_SENT') NOT NULL default 'NORMAL',
  NumBounces int(11) NULL,
  FirstName varchar(255) NULL,
  LastName varchar(255) NULL,
  Password varchar(255) NULL,
  SecretID int(11) NULL,
  ConfirmDate datetime NULL,
  DateBounce datetime NULL,
  DateHeld datetime NULL,
  DateUnsub datetime NULL,
  DateOptout datetime NULL,
  DateJoined datetime NOT NULL,
  SignupMethod varchar(255) NULL,
  SignupIP varchar(20) NULL,
  SourceSignupDate varchar(100) NULL,
  Comment varchar(255) NULL,
  SourceDesc varchar(255) NULL,
  LastOpenedIP varchar(20) NULL,
  LastOpenedHTML datetime NULL,
  LastClicked datetime NULL,
  ExternalID varchar(50) NULL,
  PRIMARY KEY  (RecipID),
  UNIQUE KEY EmailAddr (EmailAddr, SiteID),
  KEY imports (ImportID, Status),
  KEY ReverseDomain (ReverseEmailAddr),
  KEY bnc (DateBounce),
  KEY hld (DateHeld),
  KEY unsb (DateUnsub),
  KEY opt (DateOptout),
  KEY status (Status, SiteID),
  KEY dmn_status (Domain, Status)
) TYPE=InnoDB;

How to repeat:
This is an intermittent issue, I have not been able to determine a method to reliably reproduce the "Unknown error" message.

Suggested fix:
Provide a more specific error message when this condition is encountered.

Thank you for a problem report. Please, send your my.cnf file content. Do you have anything in the error logs for the periods when you get this error?

My.cnf

Attachment: my.cnf (application/octet-stream, text), 2.54 KiB.

I have attached my my.cnf to the bug.  There is nothing in the mysql error log when the "Unknown error" is encountered by my application.

Please, send the results of:

getconf GNU_LIBC_VERSION
getconf GNU_LIBPTHREAD_VERSION

from your system.

I also noted thread_cache=8 in your my.cnf. Can you try to set thread_cache=0 and check if this error will be still repeatable.

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

i've seen this error message before, and managed to get a debug trace from server.  it was caused by the select part of the 'insert .. select' getting rolled back due to a deadlock or transaction timeout.

hope that's good hint to try repeat it.

Have you tried the suggestion done by Valeriy:

"I also noted thread_cache=8 in your my.cnf. Can you try to set thread_cache=0 and check if this error will be still repeatable.".

Thanks in advance.

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

I think this bug still has some merit to be looked at.. although it is 5.1.

Is it possible to repeat this on 5.0?

See new bug abotu this issue: http://bugs.mysql.com/bug.php?id=37183