MySQL Bugs: #16607: Duplicate Entry in a Table

Bug #16607	Duplicate Entry in a Table
Submitted:	18 Jan 2006 15:25	Modified:	12 Sep 2006 11:51
Reporter:	WILLIAM RIMBUN	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.18/1.1.19	OS:	Windows (Windows XP)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Source Editors

Description:
Hi,

I have one table with two columns. One of them is a primary key auto_increment.

In the script tab, when I try to run one or more insert statements (for eg four statements):

INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing ONE');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing TWO');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing THREE');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing FOUR');

I will get 6 records (instead of four). 

This is the result after I query from the table. 
1, 'Testing ONE'
2, 'Testing TWO'
3, 'Testing TWO'
4, 'Testing THREE'
5, 'Testing THREE'
6, 'Testing FOUR'

What on earth is happening? Any idea how to solve this? Is it because of the AUTO_INCREMENT problem?

I am surprised to encounter this error. I thought my INSERT statement is wrong. But it couldn't be. I have checked my statements. It is definitely ok.

How to repeat:
1. try to create the following table

CREATE TABLE TESTING
(
  TESTING_ID    INT(9) NOT NULL AUTO_INCREMENT,
  TESTING_NAME  VARCHAR(200) NOT NULL,
  PRIMARY KEY (TESTING_ID)
);

2. Open a script tab

3. Paste in the following INSERT statements

INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing ONE');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing TWO');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing THREE');
INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing FOUR');

4. Execute the script

5. SELECT * FROM TESTING

Thank you for the bug report. Below the server query log showing the
duplicates:

060118 16:46:06	      1 Query       CREATE TABLE TESTING
(
  TESTING_ID    INT(9) NOT NULL AUTO_INCREMENT,
  TESTING_NAME  VARCHAR(200) NOT NULL,
  PRIMARY KEY (TESTING_ID)
)
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing ONE')
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing TWO')
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing TWO')
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing THREE')
060118 16:46:07	      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing THREE')
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing FOUR')
		      1 Query       INSERT INTO TESTING (TESTING_NAME) VALUES ('Testing FOUR')
		      1 Query       show databases

What can I do to rectify this problem? I have only four records, but the database is showing me six records.

I have observed the same problem on a Powermac G5 with MySQL 5.0.18-standard

Same as bug #19142