I've got a part of a complex system that isn't behaving as expected. I've
got one perl program running with autocommit off that creates entries in a
table and commits the changes. At some point, the rows have their state
field changed from 'active' to 'closed', and these changes are then
committed.
The next step in the process is the part I'm having problems with. The
next step is supposed to pull up a list of the records that have a state
of 'closed' and process them. It works fine the first time through, or if
I limit the number of results in the select that gathers the list that I
use to process them it will loop through as many times as necessary to
process them all, but once it has processed everything that was available
when the program was first run, it stops and won't see any records that
were updated after the first run.
The interesting part is, if I run the program with autocommit on, it
works fine. Unfortunately, transactions are essential. I found that the
program works if I turn autocommit on but wrap the critical table updates
in $dbh->do("BEGIN;") and $dbh->do("COMMIT;"), but I haven't tested it to
make sure there are no side-effects, and I'd rather use the mechanism
it seems that I'm supposed to use with DBI.
Has anyone seen this problem? Is do'ing the BEGIN and COMMIT valid?
Anyway, here's the table and the second-stage program (trimmed down as far
as possible). I'm using MySQL 3.23.41, DBI-1.19, and
Msql-Mysql-modules-1.2216.
CREATE TABLE LCCer
(
switchId CHAR(10) BINARY NOT NULL PRIMARY KEY,
activity INT UNSIGNED NOT NULL,
dnis CHAR(10),
state ENUM('active','closed','broken','summed','completed',
'archived') NOT NULL,
flags SET('needs_transreq'),
data TEXT NOT NULL,
INDEX state (state),
INDEX activity (activity,dnis)
) TYPE = INNODB;
#!/usr/bin/perl
use DBI;
use strict;
my($livedsn)="DBI:mysql:live;mysql_read_default_file=/home/archuser/.my.cnf";
my($testdsn)="DBI:mysql:test;mysql_read_default_file=/home/archuser/.my.cnf";
$main::livedbh=DBI->connect($livedsn,undef,undef, { RaiseError => 1, AutoCommit => 0
});
$main::testdbh=DBI->connect($testdsn,undef,undef, { RaiseError => 1, AutoCommit => 0
});
$main::dbh=$main::testdbh;
while(1)
{
my($rows,$switchId,$dnis,$data,$count);
my($cmd)="SELECT switchId,dnis,data FROM LCCer WHERE state='closed' ORDER BY
activity;";
my($sth)=$main::dbh->prepare($cmd);
$sth->execute();
while(($switchId,$dnis,$data)=$sth->fetchrow_array())
{
$main::dbh->do("UPDATE LCCer SET state='summed' where switchId='$switchId';");
$main::dbh->commit();
$count++;
}
$sth->finish();
if($count<1)
{
print("sleeping for 10\n");
sleep(10);
}
}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
