Transaction rollback? Tim.
On Wed, Sep 18, 2002 at 08:36:59PM +0100, Cillian Sharkey wrote: > Folks, > > Perhaps this is a simple problem, but it's baffled me. My setup: > > NetBSD 1.6, Postgres 7.2.1, DBI 1.21, DBD-Pg 1.13, Perl 5.6.1 > > As user "www" I can use psql to connect to my database and perform > SELECTs, UPDATEs, INSERTs etc. with no problems. > > So I have a perl script using DBI which I also run as the www user and > it can connect to the db and perform SELECTs with no problems. However > INSERTs and UPDATEs do not work. The exact same query that works in psql > fails to work in the script. > > Now, the bizarre part is that there are *no* error messages and the > operation appears to be successful i.e. for an UPDATE it returns that 1 > row was affected even though the operation never took effect! > (verifying with psql before and after). I have explicity set AutoCommit > and PrintError to on even though they are the defaults. I enabled > tracing but I can't see where it's going wrong. I have attached the > output from trace(5) and my perl script. > > Now if I try an UPDATE on a non-existant row it does return a value of > zero as expected (i.e. no row was updated). > > It's not a permissions problem; it works fine in psql as the same user > and to verify this, I ran the script as a different user who only has > SELECT access and it returned a permission denied error as expected. So > it's definitely not an access or permission problem. > > I have tried both prepare + execute() and do(). I have used the same > code and setup on a different machine (running Solaris) and it works > perfectly. > > Any ideas? This has been driving me nuts! > > Please CC me on any responses, thanks. > > -- > Cillian > DBI::db=HASH(0x8200edc) trace level set to 5 in DBI 1.21-nothread > -> prepare for DBD::Pg::db (DBI::db=HASH(0x8200f78)~0x8200edc 'update users set >username = ? where username = ?') > dbih_setup_handle(DBI::st=HASH(0x810b884)=>DBI::st=HASH(0x810b8c0), DBD::Pg::st, >810b890, Null!) > dbih_make_com(DBI::db=HASH(0x8200edc), DBD::Pg::st, 144) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), Err, DBI::db=HASH(0x8200edc)) >SCALAR(0x818f03c) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), State, DBI::db=HASH(0x8200edc)) >SCALAR(0x81b9890) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), Errstr, DBI::db=HASH(0x8200edc)) >SCALAR(0x818f00c) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), Handlers, DBI::db=HASH(0x8200edc)) >ARRAY(0x8200f3c) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), Debug, DBI::db=HASH(0x8200edc)) 5 >(already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), FetchHashKeyName, >DBI::db=HASH(0x8200edc)) 'NAME' (already defined) > dbih_setup_attrib(DBI::st=HASH(0x810b8c0), HandleError, DBI::db=HASH(0x8200edc)) >undef (not defined) > dbd_st_prepare: statement = >update users set username = ? where username = ?< > dbd_st_preparse: statement = >update users set username = ? where username = ?< > dbd_preparse scanned 2 distinct placeholders > <- prepare= DBI::st=HASH(0x810b884) at ./test.pl line 10 > -> execute for DBD::Pg::st (DBI::st=HASH(0x810b884)~0x810b8c0 'testing2' >'testing') > dbd_bind_ph > bind :p1 <== 'testing2' (type 0) > dbd_st_rebind > bind :p1 <== 'testing2' (size 8/9/0, ptype 4, otype 1043) > bind :p1 <== 'testing2' (size 8/8, otype 1043, indp 0) > dbd_bind_ph > bind :p2 <== 'testing' (type 0) > dbd_st_rebind > bind :p2 <== 'testing' (size 7/8/0, ptype 4, otype 1043) > bind :p2 <== 'testing' (size 7/7, otype 1043, indp 0) > dbd_st_execute > dbd_st_execute: statement = >update users set username = 'testing2' where username = >'testing'< > <- execute= 1 at ./test.pl line 11 > -> finish for DBD::Pg::st (DBI::st=HASH(0x810b884)~0x810b8c0) > <- finish= 1 at ./test.pl line 12 > -> rows for DBD::Pg::st (DBI::st=HASH(0x810b884)~0x810b8c0) > dbd_st_rows > <- rows= 1 at ./test.pl line 14 > -> disconnect for DBD::Pg::db (DBI::db=HASH(0x8200f78)~0x8200edc) > dbd_db_disconnect > <- disconnect= 1 at ./test.pl line 18 > -> DESTROY for DBD::Pg::st (DBI::st=HASH(0x810b8c0)~INNER) > dbd_st_destroy > <- DESTROY= undef during global destruction > -> DESTROY for DBD::Pg::db (DBI::db=HASH(0x8200edc)~INNER) > dbd_db_destroy > <- DESTROY= undef during global destruction
