Bug #28000 MySQL hangs when using INSERT IGNORE ... ON DUPLICATE KEY
Submitted: 21 Apr 2007 14:59 Modified: 17 May 2007 14:24
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.37,5.1, 6.0-falcon-bk OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: qc

[21 Apr 2007 14:59] jocelyn fournier 
Description:
Hi,

When using an INSERT IGNORE ... ON DUPLICATE KEY, where the ON DUPLICATE KEY UPDATE will fail, MySQL will hang.

Regards,
  Jocelyn

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a mediumint(8) unsigned NOT NULL auto_increment,b date NOT NULL default '0000-00-00',PRIMARY KEY  (a)) ENGINE=MyISAM;
INSERT INTO t1 (a,b) SELECT 1,NULL ON DUPLICATE KEY UPDATE b=NULL;
INSERT INTO t1 (a,b) SELECT 1,NULL ON DUPLICATE KEY UPDATE b=NULL;
ERROR 1048 (23000): Column 'b' cannot be null

=> ok

INSERT IGNORE INTO t1 (a,b) SELECT 1,NULL ON DUPLICATE KEY UPDATE b=NULL;

=> hang
[21 Apr 2007 15:56] MySQL Verification Team 
Thank you for the bug report. Verified as described.
[10 May 2007 20:13] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26469

ChangeSet@1.2482, 2007-05-11 01:17:54+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28000.
  Bug occurs in INSERT IGNORE ... SELECT ... ON DUPLICATE KEY UPDATE
  sentences, when SELECT returns duplicated values and UPDATE clause
  tries to assign NULL values to NOT NULL fields.
  NOTE: By current design MySQL server treats INSERT IGNORE ... ON
  DUPLICATE sentences as INSERT ... ON DUPLICATE (without IGNORE
  clause), but MySQL manual lacks of this information. After
  discussion with Serg we are agreed to legalize this behaviour.
  
  write_record() function was returning error values even within
  INSERT IGNORE, because ignore_errors parameter of
  fill_record_n_invoke_before_triggers() function call was
  always set to FALSE. FALSE is replaced by info->ignore.
[10 May 2007 21:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26473

ChangeSet@1.2481, 2007-05-11 02:25:46+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28000.
  Bug occurs in INSERT IGNORE ... SELECT ... ON DUPLICATE KEY UPDATE
  statements, when SELECT returns duplicated values and UPDATE clause
  tries to assign NULL values to NOT NULL fields.
  NOTE: By current design MySQL server treats INSERT IGNORE ... ON
  DUPLICATE statements as INSERT ... ON DUPLICATE with update of
  duplicated records, but MySQL manual lacks this information.
  After this fix such behaviour becomes legalized.
  
  The write_record() function was returning error values even within
  INSERT IGNORE, because ignore_errors parameter of
  the fill_record_n_invoke_before_triggers() function call was
  always set to FALSE. FALSE is replaced by info->ignore.
[10 May 2007 22:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26475

ChangeSet@1.2482, 2007-05-11 03:17:05+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28000.
  Bug occurs in INSERT IGNORE ... SELECT ... ON DUPLICATE KEY UPDATE
  statements, when SELECT returns duplicated values and UPDATE clause
  tries to assign NULL values to NOT NULL fields.
  NOTE: By current design MySQL server treats INSERT IGNORE ... ON
  DUPLICATE statements as INSERT ... ON DUPLICATE with update of
  duplicated records, but MySQL manual lacks this information.
  After this fix such behaviour becomes legalized.
  
  The write_record() function was returning error values even within
  INSERT IGNORE, because ignore_errors parameter of
  the fill_record_n_invoke_before_triggers() function call was
  always set to FALSE. FALSE is replaced by info->ignore.
[13 May 2007 6:16] Bugs System 
Pushed into 5.1.19-beta
[13 May 2007 6:19] Bugs System 
Pushed into 5.0.42
[17 May 2007 14:24] Paul DuBois 
Noted in 5.0.42, 5.1.19 changelogs.

The server could hang for INSERT IGNORE ... ON DUPLICATE KEY UPDATE
if an update failed.