Danni
Deleted
Mar 22, 2001, 11:01 AM
Post #1 of 1
(684 views)
|
|
Duplicate Entrees in SQL??
|
Can't Post
|
|
I have a serious problem, probably due to my lack of experience with MySQL and the DBI interface.
I am designing an Ad Serving Daemon in perl using MySQL for databases. The Daemon forks to handle each request.
The adserver is currently sending 130+ Queries per second to MySQL and i am getting a lot of duplicate entrees in the databases when i should only be getting single entrees.
I was checking to see if the first UPDATE generated an error and if it did, because it would normaly result in the error 'Table 'database.A$info[3]' doesn't exist' but i found that occasionaly $sth->errstr will result in 'Lost connection to MySQL server during query' hence the if ($sth->errstr =~ /doesn\'t exist/)
Can anyone see the fault in the code below, or any ideas on how i can optimise/improve this routeen.
if ($dbh->do("UPDATE A$info[3] SET views = views + 1 WHERE day = $mday AND month = $month AND year = $year AND adid = $info[0]") <= 0) {
if ($sth->errstr =~ /doesn\'t exist/) {
$dbh->do("CREATE TABLE IF NOT EXISTS A$info[3] (day INTEGER, month INTEGER, year INTEGER, adid INTEGER, views INTEGER, uviews INTEGER, clicks INTEGER, spent DECIMAL(100,10))");
$dbh->do("INSERT INTO A$info[3] VALUES ($mday, $month, $year, $info[0],1,$unique,0,$info[4])");
} elsif (! $sth->errstr) {
$dbh->do("INSERT INTO A$info[3] VALUES ($mday, $month, $year, $info[0],1,$unique,0,$info[4])");
}
} else {
$dbh->do("UPDATE A$info[3] SET spent = spent + $info[4] WHERE day = $mday AND month = $month AND year = $year AND adid = $info[0]");
$dbh->do("UPDATE A$info[3] SET uviews = uviews + $unique WHERE day = $mday AND month = $month AND year = $year AND adid = $info[0]");
}}
|
MAIN
SEARCH
WHO'S 
LOG 