Some progress on Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

Regarding my posting with a problem using DBI 1.50 and DBD:mysql 3.0002_4
(reprinted below) I wonder if someone who knows DBI and DBD::mysql better
than I can point me in the right direction. I've discovered if I make a small
change to DBI it works (see commented out lines):

sub fetchall_hashref {
my ($sth, $key_field) = @_;

my $hash_key_name = $sth->{FetchHashKeyName} || 'NAME';
my $names_hash = $sth->FETCH("${hash_key_name}_hash");
my @key_fields = (ref $key_field) ? @$key_field : ($key_field);
my @key_indexes;
my $num_of_fields = $sth->FETCH('NUM_OF_FIELDS');
foreach (@key_fields) {
my $index = $names_hash->{$_}; # perl index not column
$index = $_ - 1 if !defined $index && DBI::looks_like_number($_) &&
$_>=1 && $_ <= $num_of_fields;
return $sth->set_err(1, "Field '$_' does not exist (not one of
@{[keys %$names_hash]})")
unless defined $index;
push @key_indexes, $index;
}
my $rows = {};
my $NAME = $sth->FETCH($hash_key_name);
my @row = (undef) x $num_of_fields;
# replace:
# $sth->bind_columns(\(@row));
# while ($sth->fetch) {
# with
while (@row = $sth->fetchrow_array) {
my $ref = $rows;
$ref = $ref->{$row[$_]} ||= {} for @key_indexes;
@{$ref}{@$NAME} = @row;
}
return $rows;
}

Thanks

Martin

There was a slight error in this posting - I was doing:

selectall_hashred(sql, ['meeting_id', 'race_id'])

Previous posting:

Hi,

selectall_hashref and fetchall_hashref work fine for me most of the time but
I've hit a scenario where I get a result I just cannot fathom.

I'm using DBI 1.50 and DBD::mysql 3.0002_4 (with a few minor patches I posted
on this list to make it compile and get rid of the FREE UNBIND problem I had).

I have tables:

race
race_id primary key auto increment
meeting_id foreign key to meeting_id in meeting

meeting
meeting_id primary key auto increment
created_date_time_utc datetime

(there are other columns but they are not referenced).

I do:

SELECT m.meeting_id,r.race_id FROM meeting m, race r where
r.meeting_id = m.meeting_id and
DATEDIFF('2006-02-10', DATE(m.created_date_time_utc)) <= 100

and dump the reference returned by selectall_hashref(race_id) or
fetchall_hashref(race_id) and get:

$VAR1 = {
'' => {
'race_id' => undef,
'meeting_id' => undef
}
};

The query does return rows. I know because:
1) if I change to use prepare/execute/fetchrow_array I can the rows
back
2) if I leave it as fetchall_hashref/selectall_hashref and set
DBI_TRACE to 20 I can see my data in the trace.

I can also change nothing other than switch to DBD::ODBC (and the myodbc
driver) and it works fine.

The interesting bit is if all I do is take the
"DATEDIFF('2006-02-10', DATE(m.created_date_time_utc)) <= 100" out of
the where clause it works, even though this makes no difference
I can see to either the number of rows returned, the column names or
result-set content. Unfortunatly the trace at level 20 is 120K.

I've tried reproducing with other tables which are more simple but
failed - the above is as simple as I can get it and fail.

Any ideas?

--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: Some progress on Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

On Tue, Feb 14, 2006 at 06:16:30PM -0000, Martin J. Evans wrote:
> Regarding my posting with a problem using DBI 1.50 and DBD:mysql 3.0002_4
> (reprinted below) I wonder if someone who knows DBI and DBD::mysql better
> than I can point me in the right direction. I've discovered if I make a small
> change to DBI it works (see commented out lines):
>
> sub fetchall_hashref {

> my @row = (undef) x $num_of_fields;
> # replace:
> # $sth->bind_columns(\(@row));
> # while ($sth->fetch) {
> # with
> while (@row = $sth->fetchrow_array) {
> my $ref = $rows;
> $ref = $ref->{$row[$_]} ||= {} for @key_indexes;
> @{$ref}{@$NAME} = @row;
> }
> return $rows;
> }

> There was a slight error in this posting - I was doing:
>
> selectall_hashred(sql, ['meeting_id', 'race_id'])

Try producing a very small self-contained test case (one that creates and
populates the tables it uses with a small amount of data) so others can
easily investigate.

Tim.

Re: Some progress on Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

