OK, Tim's prompting (off list) has spurred me into action. I'm posting
back to the list, just in case anyone else is interested.
There's some good news and some bad news
First the good news: setting mode to "threads" in the config file and
restarting dbiproxy certainly allows multiple connections. I wrote a
small test script [1 -- below] which did a cycle of deletes, inserts,
updates and selects and ran it from several hosts concurrently and it
seemed to work ok (at least for several hundred iterations).
My setup is clients running on Linux, dbiproxy on Win2000 using ODBC to
Centura SQLBase. [NB, I have ActiveState 5.8.3 build 809, which comes with
Storable 2.09, which does not have the fix for threads -- that came in
with 2.10]
Now for the bad:
1. If the client dies (I killed it) or fails to disconnect, the thread in
dbiproxy stays alive (I guess) and keeps its dbi handle, sometimes after
reporting the error:
<date/time> err, 3, Child died: Error while reading socket: Bad
file descriptor at C:/Perl/site/lib/RPC/PlServer/Comm.pm line 110.
Other times, dbiproxy gives no error message.
If the handle has any locks, they're not released. I tried changing
connect to connect_cached in the client, but it made no difference. I had
to kill dbiproxy to release the database connections.
I guess this problem is something to do with (a) RPC::Pl* or (b)
Net::Daemon or (c) the threads in my perl. I've no experience of the
forking method of multiple connections, so I can't speculate on (a) or
(b). In case it's (c), I've included a perl -V at [4] below.
If the client disconnects cleanly, the database connection gets released.
2. [Concerning DBI::Proxy, rather than dbiproxy] My first test was just a
quick one with AutoCommit = 1, but even so, the DB occasionally detected
deadlock (it uses page-level locks for updates). When I tried using
proper transactions with begin_work and commit, DBD::Proxy doesn't seem to
allow you to start a new transaction after committing a previous one: the
sequence
$dbh->commit;
$dbh->begin_work;
produces the error
DBD::Proxy::db begin_work failed: Already in a transaction at
<line no of the begin_work>.
If I reset AutoCommit between the 2 ($dbh->{AutoCommit} = 1), then the
error goes away. [3] below has a DBI_Trace=2 of what went on.
So, it looks encouraging on the Storable front, but there seems to be
another problem elsewhere.
best regards, John.
----------------------------------
Versions I was using:
Linux clients (various machines)
Perl: 5.8.0 (as in RH 8)
DBI: 1.35 and 1.43
Net::Daemon: 0.30 and 0.38
PlRPC: 0.2012 and 0.2017
Storable: 2.06 and 2.13
Win2000 server (generally speaking, the latest ppms I could find)
Perl: 5.8.3 build 809
DBI: 1.42
DBD::ODBC: 1.07
Net-Daemon: 0.37
PlRPC: 0.0217
Storable: 2.12 -- see [2] below.
---------------------------------------
[1] (the table project_class already exists in my db:
class varchar(2), descr varchar(40), is_active varchar(1)
)
tst_proxy.pl
#!/usr/local/bin/perl -w
# called on host condor as
# ./tst_proxy.pl 51 condor 1000 1000
# on host portland:
# ./tst_proxy.pl 52 portland 2000 1000
use strict;
use DBI;
die "Usage: $0 class descr0 base max_iters" unless @ARGV == 4;
my ($class, $descr0, $base, $max_iters) = @ARGV;
my $dbname = "odbc_prins";
my $connect = "DBI:ODBC:$dbname";
my $host = "florida";
$connect = "dbi:Proxy:hostname=$host;port=1235;dsn=$connect";
warn "Connect string = $connect\n";
my $dbh = DBI->connect( $connect, "sysadm", "sysadm", { AutoCommit => 1,
RaiseError => 1, printError => 0 } ) or die $DBI::errstr;
for my $n (1..$max_iters) {
my $descr = sprintf( "%s insert n = %d", $descr0, $n+$base );
$dbh->do( q{ delete from project_class where class = ? },
{}, $class );
$dbh->do( q{ insert into project_class( class, descr, is_active )
values( ?, ?, ? ) },
{}, $class, $descr, "N" );
check( $n, $class, $descr, "N" );
$descr = sprintf( "%s update n = %d", $descr0, $n+$base );
$dbh->do( q{ update project_class set descr = ? where class = ? },
{}, $descr, $class );
check( $n, $class, $descr, "N" );
warn "end of iteration $n" if ($n < 5 || $n % 50 == 0);
}
sub check {
my ($n, $c, $d, $a) = @_;
my ($t1, $t2, $t3) = $dbh->selectrow_array( q{ select class,
descr, is_active
from project_class where class = ? },
{}, $c );
if ($t1 ne $c || $t2 ne $d || $t3 ne $a) {
warn "!error in interation $n:\n $1 should be $c\n $t2
should be $d\n $t3 should be $a";
}
}
warn "disconnecting";
$dbh->disconnect;
---------------------------------------------
[2]. Ppm for Storable version >= 2.10. This doesn't seem to be available
from ActiveState nor theoryx5 (they both have earlier versions according
to my ppm search). The place I found it (v2.12) was crazyinsomniac
(http://crazyinsomniac.perlmonk.org/perl/ppm/5.8). This installis it in
site_lib, so there's also some hackery to avoid using the 'standard'
Storable in lib (I just renamed lib/Storable.pm and lib/auto/Storable)
---------------------------------------------
[3].
[EMAIL PROTECTED] jre]$ DBI_TRACE=2 tst_proxy3.pl
DBI 1.43-ithread default trace level set to 0x0/2 (pid 4917)
DBI::VERSION = 1.43
Connect string =
dbi:Proxy:hostname=florida;port=1235;dsn=DBI:ODBC:odbc_prins
->
DBI->connect(dbi:Proxy:hostname=florida;port=1235;dsn=DBI:ODBC:odbc_prins,
sysadm, ****, HASH(0x804c8d4))
-> DBI->install_driver(Proxy) for linux perl=5.008 pid=4917 ruid=159
euid=159
install_driver: DBD::Proxy version 0.2004 loaded from
/home/jre/perl/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Proxy.pm
<- install_driver= DBI::dr=HASH(0x8157b74)
-> connect for DBD::Proxy::dr (DBI::dr=HASH(0x8157b74)~0x806356c
'hostname=florida;port=1235;dsn=DBI:ODBC:odbc_prins' 'sysadm' ****
HASH(0x82b7ef4)) thr#804bd00
<- connect= DBI::db=HASH(0x8063560) at DBI.pm line 595
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'RaiseError' 1) thr#804bd00
<- STORE= 1 at DBI.pm line 642
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'PrintError' 1) thr#804bd00
<- STORE= 1 at DBI.pm line 642
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'AutoCommit' 1) thr#804bd00
<- STORE= '1' at DBI.pm line 642
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER 'Username'
'sysadm') thr#804bd00
<- STORE= '1' at DBI.pm line 645
-> FETCH for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER 'Username')
thr#804bd00
<- FETCH= 'sysadm' at DBI.pm line 645
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'printError' 0) thr#804bd00
<- STORE= '' at DBI.pm line 645
-> FETCH for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'printError') thr#804bd00
<- FETCH= undef at DBI.pm line 645
<- connect= DBI::db=HASH(0x8063560)
-> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'dbi_connect_closure' CODE(0x80634e8)) thr#804bd00
<- STORE= 1 at DBI.pm line 665
-> begin_work in DBD::_::db for DBD::Proxy::db
(DBI::db=HASH(0x8063560)~0x82ce5c4) thr#804bd00
1 <> FETCH= 1 ('AutoCommit' from cache) at DBI.pm line 1531
1 -> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER
'AutoCommit' 0) thr#804bd00
1 <- STORE= '1' at DBI.pm line 1533
1 -> STORE for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER 'BegunWork'
1) thr#804bd00
1 <- STORE= '1' at DBI.pm line 1534
<- begin_work= 1 at tst_proxy3.pl line 19
-> do in DBD::_::db for DBD::Proxy::db
(DBI::db=HASH(0x8063560)~0x82ce5c4 ' delete from project_class where class
= ? ' HASH(0x804c9d0) '51') thr#804bd00
1 -> prepare for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER ' delete
from project_class where class = ? ' HASH(0x804c9d0)) thr#804bd00
1 <- prepare= DBI::st=HASH(0x82ce924) at DBI.pm line 1427
-> execute for DBD::Proxy::st (DBI::st=HASH(0x82ce924)~0x82d40c4 '51')
thr#804bd00
1 -> FETCH for DBD::Proxy::st (DBI::st=HASH(0x82d40c4)~INNER 'Database')
thr#804bd00
1 <- FETCH= DBI::db=HASH(0x8063560) at Proxy.pm line 474
1 <> FETCH= 1 ('proxy_proto_ver' from cache) at Proxy.pm line 475
1 <> FETCH= RPC::PlClient::Object::DBI::ProxyServer::db=HASH(0x82ca288)
('proxy_dbh' from cache) at Proxy.pm line 505
1 <> FETCH= RPC::PlClient=HASH(0x82ca180) ('proxy_client' from cache) at
Proxy.pm line 515
1 <> FETCH= 0 ('NUM_OF_FIELDS' from cache) at Proxy.pm line 531
<- execute= '0E0' at DBI.pm line 1428
-> rows for DBD::Proxy::st (DBI::st=HASH(0x82ce924)~0x82d40c4)
thr#804bd00
<- rows= '0E0' at DBI.pm line 1429
-> DESTROY for DBD::Proxy::st (DBI::st=HASH(0x82d40c4)~INNER)
thr#804bd00
<- DESTROY= undef at tst_proxy3.pl line 20
<- do= '0E0' at tst_proxy3.pl line 20
-> commit for DBD::Proxy::db (DBI::db=HASH(0x8063560)~0x82ce5c4)
thr#804bd00
<- commit= '1' at tst_proxy3.pl line 21
-> begin_work in DBD::_::db for DBD::Proxy::db
(DBI::db=HASH(0x8063560)~0x82ce5c4) thr#804bd00
1 <> FETCH= 0 ('AutoCommit' from cache) at DBI.pm line 1531
1 -> set_err in DBD::_::common for DBD::Proxy::db
(DBI::db=HASH(0x82ce5c4)~INNER 1 'Already in a transaction') thr#804bd00
!! ERROR: 1 'Already in a transaction' (err#1)
1 <- set_err= undef at DBI.pm line 1531
!! ERROR: 1 'Already in a transaction' (err#1)
<- begin_work= undef at tst_proxy3.pl line 23
DBD::Proxy::db begin_work failed: Already in a transaction at
./tst_proxy3.pl line 23.
DBD::Proxy::db begin_work failed: Already in a transaction at
./tst_proxy3.pl line 23.
-> DESTROY for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER)
thr#804bd00
-> disconnect for DBD::Proxy::db (DBI::db=HASH(0x82ce5c4)~INNER)
thr#804bd00
<- disconnect= 1 at Proxy.pm line 270
<- DESTROY= 1
-- DBI::END
-> disconnect_all for DBD::Proxy::dr
(DBI::dr=HASH(0x8157b74)~0x806356c) thr#804bd00
<- disconnect_all= (not implemented) at DBI.pm line 674
! -> DESTROY for DBD::Proxy::dr (DBI::dr=HASH(0x806356c)~INNER)
thr#804bd00
! <- DESTROY= undef during global destruction
-----------------------------------------------
[4] perl -V # on the win2000 server
Summary of my perl5 (revision 5 version 8 subversion 3) configuration:
Platform:
osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
uname=''
config_args='undef'
hint=recommended, useposix=true, d_sigaction=undef
usethreads=undef use5005threads=undef useithreads=define
usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cl', ccflags ='-nologo -Gf -W3 -MD -Zi -DNDEBUG -O1 -DWIN32
-D_CONSOLE -DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED
-DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO
-DPERL_MSVCRT_READFIX',
optimize='-MD -Zi -DNDEBUG -O1',
cppflags='-DWIN32'
ccversion='', gccversion='', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='link', ldflags ='-nologo -nodefaultlib -debug -opt:ref,icf
-libpath:"C:\Perl\lib\CORE" -machine:x86'
libpth=C:\PROGRA~1\MICROS~3\VC98\lib
libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
uuid.lib wsock32.lib mpr.lib winmm.lib version.lib odbc32.lib
odbccp32.lib msvcrt.lib
perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
uuid.lib wsock32.lib mpr.lib winmm.lib version.lib odbc32.lib
odbccp32.lib msvcrt.lib
libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
gnulibc_version='undef'
Dynamic Linking:
dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' ', lddlflags='-dll -nologo -nodefaultlib -debug
-opt:ref,icf -libpath:"C:\Perl\lib\CORE" -machine:x86'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES
PERL_IMPLICIT_CONTEXT PERL_IMPLICIT_SYS
Locally applied patches:
ActivePerl Build 809
22218 Remove the caveat about detached threads crashing on Windows
22201 Avoid threads+win32 crash by freeing Perl interpreter
slightly later
22169 Display 'out of memeory' errors using low-level I/O
22159 Upgrade to Time::Hires 1.55
22120 Make 'Configure -Dcf_by=...' work
22051 Upgrade to Time::HiRes 1.54
21540 Fix backward-compatibility issues in if.pm
Built under MSWin32
Compiled at Feb 3 2004 00:28:51
@INC:
C:/Perl/lib
C:/Perl/site/lib
.
