UPDATE statements causing me grief!

[Eirik Toft]

Greetings, been a DBI user for years now working with stuff from
unixODBC, Oracle, MySQL, etc...etc...

So, the issue I'm having is with a simple update statement to a mysql
database.

Here's a small snippet of code

my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
name=?)";

my $DBUSER="user";
my $DBPASS="password";
my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";

my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);

my $sth = $dbh->prepare($MAPSQL);
unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
   warn "Database error: ".$dbh->errstr;
}

So, for some reason, the update never happens, and I get no errors. I
have enabled SQL tracing and from the output, I get the following
statement:

UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
FROM variables WHERE name='varname1');

Now, if I actually run the mysql client, and paste this statement into
it and execute it, it works perfectly.

I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
1.612.

On a side note, because I know someone is going to ask, I use the same
database handle ($dbh) on other SELECT statements and they all return
data fine (so it's not a database connectivity issue).

Does anyone have any ideas, or at least can give me a good kick in the
head as to where I should look next?

Thanks in advance;

Eirik..

Re: UPDATE statements causing me grief!

[Martin Hall]

Just a thought - but how are you checking the update?  Just after the 
statement in the same Perl code or in a separate session?


Cheers

Martin

On 06/10/2011 23:04, Eirik Toft wrote:

Greetings, been a DBI user for years now working with stuff from
unixODBC, Oracle, MySQL, etc...etc...

So, the issue I'm having is with a simple update statement to a mysql
database.

Here's a small snippet of code

my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
name=?)";

my $DBUSER="user";
my $DBPASS="password";
my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";

my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);

my $sth = $dbh->prepare($MAPSQL);
unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
warn "Database error: ".$dbh->errstr;
}

So, for some reason, the update never happens, and I get no errors. I
have enabled SQL tracing and from the output, I get the following
statement:

UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
FROM variables WHERE name='varname1');

Now, if I actually run the mysql client, and paste this statement into
it and execute it, it works perfectly.

I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
1.612.

On a side note, because I know someone is going to ask, I use the same
database handle ($dbh) on other SELECT statements and they all return
data fine (so it's not a database connectivity issue).

Does anyone have any ideas, or at least can give me a good kick in the
head as to where I should look next?

Thanks in advance;

Eirik..



--

Re: UPDATE statements causing me grief!

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

> my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);

It's always a good idea to explictly set AutoConnect here. 
I also like to set PrintError=>0 and RaiseError=>1

> So, for some reason, the update never happens, and I get no errors.

Try changing it to a SELECT statement and see what comes back.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201110070943
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk6PAesACgkQvJuQZxSWSsgDYQCg5K9uqthUP1+5WViFT6JpUdeG
KMsAnAspjf+A8nVov+8AZM9wFSyoPIHs
=jbhS
-END PGP SIGNATURE-

Re: UPDATE statements causing me grief!

[ericbambach1]

Eirik Toft  wrote on 10/06/2011 05:04:10 PM:

