DBI:mysql does not return any value

Hello;

I'm on a strange problem in my script i can't resolved by myself, so i hope
that anybody has an idea.

The problem is that when i start a $dbh->prepare statement which will return
more than X rows the result is -1. Exactly the same statement on mysql shell
will return the result, in this case 1800 rows.

is there any limitation on the rows returned in DBI:mysql?
I connect from the machine where mysql is running

The statement will search inside of 1.800.000 items of its owners and this
are 1800 .
The statement in the script is:
select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
GROUP BY itSeller

$sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
$sth->execute();
# is searching in the table
print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
"\n";
# 1800
while($user_id = $sth->fetchrow_array()) {
# nothing
}

if i do so on mysql it will return the right numbers and rows.

max_allow_package = 80 MB
mysql version 5.0.32
Perl : 5.008004 (i386-linux-thread-multi)
OS : linux (2.6.15.6)
DBI : 1.46
DBD::mysql : 2.9006
DBD::XBase : 0.240
DBD::Sponge : 11.10
DBD::Proxy : 0.2004
DBD::File : 0.31
DBD::ExampleP : 11.12
DBD::DBM : 0.02





--
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: DBI:mysql does not return any value

Hi Yair;

>May be your perl version is old, the last working versions are 5.6 and 5.8.
>If the modules are new versions working into an old

perl -V
Summary of my perl5 (revision 5 version 8 subversion 4) ...

This is not my case

Thanks, any other idea!

Ciao Thomas


-----Original Message-----
From: Thomas Goik [mailto:liste@mi-espacio.com]
Sent: Tuesday, August 14, 2007 1:16 PM
To: perl@lists.mysql.com
Subject: DBI:mysql does not return any value

Hello;

I'm on a strange problem in my script i can't resolved by myself, so i hope
that anybody has an idea.

The problem is that when i start a $dbh->prepare statement which will return
more than X rows the result is -1. Exactly the same statement on mysql shell
will return the result, in this case 1800 rows.

is there any limitation on the rows returned in DBI:mysql?
I connect from the machine where mysql is running

The statement will search inside of 1.800.000 items of its owners and this
are 1800 .
The statement in the script is:
select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
GROUP BY itSeller

$sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
$sth->execute();
# is searching in the table
print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
"\n";
# 1800
while($user_id = $sth->fetchrow_array()) {
# nothing
}

if i do so on mysql it will return the right numbers and rows.

max_allow_package = 80 MB
mysql version 5.0.32
Perl : 5.008004 (i386-linux-thread-multi)
OS : linux (2.6.15.6)
DBI : 1.46
DBD::mysql : 2.9006
DBD::XBase : 0.240
DBD::Sponge : 11.10
DBD::Proxy : 0.2004
DBD::File : 0.31
DBD::ExampleP : 11.12
DBD::DBM : 0.02





--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=yairl@savion.huji.ac.il



--
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: DBI:mysql does not return any value

Hi,

What do you get if you remove the 'select ... FOUND_ROWS..' ?

Patrick

Thomas Goik wrote:

> Hello;
>
> I'm on a strange problem in my script i can't resolved by myself, so i
> hope that anybody has an idea.
>
> The problem is that when i start a $dbh->prepare statement which will
> return more than X rows the result is -1. Exactly the same statement
> on mysql shell will return the result, in this case 1800 rows.
>
> is there any limitation on the rows returned in DBI:mysql?
> I connect from the machine where mysql is running
>
> The statement will search inside of 1.800.000 items of its owners and
> this are 1800 .
> The statement in the script is:
> select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
> GROUP BY itSeller
>
> $sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
> $sth->execute();
> # is searching in the table
> print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
> "\n";
> # 1800
> while($user_id = $sth->fetchrow_array()) {
> # nothing
> }
>
> if i do so on mysql it will return the right numbers and rows.
>
> max_allow_package = 80 MB
> mysql version 5.0.32
> Perl : 5.008004 (i386-linux-thread-multi)
> OS : linux (2.6.15.6)
> DBI : 1.46
> DBD::mysql : 2.9006
> DBD::XBase : 0.240
> DBD::Sponge : 11.10
> DBD::Proxy : 0.2004
> DBD::File : 0.31
> DBD::ExampleP : 11.12
> DBD::DBM : 0.02
>
>
>
>
>


