Bug #23233 NO_AUTO_VALUE_ON_ZERO and INSERT...ON DUPLICATE cause insertion failure
Submitted: 12 Oct 2006 22:28 Modified: 4 Apr 2007 4:03
Reporter: Peter Andrews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.11-beta and 5.0.26/5.0BK/5.1BK OS:Microsoft Windows (WINDOWS)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: auto_increment, insert, NO_AUTO_VALUE_ON_ZERO, SQL_MODE

[12 Oct 2006 22:28] Peter Andrews
Description:
This sounds complicated but I have created a test case (below)which should  replicate error easily so don't despair!

When SQL_MODE is set to NO_AUTO_VALUE_ON_ZERO on table creation and you have an auto_increment field AND you use the INSERT... ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) pattern then after an attempt to add a duplicate, the next insertion will insert using id=0. If you then attempt to duplicate insert that record, all following insertions attempts will silently fail.

Note: the first malfunction was the insertion of a record with the auto_increment field value set to zero.

How to repeat:
The script below replicates this bug. I have annotated the results to help so I suggest just running and looking at the outputs.

SET SQL_MODE='';

drop database if exists `auto_increment_test`;
create database if not exists `auto_increment_test`;

USE `auto_increment_test`;

#NOTE: BUG WILL NOT HAPPEN IF SQL_MODE IS NOT SET TO 'NO_AUTO_VALUE_ON_ZERO'
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `table_1` */

DROP TABLE IF EXISTS `table_1`;