> Eirik Toft  
> 10/07/2011 03:51 AM
> 
> To
> 
> 
> 
> cc
> 
> Subject
> 
> UPDATE statements causing me grief!
> 
> Greetings, been a DBI user for years now working with stuff from
> unixODBC, Oracle, MySQL, etc...etc...
> 
> So, the issue I'm having is with a simple update statement to a mysql
> database.
> 
> Here's a small snippet of code
> 
> my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
> machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
> name=?)";
> 
> my $DBUSER="user";
> my $DBPASS="password";
> my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";
> 
> my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);
> 
> my $sth = $dbh->prepare($MAPSQL);
> unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
>warn "Database error: ".$dbh->errstr;
> }
> 
> So, for some reason, the update never happens, and I get no errors. I
> have enabled SQL tracing and from the output, I get the following
> statement:
> 
> UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
> machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
> FROM variables WHERE name='varname1');
> 
> Now, if I actually run the mysql client, and paste this statement into
> it and execute it, it works perfectly.
> 
> I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
> 1.612.
> 
> On a side note, because I know someone is going to ask, I use the same
> database handle ($dbh) on other SELECT statements and they all return
> data fine (so it's not a database connectivity issue).
> 
> Does anyone have any ideas, or at least can give me a good kick in the
> head as to where I should look next?
> 
> Thanks in advance;
> 
> Eirik..
> 
> 

Eirik,

I know it sounds silly but did you forget to commit? I've been 
doing DB programming for years but every now and again I waste 10-15 
minutes on mysteriously disappearing data because I forgot to commit.


Please consider the environment before printing this email.

Re: UPDATE statements causing me grief!

[tiger peng]

Some time, even committed, the data was still mysteriously disappearing while 
another tool worked perfectly.

Why??


I was playing on different databases!

>        I know it sounds silly but did you forget to commit? I've been 
> doing DB programming for years but every now and again I waste 10-15 
> minutes on mysteriously disappearing data because I forgot to commit.

Re: UPDATE statements causing me grief!

[Bruce Johnson]

On Oct 7, 2011, at 7:25 AM, tiger peng wrote:

> Some time, even committed, the data was still mysteriously disappearing while 
> another tool worked perfectly.
> 
> Why??
> 

ROFL.

A while back, while I was making changes to a production system, I modified the 
authentication scripts so that when I logged in all my changes went to test 
tables, not the production tables. (after all, I'm not the worlds most 
interesting man )

This was a system that I didn't use for real a whole lot.

A couple months later I go to use the system for real...and it doesn't work...I 
spent a half-day trying to figure out why it worked for other people, and not 
me...:-) I suspect we've all committed more than our share of Stupid Programmer 
Tricks.

> 
> I was playing on different databases!
> 
>> I know it sounds silly but did you forget to commit? I've been 
>> doing DB programming for years but every now and again I waste 10-15 
>> minutes on mysteriously disappearing data because I forgot to commit.

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: UPDATE statements causing me grief!

[Puneet Kishor]

On Oct 7, 2011, at 11:32 AM, Bruce Johnson wrote:

> 
> On Oct 7, 2011, at 7:25 AM, tiger peng wrote:
> 
>> Some time, even committed, the data was still mysteriously disappearing 
>> while another tool worked perfectly.
>> 
>> Why??
>> 
> 
> ROFL.
> 
> A while back, while I was making changes to a production system, I modified 
> the authentication scripts so that when I logged in all my changes went to 
> test tables, not the production tables. (after all, I'm not the worlds most 
> interesting man )
> 
> This was a system that I didn't use for real a whole lot.
> 
> A couple months later I go to use the system for real...and it doesn't 
> work...I spent a half-day trying to figure out why it worked for other 
> people, and not me...:-) I suspect we've all committed more than our share of 
> Stupid Programmer Tricks.
> 


no shit... I do it all the bloody time. I change my javascript code, and keep 
on refreshing the web site, which refuses to behave the "new" way. Then I 
realize, I have changed the javascript code in the test site, and am refreshing 
the prod site.

All the fancy tools notwithstanding, human mind can handle only so much 
complexity.

Which is why drivers shouldn't be allowed to text (or talk on the phone, or 
shave, or check their lipstick, or...). Lives are at stake here.

--
Puneet Kishor

Re: UPDATE statements causing me grief!

[Eirik Toft]