--
Patrick Galbraith, Senior Programmer
Grazr - Easy feed grazing and sharing
http://www.grazr.com

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




--
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: DBI:mysql does not return any value

Hi Kenny;

> What is in your $stats{$_}{where} variable? I'm wondering whether it's
> the problem.

In this hash are the diferent tasks to do:

my %stats = ( 'abc001' => {
bind => 1,
sql => qq[select count(*) from tblItems where itSeller = ? and
itClosedflag=0 ],
where => qq[select SQL_CALC_FOUND_ROWS itSeller from tblItems where
itClosedflag=0 GROUP BY itSeller],
},
'item_planstart' => {
bind=> 1,
sql => qq(select count(*) from tblItems where itSeller = ? and
itClosedflag=6 ),
where => qq(select itSeller from tblItems where itClosedflag=6 group
by itSeller),
},
......
);
@stats = keys %stats;
foreach(sort @stats) {

$sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr;
$sth->execute();
print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
"\n";

$sdb = $dbh->prepare($stats{$_}{sql});

while($user_id = $sth->fetchrow_array()) {

# perform the stat->sql query with
$sdb->execute($user_id);
$cnt = $sdb->fetchrow_array();

$dbh->do("replace stats.user_stat set user_id = $user_id,
name ='$_', value = " . ($cnt || 0));
}
}
.....
END


> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?

I used DBI->trace(4) to get some information.
DBI::db=HASH(0x82c7084) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI
1.46-ithread (pid 11592)
-> prepare for DBD::mysql::db (DBI::db=HASH(0x82a4030)~0x82c7084 '
select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
GROUP BY itSel
ler ') thr#814cc80
dbih_setup_handle(DBI::st=HASH(0x82c71e0)=>DBI::st=HASH(0x82 cb814),
DBD::mysql::st, 82c71ec, Null!)
dbih_make_com(DBI::db=HASH(0x82c7084), 82c7718, DBD::mysql::st, 216, 0)
thr#814cc80
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x82c71e0) at stats.pl line 79
-> execute for DBD::mysql::st (DBI::st=HASH(0x82c71e0)~0x82cb814)
thr#814cc80
-> dbd_st_execute for 082cb904
-> mysql_st_interal_execute
<- mysql_st_internal_execute ERROR: returning -1
<- dbd_st_execute 1800 rows
<- execute= 1800 at stats.pl line 80
! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x82cb814)~INNER)
thr#814cc80
! <- DESTROY= undef during global destruction
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x82c7084)~INNER)
thr#814cc80
&imp_dbh->mysql: 82c776c
! <- DESTROY= undef during global destruction


Bye Thomas


----- Original Message -----
From: "Kenny Scott"
To: "Thomas Goik"
Sent: Tuesday, August 14, 2007 10:03 PM
Subject: Re: DBI:mysql does not return any value