CREATE TABLE `table_1` (
  `id` int(11) NOT NULL auto_increment,
  `field_1` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `field_1` (`field_1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

/* Function  structure for function  `set_test_source_broken` */
drop function  if exists `insert_broken`;
DELIMITER $$

CREATE FUNCTION `insert_broken`(
	p_field_1 varchar(64)
) RETURNS int(11)
	COMMENT 'There appears to be a bug in using INSERT_LAST_ID(id) for the duplicate key case so do less
efficient select to find record id'
BEGIN
	INSERT IGNORE INTO table_1 (field_1)
		VALUES (p_field_1)
	ON DUPLICATE KEY
		UPDATE id=LAST_INSERT_ID(id);
	RETURN LAST_INSERT_ID();
END$$
DELIMITER ;

SET SQL_MODE='';

SELECT insert_broken('broken1?') as 'broken1 THIS CORRECTLY GETS CREATED WITH ID=1';
SELECT * FROM table_1;
SELECT insert_broken('broken1?') as 'broken1 THIS CORRECTLY RETURNS ID=1';
SELECT * FROM table_1;
SELECT insert_broken('broken2?') as 'broken2 THIS INCORRECTLY GETS CREATED WITH ID=0'; 
SELECT * FROM table_1;
SELECT insert_broken('broken2?') as 'broken2 THIS CORRECTLY RETURNS ID=0';
SELECT * FROM table_1;
SELECT insert_broken('broken3?') as 'broken3 THIS FAILS SILENTLY TO INSERT A ROW AND REPORTS ID=0';
SELECT * FROM table_1;
[13 Oct 2006 13:12] Peter Andrews
Just wanted to make this absolutely clear: this bug only occurs when the SQL_MODE is set to NO_AUTO_VALUE_ON_ZERO at time of function/procedure definition. It does not matter whether it is set during table creation time.
[13 Oct 2006 13:44] Peter Andrews
replicated the error exactly on 5.0.26
[19 Oct 2006 13:36] Peter Andrews
Why has no one looked at this bug?
[19 Oct 2006 14:23] Peter Andrews
I have a suspicion that the NO_AUTO_VALUE_ON_ZERO is not a cause of the bug but instead causes the bug to be unmasked. In other words, the bug has to do with incorrectly inserting a row with id 0. When NO_AUTO_VALUE_ON_ZERO is not set, then the bug would appear not to happen because the insert of id 0 will automatically be changed to insert at the next id.
[19 Oct 2006 14:44] Miguel Solorzano
Thank you for the bug report. Verfied as described.
[19 Mar 2007 15:26] 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/22278

ChangeSet@1.2477, 2007-03-19 18:24:50+03:00, evgen@moonbone.local +3 -0
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  
  In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
  variable is used to indicate that the LAST_INSERT_ID value is set. But the
  open_table() function doesn't initialize it. When an INSERT .. ON DUPLICATE
  query that updates the auto_increment field is issued it sets the
  table->auto_increment_field_not_null variable to TRUE. This makes each
  subsequent query treat the LAST_INSERT_ID value as already set while it isn't
  and using 0 as the LAST_INSERT_ID value.
  
  Now the open_table() function initializes the 
  table->auto_increment_field_not_null variable at the table opening.
[21 Mar 2007 17:25] Guilhem Bichot
comments sent by mail
[21 Mar 2007 19:23] 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/22529

ChangeSet@1.2477, 2007-03-21 22:22:12+03:00, evgen@moonbone.local +3 -0
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  
  In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
  variable is used to indicate that a non-NULL value was specified by the user
  for an auto_increment column. When an INSERT .. ON DUPLICATE updates the
  auto_increment field this variable is set to true and stays unchanged for the
  next insert operation. This makes the next inserted row to wrongly have 0 
  as the value of the auto_increment field.
  
  Now the write_record() function resets the table->auto_increment_field_not_null
  variable after inserting or updating a row.
[26 Mar 2007 20:27] 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/22964

ChangeSet@1.2477, 2007-03-27 00:26:08+04:00, evgen@moonbone.local +5 -0
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  
  In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
  variable is used to indicate that a non-NULL value was specified by the user
  for an auto_increment column. When an INSERT .. ON DUPLICATE updates the
  auto_increment field this variable is set to true and stays unchanged for the
  next insert operation. This makes the next inserted row sometimes wrongly have
  0 as the value of the auto_increment field.
  
  Now the fill_record() function resets the table->auto_increment_field_not_null
  variable before filling the record.
[28 Mar 2007 14:53] 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/23167

ChangeSet@1.2477, 2007-03-28 18:51:21+04:00, evgen@moonbone.local +9 -0
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  
  In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
  variable is used to indicate that a non-NULL value was specified by the user
  for an auto_increment column. When an INSERT .. ON DUPLICATE updates the
  auto_increment field this variable is set to true and stays unchanged for the
  next insert operation. This makes the next inserted row sometimes wrongly have
  0 as the value of the auto_increment field.
  
  Now the fill_record() function resets the table->auto_increment_field_not_null
  variable before filling the record.
  The table->auto_increment_field_not_null variable is also reset by the
  open_table() function for a case if we missed some auto_increment_field_not_null
  handling bug.
[30 Mar 2007 13:30] 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/23413

ChangeSet@1.2424, 2007-03-30 17:28:15+04:00, evgen@sunlight.local +9 -0
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  
  In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
  variable is used to indicate that a non-NULL value was specified by the user
  for an auto_increment column. When an INSERT .. ON DUPLICATE updates the
  auto_increment field this variable is set to true and stays unchanged for the
  next insert operation. This makes the next inserted row sometimes wrongly have
  0 as the value of the auto_increment field.
  
  Now the fill_record() function resets the table->auto_increment_field_not_null
  variable before filling the record.
  The table->auto_increment_field_not_null variable is also reset by the
  open_table() function for a case if we missed some auto_increment_field_not_null
  handling bug.
  Now the table->auto_increment_field_not_null is reset at the end of the
  mysql_load() function.
  
  Reset the table->auto_increment_field_not_null variable after each
  write_row() call in the copy_data_between_tables() function.
[30 Mar 2007 17:25] Guilhem Bichot
yes, the patch is approved, provided Evgeny made modifications agreed upon via IRC.
Mar 30 16:10:34 <guilhem>       your latest cset + your fix (reset after write_record() in read_sep_field/read_fixed_length) + a testcase + a new bug report = approved.
where "new bug report" and "your fix" and "a testcase" refer to a new bug found in LOAD DATA INFILE together with Evgeny.
[31 Mar 2007 8:39] Bugs System
Pushed into 5.1.18-beta
[31 Mar 2007 8:44] Bugs System
Pushed into 5.0.40
[4 Apr 2007 4:03] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

With the NO_AUTO_VALUE_ON_ZERO SQL mode enabled, LAST_INSERT_ID()
could return 0 after INSERT ... ON DUPLICATE KEY UPDATE.