FWD: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

Hi,

I posted the following email to dbi-users list but have so far received no
responses. I hoped I might have more luck here. I'd appreciate any ideas on how
to persue this.

Thanks

-----FW: -----

Date: Fri, 10 Feb 2006 12:04:33 -0000 (GMT)
From: "Martin J. Evans"
To: dbi-users@perl.org
Subject: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

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
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

--------------End of forwarded message-------------------------

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


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

It sounds to me like DATEDIFF and DATE might be messing things up a bit,
hence the problems go away after they're gone.

I myself use perl to do the Date calculations then translate it for the
database.

It avoids issues where database syntax changes as well as your problem.

Try it with perl doing the calculations, or just supply arbitrary date
info to
get it working as a request. It will isolate the issue a bit more, as
well as
allow you to get what you want done until you figure out what's going
on with
'DATEDIFF', and 'DATE'.

You might also want to try the mod_perl group they're really good about
knowing the internals of Packages like DBI and DBD::mysql:

modperl@perl.apache.org



Boysenberry

boysenberrys.com | habitatlife.com | selfgnosis.com

On Feb 14, 2006, at 5:22 AM, Martin J. Evans wrote:

> Hi,
>
> I posted the following email to dbi-users list but have so far
> received no
> responses. I hoped I might have more luck here. I'd appreciate any
> ideas on how
> to persue this.
>
> Thanks
>
> -----FW: -----
>
> Date: Fri, 10 Feb 2006 12:04:33 -0000 (GMT)
> From: "Martin J. Evans"
> To: dbi-users@perl.org
> Subject: Strange selectall_hashref/fetchall_hashref problem with
> DBD::mysql
>
> 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
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
> --------------End of forwarded message-------------------------
>
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=boysenberry@humaniteque.co m
>
>
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

Payne,

Thanks for the suggestions.

On 14-Feb-2006 Boysenberry Payne wrote:
> It sounds to me like DATEDIFF and DATE might be messing things up a bit,
> hence the problems go away after they're gone.

Yes, that was my conclusion.

> I myself use perl to do the Date calculations then translate it for the
> database.
>
> It avoids issues where database syntax changes as well as your problem.

I'm not saying I cannot do that but as I did say, this was my attempt to
simplify the problem. The actual SQL in the real application involves
procedures and cannot be changed. The procedures isolate the application from
database differences.

> Try it with perl doing the calculations, or just supply arbitrary date
> info to
> get it working as a request. It will isolate the issue a bit more, as
> well as
> allow you to get what you want done until you figure out what's going
> on with
> 'DATEDIFF', and 'DATE'.

I've done that. If you replace the datediff in the SQL where with an exact date
it works.

> You might also want to try the mod_perl group they're really good about
> knowing the internals of Packages like DBI and DBD::mysql:
>
> modperl@perl.apache.org

Just in case anyone else is following this thread it has been pointed out to me
privately that I over-simplified the problem. I said I was doing:

selectall_hashref($sql, 'race_id')

and in fact race_id is not the unique key for the result-set. The unique key
for the result-set is a combination of race_id and meeting_id. However:

selectall_hashref($sql, ['race_id', 'meeting_id'])

does not work either. Neither does inventing a different key using methods like
adding the following to the select list and changing the key in the
selectall_hashref to 'pkey':

CONCAT_WS('$;',m.meeting_id,r.race_id) AS pkey

(@id := @id + 1) AS pkey

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


> On Feb 14, 2006, at 5:22 AM, Martin J. Evans wrote:
>
>> Hi,
>>
>> I posted the following email to dbi-users list but have so far
>> received no
>> responses. I hoped I might have more luck here. I'd appreciate any
>> ideas on how
>> to persue this.
>>
>> Thanks
>>
>> -----FW: -----
>>
>> Date: Fri, 10 Feb 2006 12:04:33 -0000 (GMT)
>> From: "Martin J. Evans"
>> To: dbi-users@perl.org
>> Subject: Strange selectall_hashref/fetchall_hashref problem with
>> DBD::mysql
>>
>> 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
>> --
>> Martin J. Evans
>> Easysoft Ltd, UK
>> http://www.easysoft.com
>>
>> --------------End of forwarded message-------------------------
>>
>> --
>> Martin J. Evans
>> Easysoft Ltd, UK
>> http://www.easysoft.com
>>
>>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org