| Bug #35376 | Value of insert_id automatically resets to 0 after inserting 1st row | ||
|---|---|---|---|
| Submitted: | 18 Mar 2008 7:31 | Modified: | 8 May 2008 16:34 |
| Reporter: | Salman Rawala | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INSERT_ID | ||
[18 Mar 2008 10:22]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.1.23. In case of the same problem, please, send the entire test case, starting with CREATE TABLE.
[31 Mar 2008 10:35]
Salman Rawala
Test Case of insert_id where you can see the behavior
Attachment: insert_id_func.test (application/octet-stream, text), 4.04 KiB.
[18 Apr 2008 23:00]
Bugs System
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".
[8 May 2008 9:38]
Susanne Ebrecht
Verified as described:
version(): 5.1.25-rc-debug
create table t(id serial, num integer, primary key(id));
set insert_id=100;
show variables like 'insert_id'\G
*************************** 1. row ***************************
Variable_name: insert_id
Value: 100
1 row in set (0.00 sec)
insert into t(num) values(1),(2),(3);
select * from t;
+-----+------+
| id | num |
+-----+------+
| 100 | 1 |
| 101 | 2 |
| 102 | 3 |
+-----+------+
show variables like 'insert_id'\G
*************************** 1. row ***************************
Variable_name: insert_id
Value: 0
The variable insert_id was reset to 0.
When you don't recognise this and you delete all values in the table for a new start, the insert starts with 1 instead of 100.
I tested with MyISAM and InnoDB.
[8 May 2008 16:34]
Timothy Smith
Thanks for the report, but this is not a bug. The INSERT_ID value is intended mainly for binary log, and works as designed and described in documentation: ======================================== http://dev.mysql.com/doc/refman/5.0/en/set-option.html INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. ========================================
Description: When we set set value of insert_id and insert some rows, this variable resets to 0 after inserting first row only How to repeat: SET @@session.insert_id = 50; SELECT @@session.insert_id; @@session.insert_id 50 INSERT into t1(name) values('Record_4'); INSERT into t1(name) values('Record_5'); INSERT into t1(name) values('Record_6'); SELECT * from t1; id name 100 Record_1 101 Record_2 102 Record_3 50 Record_4 103 Record_5 104 Record_6 SELECT @@session.insert_id; @@session.insert_id 0 'Bug: Only record after setting insert_id value added with that id into table, after that it resets to 0';