Insert record not using next auto_increment value

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Insert record not using next auto_increment value

Post by diverge » Wed 13 Sep 2017 17:55

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

alexa

Re: Insert record not using next auto_increment value

Post by alexa » Thu 14 Sep 2017 14:35

We will investigate this issue and will answer you as soon as possible.

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Thu 05 Oct 2017 00:38

Any update on this? Is there any debug or logging that can be enabled to help you track it down?

alexa

Re: Insert record not using next auto_increment value

Post by alexa » Thu 05 Oct 2017 08:41

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'

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Fri 06 Oct 2017 17:26

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.

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Fri 06 Oct 2017 19:20

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.

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Wed 11 Oct 2017 20:16

Server version: 5.1.73

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Wed 25 Oct 2017 06:57

Any update alexa?

alexa

Re: Insert record not using next auto_increment value

Post by alexa » Wed 25 Oct 2017 12:45

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)
)

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Thu 26 Oct 2017 22:33

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.

alexa

Re: Insert record not using next auto_increment value

Post by alexa » Mon 30 Oct 2017 15:11

Thank you for the reply.

We will investigate this issue and will answer you as soon as any result is achieved on this.

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Thu 09 Nov 2017 00:49

Any update on the investigation?

alexa

Re: Insert record not using next auto_increment value

Post by alexa » Thu 09 Nov 2017 11:28

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.

diverge
Posts: 9
Joined: Wed 13 Sep 2017 17:45

Re: Insert record not using next auto_increment value

Post by diverge » Mon 11 Dec 2017 18:43

This is fixed in 7.3. Thanks a bunch!

Post Reply