MySQL Bugs: #27604: Update query with Join crashes mysqld

Bug #27604	Update query with Join crashes mysqld
Submitted:	3 Apr 2007 1:29	Modified:	11 Jun 2007 6:20
Reporter:	Kristian Ducharme	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	14.12 Distrib 5.0.37, for pc-linux-gnu (	OS:	Linux (Redhat 9 (Shrike))
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crash, join, UPDATE

Description:
Update query crashes on a daily basis. Two tables involved.

Table definitions:
-----------------
CREATE TABLE `emails_search4more_bl` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `list_id` int(11) unsigned NOT NULL default '0',
  `email` varchar(100) NOT NULL default '',
  `first_name` varchar(40) default NULL,
  `last_name` varchar(40) default NULL,
  `custom_field_1` varchar(100) default NULL,
  `custom_field_2` varchar(100) default NULL,
  `custom_field_3` varchar(100) default NULL,
  `custom_field_4` varchar(100) default NULL,
  `custom_field_5` varchar(100) default NULL,
  `date_created` datetime default NULL,
  `source` varchar(20) NOT NULL default '',
  `ip_address` varchar(20) default NULL,
  `remove_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(20) NOT NULL default 'Active',
  `init_autoresponder_sent` tinyint(1) NOT NULL default '0',
  `is_bl_pattern` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ListEmail` (`email`,`list_id`,`status`,`remove_date`),
  KEY `ListFirstLast` (`list_id`,`first_name`,`last_name`),
  KEY `ListEmailStatus` (`list_id`,`status`),
  KEY `RemoveStatus` (`remove_date`,`status`),
  KEY `ListLastname` (`list_id`,`last_name`),
  KEY `ListFirstname` (`list_id`,`first_name`),
  KEY `SourceID` (`source`,`list_id`),
  KEY `ListIds` (`list_id`,`id`),
  KEY `EmailListStatus` (`list_id`,`email`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

 CREATE TABLE `emails_search4more_queue` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `list_id` int(11) unsigned NOT NULL default '0',
  `email` varchar(100) NOT NULL default '',
  `first_name` varchar(40) default NULL,
  `last_name` varchar(40) default NULL,
  `custom_field_1` varchar(100) default NULL,
  `custom_field_2` varchar(100) default NULL,
  `custom_field_3` varchar(100) default NULL,
  `custom_field_4` varchar(100) default NULL,
  `custom_field_5` varchar(100) default NULL,
  `date_created` datetime default NULL,
  `source` varchar(20) NOT NULL default '',
  `ip_address` varchar(20) default NULL,
  `remove_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(20) NOT NULL default 'Active',
  `init_autoresponder_sent` tinyint(1) NOT NULL default '0',
  `is_bl_pattern` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ListEmail` (`email`,`list_id`,`status`,`remove_date`),
  KEY `ListFirstLast` (`list_id`,`first_name`,`last_name`),
  KEY `ListEmailStatus` (`list_id`,`status`),
  KEY `RemoveStatus` (`remove_date`,`status`),
  KEY `ListLastname` (`list_id`,`last_name`),
  KEY `ListFirstname` (`list_id`,`first_name`),
  KEY `SourceID` (`source`,`list_id`),
  KEY `ListIds` (`list_id`,`id`),
  KEY `EmailListStatus` (`list_id`,`email`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How to repeat:
UPDATE emails_search4more_queue AS a JOIN emails_search4more_bl AS b ON (a.email = b.email) SET a.remove_date=now(),a.status='Removed' WHERE a.status = 'Active' AND b.is_bl_pattern=0 AND b.status='Active' AND b.email != '' AND a.id <=715647

a.id does not seem to matter.

mysqld stack trace

Attachment: mysql_stack.rtf (application/rtf, text), 2.68 KiB.

Thank you for the report.

Please provide output of SHOW TABLE STATUS LIKE 'emails_search4more_bl' and SHOW TABLE STATUS LIKE 'emails_search4more_queue'

show table status like 'emails_search4more_bl' \G;
*************************** 1. row ***************************
           Name: emails_search4more_bl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 124010
 Avg_row_length: 105
    Data_length: 13123584
Max_data_length: 0
   Index_length: 48988160
      Data_free: 0
 Auto_increment: 6261033
    Create_time: 2007-03-30 18:04:16
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 5120 kB

show table status like 'emails_search4more_queue' \G;
*************************** 1. row ***************************
           Name: emails_search4more_queue
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9774
 Avg_row_length: 162
    Data_length: 1589248
Max_data_length: 0
   Index_length: 5079040
      Data_free: 0
 Auto_increment: 727162
    Create_time: 2007-03-30 18:04:25
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 4096 kB

***Note that emails_search4more_queue varies in length daily, email_search4more_bl does not change as often.

Thank you for the report.

I can not repeat described behaviour.

Please upgrade to current 5.0.41 version and check if problem has solved.

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