DBD::mysql Unusual Behavior

[tadman]

In the course of debugging something, there's this bit of code which is causing a lot of fuss:

my $sth = $dbh->prepare("
                      INSERT INTO web_sessions_aux 
                      (wsa_id, wsa_type, wsa_key, wsa_value)
                      VALUES (?,?,?,?)");

foreach my $key (keys %$pairs) {
     my $key_value = defined($pairs->{$key})?
                             $pairs->{$key} : '';

     $sth->execute($id,$type,$key,$key_value);
}

For some as-of-yet undetermined reason, MySQL was producing errors like this:

DBD::mysql::st execute failed: Unknown column 'RTEST' in 'field list' at /usr/local...

Now 'RTEST' was one of the possible $key_value values. Why was this being interpreted as a field? Placeholder bug? Bad SQL? Not sure. One thing that made it "go away" was:

$sth->execute($id,$type,$key,"$key_value");

How can a scalar be broken in such a way? This is under Perl 5.8.0.

[gmax]

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

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

[tadman]

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.

[bart]

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? ;-)

[bart]

Silly question (probably): you're sure $key_value isn't a reference, are you?

[tadman]

Here's some example values from a debugging exercise:

'2010','CRS','crs_search_static_vend','RTEST'

Looks and smells like a variable to me.

[BrowserUk]

Probably way off here, but isn't the correct way to test for the existance of a key exists rather than defined?

Doesn't the use of defined($pair->{unknown key} cause that unknown key to be autovivified? )

That's what I seem to remembering reading somewhere. I'll update if I find the reference. Not sure if that is the cause of your problem though.

---

What's this about a "crooked mitre"? I'm good at woodwork!

[tadman]

The correct way to check for the presence of a key is to use exists. You'll note, though, that these keys exist since they're from the keys call in the foreach. The concern is that the value will be undefined which is going to generate warnings if used.

Now regarding this so-called autovivification. People get so paranoid about that sort of thing that they don their tin-foil hats at the merest hint of trouble. It's a lot harder to do than you think. Merely thinking of a key is not enough:

use Data::Dumper;

my $foo = {};

1 if (defined($foo->{bar}));
print Dumper($foo);

1 if (defined($foo->{bar}->{baz}));
print Dumper($foo);

The second one causes autovivification since you're actually extending some pseudo-predefined structure. You can't dereference an undefined value, so it just makes one up for you.

[bart]

The concern is that the value will be undefined which is going to generate warnings if used.

Eh, no. Not in this particular case, anyway. The thing is that DBI's placeholder mechanism will convert every undef into the unquoted string "NULL", so that

INSERT INTO FOO (foo) VALUES (?)

will effectively mean

INSERT INTO FOO (foo) VALUES (NULL)

And some people's database setup will complain about that, if the field isn't allowed to be NULL. Personally, I like NULLs in my databases, for values that indeed have no value.