Duplicate records inserted into mysql table

[perlmaster56 (barry kimelman)]

I have successfully used GO to query my mysql tables. Now however I am having an issue with inserting records into my mysql tables. I wrote a small GO test program, listed the contents of my database table, ran my compiled test program and then once again I listed the contents of my mysql table.

To my surprise the record was inserted twice !!
Here is the source code for my test program

 1	package main
 2	
 3	import (
 4		_ "github.com/go-sql-driver/mysql"
 5		"database/sql"
 6		"log"
 7		"fmt"
 8		"os"
 9	)
10	
11	func main(){
12		mysql_connect_string := "myusername:mypassword@(127.0.0.1:3306)/mydatabase?parseTime=true"
13	
14		db, err := sql.Open("mysql", mysql_connect_string)
15		if err != nil {
16			log.Fatal(err)
17		}
18	
19		tx,_:= db.Begin()
20		stmt, err := tx.Prepare("INSERT INTO authors(name) VALUES(?)")
21		res,err := stmt.Exec("Larry Wall")
22		if err != nil {
23			tx.Rollback()
24			log.Fatalf("\n1st insert failed\n%q\n",err)
25		}
26	
27		res,err = stmt.Exec("Randall Schwarz")
28		if err != nil {
29			tx.Rollback()
30			log.Fatalf("\n2nd insert failed\n%q\n",err)
31		}
32		tx.Commit()
33		fmt.Printf("\nresult :\n%q\n",res)
34		os.Exit(0)
35		
36	} // main

There were 2 identical records from each of the insert operations. Why am I getting these duplicate inserts ?

[Yamil_Bracho]

You would need to check which field is primary key for authors table (or maybe this table even does not has PK so you could create name as primary key)

[perlmaster56 (barry kimelman)]

The primary key for this table is an INTEGER AUTO_INCREMENT field

mysql> desc authors;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(25) | YES | | NULL | |
±------±------------±-----±----±--------±---------------+
2 rows in set (0.08 sec)

mysql>

[Yamil_Bracho]

In that case, you can insert many authors with the same name, so you first have to serach if the author exists and then if he/she does not exists, you can add him/her.In code it could be something like

sqlString := "SELECT name FROM authors WHERE name= ?"
err := db.QueryRow(sqlString, name).Scan(&name)
if err != nil && err == sql.ErrNoRows {
   // Not found, just insert	
} else {
	// author found
}

[perlmaster56 (barry kimelman)]

Your solution makes sense except for one thing: The author “larry wall” did not exist before I did the insert. Why would the INSERT cause 2 records to be created ?

[Yamil_Bracho]

It could be because the field your are using does not give a clue for the RDBMS to know if it is duplicated.
Do this: Clean the table, and execute your application step by step or debug it…

[perlmaster56 (barry kimelman)]

I deleted all the records from the table and I am no longer getting duplicated records no matter how I run my tests. Now I may need to restructure my much larger program and database table which first gave me signs of this issue.

[thinkeridea (Qi Yin)]

I learned from your description that you only found several pieces of data when you ran the test program for the second time. I think this is the data that you didn’t clear the historical test before you ran the test. The illusion to you is that there are more data after you run the test. In fact, it is the result of running the test several times