On 15-Feb-2006 Tim Bunce wrote:
> On Tue, Feb 14, 2006 at 06:16:30PM -0000, Martin J. Evans wrote:
>> Regarding my posting with a problem using DBI 1.50 and DBD:mysql 3.0002_4
>> (reprinted below) I wonder if someone who knows DBI and DBD::mysql better
>> than I can point me in the right direction. I've discovered if I make a
>> small
>> change to DBI it works (see commented out lines):
>>
>> sub fetchall_hashref {
>
>> my @row = (undef) x $num_of_fields;
>> # replace:
>> # $sth->bind_columns(\(@row));
>> # while ($sth->fetch) {
>> # with
>> while (@row = $sth->fetchrow_array) {
>> my $ref = $rows;
>> $ref = $ref->{$row[$_]} ||= {} for @key_indexes;
>> @{$ref}{@$NAME} = @row;
>> }
>> return $rows;
>> }
>
>> There was a slight error in this posting - I was doing:
>>
>> selectall_hashred(sql, ['meeting_id', 'race_id'])
>
> Try producing a very small self-contained test case (one that creates and
> populates the tables it uses with a small amount of data) so others can
> easily investigate.
>
> Tim.

I have now reduced it to far less and quite simply:

drop table if exists test;
create table test (
created_date_time_utc datetime
);
insert into test values ('2006-02-14 10:11:12');

my $dbh = DBI->connect($dsn, $uid, $pwd);
$sql = q/select created_date_time_utc from test/;
my $sth = $dbh->prepare($sql);
$sth->execute;
my $cols = $sth->{NUM_OF_FIELDS};

my @row = (undef) x $cols;
$sth->bind_columns(\(@row));
while ($sth->fetch) {
print join(",", @row), "\n";
}
$sth->execute;
while (@row = $sth->fetchrow_array) {
print join(",", @row), "\n";
}

which produces:

Use of uninitialized value in join or string at mysqlbug2.pl line 25.

2006-02-14 10:11:12

The "Use of uninitialized..." is down to the print in the fetch with bound
columns.

The clue was the change to stop binding of columns in DBI.

I am using DBI 1.50, DBD::mysql 3.0002_4 and mysql 5.0.15.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: Some progress on Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

I've finally tracked this problem down and I believe it is down to the
code which turns off server_side_prepare if the SQL looks like a
create statement. Of course my statement looks like a create statement
because:

o it is "select created_date_time_utc from test"
o the code looks for "CREATE" or "create" anywhere in the SQL

I fixed it with the "bodge" on a "bodge" by ensuring the code looking
for "create" looks for "create " (but see below). However, I'd just
point out that it looks to me like

o the code looking for "create" etc appears to run off the end of the
SQL string whilst attempting to find "create", "drop" etc
because it always looks up to 6 chrs past the current position and
the current position is eventually set to the last chr in the SQL string.

o when MYSQL_VERSION >= MULTIPLE_RESULT_SET_VERSION and not doing
server_side_prepare the code uses my_get_fbav() then my_setup_fbav
and the latter appears to do a "av = newAV();" then av_store for
each column to extend av. I'm not an XS expert by any means but I
"think" this would prevent DBI's bind_columns working.

I think it was the combination of the two issues above that caused
the issue I was seeing.

For anyone else using DBD::mysql at the moment I'd seriously avoid
using an columns or tables which contain the strings
"create", "CREATE", "alter", "ALTER", "drop", "DROP", "show", "SHOW",
"call", "CALL" for now because you are likely to not get your data
back if you bind columns.

I'll happily have a go at a proper patch to fix if someone can explain what
my_get_fbav and my_setup_fbav were written to acheive.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

On 15-Feb-2006 Tim Bunce wrote:
> On Tue, Feb 14, 2006 at 06:16:30PM -0000, Martin J. Evans wrote:
>> Regarding my posting with a problem using DBI 1.50 and DBD:mysql 3.0002_4
>> (reprinted below) I wonder if someone who knows DBI and DBD::mysql better
>> than I can point me in the right direction. I've discovered if I make a
>> small
>> change to DBI it works (see commented out lines):
>>
>> sub fetchall_hashref {
>
>> my @row = (undef) x $num_of_fields;
>> # replace:
>> # $sth->bind_columns(\(@row));
>> # while ($sth->fetch) {
>> # with
>> while (@row = $sth->fetchrow_array) {
>> my $ref = $rows;
>> $ref = $ref->{$row[$_]} ||= {} for @key_indexes;
>> @{$ref}{@$NAME} = @row;
>> }
>> return $rows;
>> }
>
>> There was a slight error in this posting - I was doing:
>>
>> selectall_hashred(sql, ['meeting_id', 'race_id'])
>
> Try producing a very small self-contained test case (one that creates and
> populates the tables it uses with a small amount of data) so others can
> easily investigate.
>
> Tim.