Page 1 of 1
Insert record not using next auto_increment value
Posted: Wed 13 Sep 2017 17:55
by diverge
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
Re: Insert record not using next auto_increment value
Posted: Thu 14 Sep 2017 14:35
by alexa
We will investigate this issue and will answer you as soon as possible.
Re: Insert record not using next auto_increment value
Posted: Thu 05 Oct 2017 00:38
by diverge
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
Posted: Thu 05 Oct 2017 08:41
by alexa
Sorry for the delay on this.
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
Posted: Fri 06 Oct 2017 17:26
by diverge
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)
When I add a record to the table the ID value pre-populated is 5011. I tried another machine with the same dbforge version, same ID is used when adding a record.
Re: Insert record not using next auto_increment value
Posted: Fri 06 Oct 2017 19:20
by diverge
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
Posted: Wed 11 Oct 2017 20:16
by diverge
Server version: 5.1.73
Re: Insert record not using next auto_increment value
Posted: Wed 25 Oct 2017 06:57
by diverge
Any update alexa?
Re: Insert record not using next auto_increment value
Posted: Wed 25 Oct 2017 12:45
by alexa
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
Posted: Thu 26 Oct 2017 22:33
by diverge
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.
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';
At the end of this code the last "show table" command returns the values below.
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 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.
Re: Insert record not using next auto_increment value
Posted: Mon 30 Oct 2017 15:11
by alexa
Thank you for the reply.
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
Posted: Thu 09 Nov 2017 00:49
by diverge
Any update on the investigation?
Re: Insert record not using next auto_increment value
Posted: Thu 09 Nov 2017 11:28
by alexa
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.
Re: Insert record not using next auto_increment value
Posted: Mon 11 Dec 2017 18:43
by diverge
This is fixed in 7.3. Thanks a bunch!