Database Error? DBD:MySQL:DB DO failed: Duplicate entry '0' for key '1' at

jdlev has asked for the wisdom of the Perl Monks concerning the following question:

Not sure why I am getting this error, but I have isolated it to the 2nd prepare statement. If I remove it, the code runs fine. I'm still a newb, so please keep the technical jargon at an elementary level. The program is supposed to open one table on the database, pull all the customerIDs into an array, then the grep function pulls the email, and last the program opens a connection to a new table, and is supposed to upload the id and email. Here is the code with the problem, when it trys to insert the id & email:

#QUERY PREPARATION
my $query = sprintf("INSERT INTO email VALUES (%d, %s)", $servproID, $dbh->quote("$address"));
$dbh->do($query);

Here is the FULL CODE:

use Regexp::Common qw(Email::Address);
use Email::Address;
use DBI;

my $dsn = 'dbi:mysql:servpro:localhost:3306';

my $db = 'servpro';
my $host = 'host';
my $user = 'user';
my $password = 'pass';
my $address = ($addresses[0]);
# DATABASE HANDLE
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $user, $password, {RaiseError => 1});

#QUERY PREPARATION
my $sth = $dbh->prepare("select servproID from fran");
$sth->execute;

while(@row = $sth->fetchrow_array()) {
foreach $row(@row) {
my $filename = "C:/Test/email/$row.txt";

open my $rh, '<', $filename or die "$filename: $!";

my @addresses =
map { m/mailto:($RE{Email}{Address})/o; $1 }
grep { m/href=.+?mailto:/ }
<$rh>
;

close $rh;

{
local $, = local $\ = "\n";
chop(@addresses);
print "$row : @addresses";

#QUERY PREPARATION
my $query = sprintf("INSERT INTO email VALUES (%d, %s)", $servproID, $dbh->quote("$address"));
$dbh->do($query);

}
}
}

Comment on Database Error? DBD:MySQL:DB DO failed: Duplicate entry '0' for key '1' at

Replies are listed 'Best First'.
Re: Database Error? DBD:MySQL:DB DO failed: Duplicate entry '0' for key '1' at by Corion (Patriarch) on Dec 30, 2008 at 18:14 UTC
You're not setting `$servproID` anywhere in your code, which is why MySQL sees it as `undef`, which it chopses to interpret as zero. If you had been using strict, that error would have been caught. Printing your SQL is also very helpful in such situations.	[reply] [d/l] [select]
Re: Database Error? DBD:MySQL:DB DO failed: Duplicate entry '0' for key '1' at by jeffa (Bishop) on Dec 30, 2008 at 18:10 UTC
Sounds like you set a primary key without setting that key to auto_increment: `testdb> create table foo(id int unsigned not null, bar varchar(32), pr +imary key(id)); Query OK, 0 rows affected (0.01 sec) testdb> insert into foo(bar) values('foo'); Query OK, 1 row affected, 1 warning (0.00 sec) testdb> insert into foo(bar) values('bar'); ERROR 1062 (23000): Duplicate entry '0' for key 1` [download] You will either need to handle the key increment yourself -- or alter the table to make the id `auto_increment`'ed. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re: Database Error? DBD:MySQL:DB DO failed: Duplicate entry '0' for key '1' at by CountZero (Bishop) on Dec 30, 2008 at 22:13 UTC
I find this part of your program "strange": `my $sth = $dbh->prepare("select servproID from fran"); $sth->execute; while(@row = $sth->fetchrow_array()) { foreach $row(@row) { ... } }` [download] This part of your code does not "pull all the customerIDs into an array". Rather, the `fetchrow_array()`-method, "Fetches the next row of data and returns it as a list containing the field values" (as state the docs). So, `@row = $sth->fetchrow_array()` would be clearer if you renamed it to `@fields = $sth->fetchrow_array()` and then `foreach my $field (@fields) { ...}`. And even that is partly misleading since your SQL-statement only pulls 1 field from the database and therefore the whole `foreach`-loop is just expensive decoration. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]