| Bug #48562 | fetch without execute error on stored procedures | ||
|---|---|---|---|
| Submitted: | 5 Nov 2009 11:42 | Modified: | 2 Dec 2011 8:26 |
| Reporter: | Stephen Fenwick-Paul | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S2 (Serious) |
| Version: | 4.007 onwards | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql_multi_results, stored procedures | ||
[10 Nov 2009 8:04]
Valeriy Kravchuk
Thank you for the problem report. I can't repeat this problem tho8ugh. Look: openxs@suse:/home2/openxs/dbs/5.1> perl -w bug48562.pl $VAR1 = { 'a' => '1' }; $VAR1 = { 'a' => '2' }; openxs@suse:/home2/openxs/dbs/5.1> cat bug48562.pl use DBI; use Data::Dumper; my ($connect_string) = "dbi:mysql:test;mysql_multi_results=1"; my $dbh = DBI->connect($connect_string, "root", "", { RaiseError => 1, }) or die "Couldn't connect to database: " . DBI->errstr; #my ($sql) = "select 'fred' AS 'name'"; # Will work my ($sql) = "call sptest()"; # Any proc with a result set my $sth = $dbh->prepare($sql) || die $DBI::err . ": " . $DBI::errstr; $sth->execute() || die $DBI::err. ": " .$DBI::errstr; my ($row); do { while ($row = $sth->fetchrow_hashref()) { print Dumper $row; } } until (!$sth->more_results); openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.1.41-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show create procedure sptest\G *************************** 1. row *************************** Procedure: sptest sql_mode: Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sptest`() select 1 as a union select 2 as a character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> call sptest(); +---+ | a | +---+ | 1 | | 2 | +---+ 2 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) What am I doing wrong? What version of MySQL server are you working with?
[10 Nov 2009 9:30]
Stephen Fenwick-Paul
I'm on Server version: 5.1.37-1ubuntu5 (Ubuntu) and also got the same error on 5.0.84 source distribution (on Fedora 10).
[10 Nov 2009 11:31]
Valeriy Kravchuk
Please, check if you'll get the same error with my sptest() in your environment.
[12 Nov 2009 14:07]
Stephen Fenwick-Paul
Yeap, same error.
[14 Nov 2009 12:01]
Valeriy Kravchuk
Can you, please, test with newer server versions, 5.1.40 or 5.0.86?
[15 Dec 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[19 Apr 2011 20:56]
Thomas Pries
Same problem here with mysql Ver 14.14 Distrib 5.1.46, for suse-linux-gnu (i686) using readline 6.1
[22 Apr 2011 17:06]
Valeriy Kravchuk
Thomas, 5.1.46 is still old enough, 5.1.56 is current... Anyway, in one of the previous comments (dated [10 Nov 2009 9:04] Valeriy Kravchuk) I copy/pasted results that shown everything worked as expected on 5.1.41 long time ago. Can you check if that exact test case fails for you?
[26 Apr 2011 14:46]
Thomas Pries
thomas@rhodos:~/test/perltest> cat bug.pl
use DBI;
use Data::Dumper;
my ($connect_string) = "dbi:mysql:test;mysql_multi_results=1";
my $dbh = DBI->connect("DBI:mysql:database=dbname;host=localhost", "dbuser", "userpasswd", {'RaiseError' => 1})
or die "Couldn't connect to database: " . DBI->errstr;
#my ($sql) = "select 'fred' AS 'name'"; # Will work
my ($sql) = "call bugtest(2)"; # Any proc with a result set
my $sth = $dbh->prepare($sql) || die $DBI::err . ": " . $DBI::errstr;
$sth->execute() || die $DBI::err. ": " .$DBI::errstr;
my ($row);
do {
while ($row = $sth->fetchrow_hashref()) {
print Dumper $row;
}
} until (!$sth->more_results);
thomas@rhodos:~/test/perltest>perl -w bug.pl
$VAR1 = {
'2' => '2'
};
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at bug.pl line 17.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at bug.pl line 17.
thomas@rhodos:~/test/perltest>
----------------
storedProc:
delimiter $$
DROP PROCEDURE IF EXISTS bugtest$$.
CREATE PROCEDURE bugtest( IN x INT )
DETERMINISTIC
BEGIN
IF( x =2 ) THEN
SELECT '2';
END IF ;
END$$
delimiter;
----------------------
mysql> call bugtest(2);
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[10 May 2011 11:21]
Stephen Fenwick-Paul
Been a long time, but I've had to build a new machine and now I'm on version: 5.1.54-1ubuntu4 and still get the same error (exactly the same as the previous poster).
[29 Jun 2011 12:45]
Jon Stephens
No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.
[29 Jun 2011 18:06]
Thomas Pries
I am still experiencing the problem and the last comment in this thread was: "[10 May 13:21] Stephen Fenwick-Paul ... on version: 5.1.54-1ubuntu4 and still get the same error (exactly the same as the previous poster)." I guess, 5.1.54 is close enough to the recommended version 5.1.56 that we can expect that this bug is not solved by an update.
[30 Jun 2011 8:43]
Jon Stephens
No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.
[18 Nov 2011 22:43]
Paul Pikowsky
I had a similar problem, but it turns out that I was using the statement,
'while(@row0 = $sth0->fetchrow_hashref()) {'
instead of
'while($row0 = $sth0->fetchrow_hashref()) {'.
Perhaps it is the use of 'my ($row);' in the first example that is the problem.
[2 Dec 2011 8:26]
Stephen Fenwick-Paul
Thanks for the suggestion alas it did not work, but, you get me looking at it again and simply removing the lines:
do {
} until (!$sth->more_results);
brought to life.
This paradigm is the one I've used for years and was touted as the way to handle procs. I guess things have changed.
Description: Error: DBD::mysql::st fetchrow_hashref failed: fetch() without execute() produced when fetching results from a stored procedure (SELECT from table fine.) How to repeat: my ($connect_string) = "dbi:mysql:test;mysql_multi_results=1"; my $dbh = DBI->connect($connect_string, "test", "", { RaiseError => 1, }) or die "Couldn't connect to database: " . DBI->errstr; #my ($sql) = "select 'fred' AS 'name'"; # Will work my ($sql) = "call sptest()"; # Any proc with a result set my $sth = $dbh->prepare($sql) || die $DBI::err . ": " . $DBI::errstr; $sth->execute() || die $DBI::err. ": " .$DBI::errstr; my ($row); do { while ($row = $sth->fetchrow_hashref()) { print Dumper $row; } } until (!$sth->more_results); Will produce: $VAR1 = { 'name' => 'joe' }; DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at ./testdbd.pl line 26. DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at ./testdbd.pl line 26. Suggested fix: I'm out of depth here but: in version 4.008 -> 4.013 (line 2784 of dbdimp.c) changing: if (imp_sth->result == NULL) { /* No "real" rowset*/ return 1; } to: if (imp_sth->result == NULL) { /* No "real" rowset*/ return 0; } fixes the above error, but: t/76multi_statement.........NOK 11/24 # Failed test at t/76multi_statement.t line 48. I notice that test does not including everything producing a result set.