On Oct 7, 1:59 am, martin.h...@oracle.com (Martin Hall) wrote:
> Just a thought - but how are you checking the update?  Just after the
> statement in the same Perl code or in a separate session?
>
> Cheers
>
> Martin
>
> On 06/10/2011 23:04, Eirik Toft wrote:
>
>
>
>
>
>
>
>
>
> > Greetings, been a DBI user for years now working with stuff from
> > unixODBC, Oracle, MySQL, etc...etc...
>
> > So, the issue I'm having is with a simple update statement to a mysql
> > database.
>
> > Here's a small snippet of code
>
> > my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
> > machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
> > name=?)";
>
> > my $DBUSER="user";
> > my $DBPASS="password";
> > my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";
>
> > my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);
>
> > my $sth = $dbh->prepare($MAPSQL);
> > unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
> >     warn "Database error: ".$dbh->errstr;
> > }
>
> > So, for some reason, the update never happens, and I get no errors. I
> > have enabled SQL tracing and from the output, I get the following
> > statement:
>
> > UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
> > machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
> > FROM variables WHERE name='varname1');
>
> > Now, if I actually run the mysql client, and paste this statement into
> > it and execute it, it works perfectly.
>
> > I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
> > 1.612.
>
> > On a side note, because I know someone is going to ask, I use the same
> > database handle ($dbh) on other SELECT statements and they all return
> > data fine (so it's not a database connectivity issue).
>
> > Does anyone have any ideas, or at least can give me a good kick in the
> > head as to where I should look next?
>
> > Thanks in advance;
>
> > Eirik..
>
> --

Actually, I'm checking both - using the same Perl code, and also in a
separate mysql session.

Re: UPDATE statements causing me grief!

[Eirik Toft]

On Oct 7, 6:43 am, g...@turnstep.com ("Greg Sabino Mullane") wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
> > my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);
>
> It's always a good idea to explictly set AutoConnect here.
> I also like to set PrintError=>0 and RaiseError=>1
>
> > So, for some reason, the update never happens, and I get no errors.
>
> Try changing it to a SELECT statement and see what comes back.
>
> - --
> Greg Sabino Mullane g...@turnstep.com
> End Point Corporationhttp://www.endpoint.com/
> PGP Key: 0x14964AC8 
> 201110070943http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -BEGIN PGP SIGNATURE-
>
> iEYEAREDAAYFAk6PAesACgkQvJuQZxSWSsgDYQCg5K9uqthUP1+5WViFT6JpUdeG
> KMsAnAspjf+A8nVov+8AZM9wFSyoPIHs
> =jbhS
> -END PGP SIGNATURE-

I did, and the select statement works.

Re: UPDATE statements causing me grief!

[Eirik Toft]

On Oct 6, 3:04 pm, grep_...@yahoo.com (Eirik Toft) wrote:
> Greetings, been a DBI user for years now working with stuff from
> unixODBC, Oracle, MySQL, etc...etc...
>
> So, the issue I'm having is with a simple update statement to a mysql
> database.
>
> Here's a small snippet of code
>
> my $MAPSQL="UPDATE varmaptest SET value=? WHERE grpid=(SELECT id FROM
> machinegroups WHERE name=?) AND varid=(SELECT id FROM variables WHERE
> name=?)";
>
> my $DBUSER="user";
> my $DBPASS="password";
> my $SQLDS="dbi:mysql:database=$CADB;host=databasehost";
>
> my $dbh = DBI->connect($SQLDS,$DBUSER,$DBPASS);
>
> my $sth = $dbh->prepare($MAPSQL);
> unless ($sth->execute("newval","group1.mygroup.mine","varname1")) {
>    warn "Database error: ".$dbh->errstr;
>
> }
>
> So, for some reason, the update never happens, and I get no errors. I
> have enabled SQL tracing and from the output, I get the following
> statement:
>
> UPDATE varmaptest SET value='newval' WHERE grpid=(SELECT id FROM
> machinegroups WHERE name='group1.mygroup.mine') AND varid=(SELECT id
> FROM variables WHERE name='varname1');
>
> Now, if I actually run the mysql client, and paste this statement into
> it and execute it, it works perfectly.
>
> I'm using perl 5.10.1, mysql database version 5.1.49, DBI version
> 1.612.
>
> On a side note, because I know someone is going to ask, I use the same
> database handle ($dbh) on other SELECT statements and they all return
> data fine (so it's not a database connectivity issue).
>
> Does anyone have any ideas, or at least can give me a good kick in the
> head as to where I should look next?
>
> Thanks in advance;
>
> Eirik..

Thanks to everyone's help, finally after setting the appropriate trace
levels, I discovered that despite my best effort to eliminate them
(DOS versus UNIX), I had a rouge carriage return stuck in one of the
variables.

Thanks for everyone's help - I knew it was something stupid.  Thanks
to those who pointed me to the right trace settings to discover the
issue!

Eirik..