CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: Advanced:
Duplicate Entrees in SQL??



Mar 22, 2001, 11:01 AM

Post #1 of 1 (2305 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]");


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives