Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: DBD::mysql Unusual Behavior

by gmax (Abbot)
on Aug 23, 2002 at 07:17 UTC ( #192275=note: print w/replies, xml ) Need Help??


in reply to DBD::mysql Unusual Behavior

I tried to reproduce the problem, but I couldn't get the same result you mentioned.
#!/usr/bin/perl -w use DBI; use strict; my $dbh = DBI->connect("DBI:mysql:test;host=localhost" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", undef, undef, {RaiseError => 1}) or die "can't connect\n"; my $pairs = { ONE => 'RTEST', TWO => undef, THREE => 'ANOTHER' }; # Creating a dummy table to match the example $dbh->do(qq{CREATE TABLE IF NOT EXISTS web_sessions_aux (wsa_id INT not null, wsa_type char(10), wsa_key char(10), wsa_value char(10))}); my ($id,$type) = (1,'UNKNOWN'); my $sth = $dbh->prepare(" INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_value) VALUES (?,?,?,?)"); DBI->trace(2); foreach my $key (keys %$pairs) { my $key_value = defined($pairs->{$key})? $pairs->{$key} : ''; $sth->execute($id,$type,$key,$key_value); } DBI->trace(0); $dbh->disconnect();
This sample script shows what is going on behind the scenes.
DBI 1.21-nothread dispatch trace level set to 2 -> execute for DBD::mysql::st (DBI::st=HASH(0x81342c0)~0x80b1264 1 + 'UNKNOWN' 'TWO' '') -> dbd_st_execute for 081342f0 Binding parameters: INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_va +lue) VALUES (1,'UNKNOWN','TWO','') <- dbd_st_execute 1 rows <- execute= 1 at dbi_quoting_problem.pl line 29 -> execute for DBD::mysql::st (DBI::st=HASH(0x81342c0)~0x80b1264 1 + 'UNKNOWN' 'THREE' 'ANOTHER') -> dbd_st_execute for 081342f0 Binding parameters: INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_va +lue) VALUES (1,'UNKNOWN','THREE','ANOTH +ER') <- dbd_st_execute 1 rows <- execute= 1 at dbi_quoting_problem.pl line 29 -> execute for DBD::mysql::st (DBI::st=HASH(0x81342c0)~0x80b1264 1 + 'UNKNOWN' 'ONE' 'RTEST') -> dbd_st_execute for 081342f0 Binding parameters: INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_va +lue) VALUES (1,'UNKNOWN','ONE','RTEST') <- dbd_st_execute 1 rows <- execute= 1 at dbi_quoting_problem.pl line 29
Since the DBI seems to be doing its honest job correctly, I would investigate some side effects somewhere else. Is the problem replicable? Could you isolate the strange behavior in a stand-alone (possibly short) script? That would be useful to understand where the problem lays.
As an additional thought, this case looks similar to DBI-trace and $hDB-quote() interaction. The problem there was related to some CGI variables behaving differently under trace(0) and trace(2). I was not able to find out what exactly was happening in that case, except that interpolating the variable made the problem disappear.
In this case, with or without trace(2), I get a correct database update.
Maybe, if the two cases are related, we could reach a more enlightening conclusion.

HTH
_ _ _ _ (_|| | |(_|>< _|

Replies are listed 'Best First'.
Re^2: DBD::mysql Unusual Behavior
by tadman (Prior) on Aug 23, 2002 at 10:16 UTC
    This is going to sound crazy, I know, but I have a feeling DBI is actually paying some kind of attention to the internal type of the presented variables.

    During diagnosis, I tried to abstract the variable away into an array, which would make a copy and theoretically de-taint the orginal scalar. Then, a few things worked, surprisingly:
    my $foo = ''.$key_value; my $foo = "$key_value"; my $foo = eval($dbh->quote($key_value)); # Desperation
    Also, priming the query with some stupid data did the trick too:
    $sth->execute(1,"A","B","C");
    Now it just occured to me that the data in $key_value leading up to the call that failed was strictly numeric, except when either a) the code was literally converted to a scalar, or b) enquoted, to force stringification.

    Now, the question is, what does this mean?

    My busted test case, which I'm curious to see reproduced, is:
    my $sth = $dbh->prepare(" INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_value) VALUES (?,?,?,?)", {}); my $rv = $sth->execute("2012","CRS","crs_search_index",1) && $sth->execute("2012","CRS","crs_search_criteria","S") ; $sth->finish();
    Note the numeric in the fourth column (CHAR). Why would this matter?

    Update: Solved!
    It would seem to be a problem in DBD::mysql which checks the "type" of the scalar being passed to the execute call. From dbdimp.c:
    if (!ph->type) { ph->type = SvNIOK(ph->value) ? SQL_INTEGER : SQL_VARCHAR; }
    You can see here that if the placeholder type is not set, it uses the Perl internal representation as a hint. Unfortunately, once assigned, this is never checked again. To clear up the problem, I just assigned straight to SQL_INTEGER and everything is A-OK.
      This is going to sound crazy, I know, but I have a feeling DBI is actually paying some kind of attention to the internal type of the presented variables.
      I pretty much got the same gut feeling. It's not doing it intentionally, though...

      I gather that Perl's scalars have a pretty large internal representation, and perl turns one representation into another when needed if it didn't exist yet, on the fly. You say the one case that fails is numerical, while the other ones are strings. So only this one needs to be converted to a string.

      My guess is that, when Perl does this conversion, something goes astray, and modifies a bit (or a few more) in a location that doesn't belong to this scalar's structure. It could well be that this always happens, but that most of the time, we don't notice. After all, you say you're using 5.8.0, and not too many many people are using that yet. Well, my guess is that in your particular case, this stray modified bit is in this DBI statement handle, thereby corrupting it, and this is what throws this spurious error.

      All just speculation, really. :-)

      ps. What's the value of Re xor 2, anyway? ;-)

        Could be re() ** 2 but that would be silly.

        Re:4

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://192275]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2019-11-22 07:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (110 votes). Check out past polls.

    Notices?