Insert record not using next auto_increment value
Insert record not using next auto_increment value
Hello,
I'm using 7.2.78 of dbForge Studio Express for MySQL. When I add a new row with the "+" in the data grid view the auto_increment column value is being pre-populated with a much higher number than is required.
From the table edit page,
***
ENGINE = MYISAM
AUTO_INCREMENT = 4752
AVG_ROW_LENGTH = 134
***
Query for next auto_increment value:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'tester';
Returns: 4752
The ID value given when I add a row is 4995. This same behavior occurs with a number of different tables on the same database.
Another mysql client doesn't exhibit this behavior so I don't believe it is the server causing it. I enabled SQL log output for all queries and I don't see any indication that the client is even querying for the next value.
Any ideas on this behavior?
Thanks,
- D
I'm using 7.2.78 of dbForge Studio Express for MySQL. When I add a new row with the "+" in the data grid view the auto_increment column value is being pre-populated with a much higher number than is required.
From the table edit page,
***
ENGINE = MYISAM
AUTO_INCREMENT = 4752
AVG_ROW_LENGTH = 134
***
Query for next auto_increment value:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'tester';
Returns: 4752
The ID value given when I add a row is 4995. This same behavior occurs with a number of different tables on the same database.
Another mysql client doesn't exhibit this behavior so I don't believe it is the server causing it. I enabled SQL log output for all queries and I don't see any indication that the client is even querying for the next value.
Any ideas on this behavior?
Thanks,
- D
Re: Insert record not using next auto_increment value
We will investigate this issue and will answer you as soon as possible.
Re: Insert record not using next auto_increment value
Any update on this? Is there any debug or logging that can be enabled to help you track it down?
Re: Insert record not using next auto_increment value
Sorry for the delay on this.
Could you please provide us the server version and the result of the following queries:
Could you please provide us the server version and the result of the following queries:
Code: Select all
SHOW VARIABLES LIKE 'auto_increment%'
SHOW TABLE STATUS FROM `mydb` LIKE 'tester'
Re: Insert record not using next auto_increment value
Code: Select all
SHOW VARIABLES LIKE 'auto_increment%';
Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
Code: Select all
SHOW TABLE STATUS FROM `pandb` LIKE 'builds';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
builds MyISAM 10 Dynamic 445 130 57944 281474976710655 8192 0 4767 10/6/2017 10:17:04 AM 10/6/2017 10:17:04 AM (null) latin1_swedish_ci (null)
Re: Insert record not using next auto_increment value
I added two new rows by manually editing the ID value to be the next values, 4767 and 4768. Now the output from the show table command shows the "Auto_increment" value as 4769. If I then hit the "+" to add a new row it shows the pre-populated ID value 5013.
Re: Insert record not using next auto_increment value
Server version: 5.1.73
Re: Insert record not using next auto_increment value
Any update alexa?
Re: Insert record not using next auto_increment value
Could you please try creating the table below and let us know if the issue also happens with this table:
Code: Select all
CREATE TABLE test.table8 (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
)
Re: Insert record not using next auto_increment value
I've been able to reproduce this with the table you defined. It seems tied to older rows being deleted. The script creates the table, inserts some records and then deletes others.
At the end of this code the last "show table" command returns the values below.
If I go to the table and add a row with the "+" in the results pane, the ID for the added row is 18.
If you instead add an additional row with an insert statement, the ID is correctly stored as 14.
Code: Select all
DROP TABLE repos.table8;
CREATE TABLE repos.table8 (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO repos.table8 (name) VALUES ('test');
INSERT INTO repos.table8 (name) VALUES ('test1');
INSERT INTO repos.table8 (name) VALUES ('test1');
INSERT INTO repos.table8 (name) VALUES ('test1');
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
DELETE FROM repos.table8 WHERE name = 'test1';
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
INSERT INTO repos.table8 (name) VALUES ('test2');
DELETE FROM repos.table8 WHERE name = 'test1';
INSERT INTO repos.table8 (name) VALUES ('test3');
SHOW TABLE STATUS FROM `repos` like 'table8';
Code: Select all
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
table8 MyISAM 10 Dynamic 10 20 200 281474976710655 2048 0 14 10/26/2017 3:28:45 PM 10/26/2017 3:28:45 PM (null) latin1_swedish_ci (null)
If you instead add an additional row with an insert statement, the ID is correctly stored as 14.
Re: Insert record not using next auto_increment value
Thank you for the reply.
We will investigate this issue and will answer you as soon as any result is achieved on this.
We will investigate this issue and will answer you as soon as any result is achieved on this.
Re: Insert record not using next auto_increment value
Any update on the investigation?
Re: Insert record not using next auto_increment value
Sorry for the delay on this.
We will fix this issue in the next version 7.3 of dbForge Studio for MySQL and will notify you once it's available for downloading.
We will fix this issue in the next version 7.3 of dbForge Studio for MySQL and will notify you once it's available for downloading.
Re: Insert record not using next auto_increment value
This is fixed in 7.3. Thanks a bunch!