> Hi,
>
> That's odd, I can't replicate your error. I've created a single table:
>
> CREATE TABLE `foo` (
> `id` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=3501 DEFAULT CHARSET=latin1;
>
> I populate it with 3500 rows:
>
> ---
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "INSERT INTO foo SET id = NULL" );
> for ( 1 .. 3500 ) {
> $sth->execute();
> }
> ---
>
> I then run the following script to query it, which is similar to your
> script:
>
> ---
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "SELECT * FROM foo" );
> $sth->execute();
> my $total = $dbh->selectrow_array("select FOUND_ROWS()");
> while ( my ( $id ) = $sth->fetchrow_array() ) {
> print $id, "\n";
> }
>
> print "There were $total overall\n";
> ---
>
> It prints a line for each row that it selected from the table. and the
> total (3500 rows) at the end.
>
> What is in your $stats{$_}{where} variable? I'm wondering whether it's
> the problem.
>
> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?
>
> --
> Kenny
>
>
> Thomas Goik wrote:
>> Hello;
>>
>> I'm on a strange problem in my script i can't resolved by myself, so i
>> hope that anybody has an idea.
>>
>> The problem is that when i start a $dbh->prepare statement which will
>> return more than X rows the result is -1. Exactly the same statement on
>> mysql shell will return the result, in this case 1800 rows.
>>
>> is there any limitation on the rows returned in DBI:mysql?
>> I connect from the machine where mysql is running
>>
>> The statement will search inside of 1.800.000 items of its owners and
>> this are 1800 .
>> The statement in the script is:
>> select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
>> GROUP BY itSeller
>>
>> $sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
>> $sth->execute();
>> # is searching in the table
>> print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
>> "\n";
>> # 1800
>> while($user_id = $sth->fetchrow_array()) {
>> # nothing
>> }
>>
>> if i do so on mysql it will return the right numbers and rows.
>>
>> max_allow_package = 80 MB
>> mysql version 5.0.32
>> Perl : 5.008004 (i386-linux-thread-multi)
>> OS : linux (2.6.15.6)
>> DBI : 1.46
>> DBD::mysql : 2.9006
>> DBD::XBase : 0.240
>> DBD::Sponge : 11.10
>> DBD::Proxy : 0.2004
>> DBD::File : 0.31
>> DBD::ExampleP : 11.12
>> DBD::DBM : 0.02
>>
>>
>>
>>
>>
>


--
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: DBI:mysql does not return any value (resolved)

Hello List;

If any body likes to know, in the code it selfs where no problem.
To solve the strange behaviour of the fetchrow_array I changed it to
fetchrow_hashref and it worked like it should.

So thanks to anybody who looked a minute on my mails?

Ciao Thomas


----- Original Message -----
From: "Thomas Goik"
To:
Sent: Wednesday, August 15, 2007 9:24 AM
Subject: Re: DBI:mysql does not return any value

