| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.11-beta and 5.0.26/5.0BK/5.1BK | OS: | Windows (WINDOWS) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | auto_increment, insert, NO_AUTO_VALUE_ON_ZERO, SQL_MODE | ||
[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]
MySQL Verification Team
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.
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;