> Hi Kenny;
>
>> What is in your $stats{$_}{where} variable? I'm wondering whether it's
>> the problem.
>
> In this hash are the diferent tasks to do:
>
> my %stats = ( 'abc001' => {
> bind => 1,
> sql => qq[select count(*) from tblItems where itSeller = ? and
> itClosedflag=0 ],
> where => qq[select SQL_CALC_FOUND_ROWS itSeller from tblItems where
> itClosedflag=0 GROUP BY itSeller],
> },
> 'item_planstart' => {
> bind=> 1,
> sql => qq(select count(*) from tblItems where itSeller = ? and
> itClosedflag=6 ),
> where => qq(select itSeller from tblItems where itClosedflag=6
> group
> by itSeller),
> },
> .....
> );
> @stats = keys %stats;
> foreach(sort @stats) {
>
> $sth = $dbh->prepare(" $stats{$_}{where} ") || die
> $dbh->errstr;
> $sth->execute();
> print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
> "\n";
>
> $sdb = $dbh->prepare($stats{$_}{sql});
>
> while($user_id = $sth->fetchrow_array()) {
>
> # perform the stat->sql query with
> $sdb->execute($user_id);
> $cnt = $sdb->fetchrow_array();
>
> $dbh->do("replace stats.user_stat set user_id = $user_id,
> name ='$_', value = " . ($cnt || 0));
> }
> }
> ....
> END
>
>
>> Have you run "show warnings" after each SQL query in your script just to
>> see whether there's anything in there?
>
> I used DBI->trace(4) to get some information.
> DBI::db=HASH(0x82c7084) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI
> 1.46-ithread (pid 11592)
> -> prepare for DBD::mysql::db (DBI::db=HASH(0x82a4030)~0x82c7084 '
> select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
> GROUP BY itSel
> ler ') thr#814cc80
> dbih_setup_handle(DBI::st=HASH(0x82c71e0)=>DBI::st=HASH(0x82 cb814),
> DBD::mysql::st, 82c71ec, Null!)
> dbih_make_com(DBI::db=HASH(0x82c7084), 82c7718, DBD::mysql::st, 216, 0)
> thr#814cc80
> Setting mysql_use_result to 0
> <- prepare= DBI::st=HASH(0x82c71e0) at stats.pl line 79
> -> execute for DBD::mysql::st (DBI::st=HASH(0x82c71e0)~0x82cb814)
> thr#814cc80
> -> dbd_st_execute for 082cb904
> -> mysql_st_interal_execute
> <- mysql_st_internal_execute ERROR: returning -1
> <- dbd_st_execute 1800 rows
> <- execute= 1800 at stats.pl line 80
> ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x82cb814)~INNER)
> thr#814cc80
> ! <- DESTROY= undef during global destruction
> ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x82c7084)~INNER)
> thr#814cc80
> &imp_dbh->mysql: 82c776c
> ! <- DESTROY= undef during global destruction
>
>
> Bye Thomas
>
>
> ----- Original Message -----
> From: "Kenny Scott"
> To: "Thomas Goik"
> Sent: Tuesday, August 14, 2007 10:03 PM
> Subject: Re: DBI:mysql does not return any value
>
>> Hi,
>>
>> That's odd, I can't replicate your error. I've created a single table:
>>
>> CREATE TABLE `foo` (
>> `id` int(10) unsigned NOT NULL auto_increment,
>> PRIMARY KEY (`id`)
>> ) ENGINE=MyISAM AUTO_INCREMENT=3501 DEFAULT CHARSET=latin1;
>>
>> I populate it with 3500 rows:
>>
>> ---
>> #!/usr/bin/perl -w
>>
>> use strict;
>> use DBI;
>>
>> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
>> my $sth = $dbh->prepare( "INSERT INTO foo SET id = NULL" );
>> for ( 1 .. 3500 ) {
>> $sth->execute();
>> }
>> ---
>>
>> I then run the following script to query it, which is similar to your
>> script:
>>
>> ---
>> #!/usr/bin/perl -w
>>
>> use strict;
>> use DBI;
>>
>> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
>> my $sth = $dbh->prepare( "SELECT * FROM foo" );
>> $sth->execute();
>> my $total = $dbh->selectrow_array("select FOUND_ROWS()");
>> while ( my ( $id ) = $sth->fetchrow_array() ) {
>> print $id, "\n";
>> }
>>
>> print "There were $total overall\n";
>> ---
>>
>> It prints a line for each row that it selected from the table. and the
>> total (3500 rows) at the end.
>>
>> What is in your $stats{$_}{where} variable? I'm wondering whether it's
>> the problem.
>>
>> Have you run "show warnings" after each SQL query in your script just to
>> see whether there's anything in there?
>>
>> --
>> Kenny
>>
>>
>> Thomas Goik wrote:
>>> Hello;
>>>
>>> I'm on a strange problem in my script i can't resolved by myself, so i
>>> hope that anybody has an idea.
>>>
>>> The problem is that when i start a $dbh->prepare statement which will
>>> return more than X rows the result is -1. Exactly the same statement on
>>> mysql shell will return the result, in this case 1800 rows.
>>>
>>> is there any limitation on the rows returned in DBI:mysql?
>>> I connect from the machine where mysql is running
>>>
>>> The statement will search inside of 1.800.000 items of its owners and
>>> this are 1800 .
>>> The statement in the script is:
>>> select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
>>> GROUP BY itSeller
>>>
>>> $sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
>>> $sth->execute();
>>> # is searching in the table
>>> print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
>>> "\n";
>>> # 1800
>>> while($user_id = $sth->fetchrow_array()) {
>>> # nothing
>>> }
>>>
>>> if i do so on mysql it will return the right numbers and rows.
>>>
>>> max_allow_package = 80 MB
>>> mysql version 5.0.32
>>> Perl : 5.008004 (i386-linux-thread-multi)
>>> OS : linux (2.6.15.6)
>>> DBI : 1.46
>>> DBD::mysql : 2.9006
>>> DBD::XBase : 0.240
>>> DBD::Sponge : 11.10
>>> DBD::Proxy : 0.2004
>>> DBD::File : 0.31
>>> DBD::ExampleP : 11.12
>>> DBD::DBM : 0.02
>>>
>>>
>>>
>>>
>>>
>>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=liste@mi-espacio.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