php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40870 Stored proc warning 1329 produces NULL result set
Submitted: 2007-03-20 19:26 UTC Modified: 2007-11-27 01:00 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: key88sf at gmail dot com Assigned:
Status: No Feedback Package: MySQLi related
PHP Version: 5.2.1 OS: Windows Vista
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
16 + 5 = ?
Subscribe to this entry?

 
 [2007-03-20 19:26 UTC] key88sf at gmail dot com
Description:
------------
I have a simple stored procedure in MySQL 5.x. The proc first does a SELECT INTO query, followed by a regular SELECT to return it's result set.

The SELECT INTO generates a warning 1329 because no rows were fetched. However, the final SELECT works properly and returns 1 row.

However, the result returned from mysqli_query() is NULL !


Reproduce code:
---------------
Stored proc is this:

  DECLARE v_affiliate_id INTEGER DEFAULT NULL;

  -- This SELECT INTO returns 0 rows:
  select
	affiliate_id INTO v_affiliate_id 
  from 
	affiliates 
  where 
	user_name = v_user_name 
	and password_hash = v_password_hash;

  if ( v_affiliate_id IS NULL ) THEN
    SET v_affiliate_id := -1;
  end if;

  -- This is the result set row:
  select v_affiliate_id;


Expected result:
----------------
When I run this from the MySQL command line, I get 1 row, 1 warning (1329).

When I run from PHP, I *expect* to see a result set with 1 row.

Actual result:
--------------
When I run from PHP, the result set from mysqli_query() is NULL.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-03-20 19:35 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.


 [2007-03-21 07:52 UTC] key88sf at gmail dot com
Database table to create (MySQL):

CREATE TABLE affiliates (
  affiliate_id int(11) NOT NULL auto_increment,
  user_name varchar(32) NOT NULL,
  password_hash char(32) NOT NULL,
  PRIMARY KEY  (affiliate_id),
  UNIQUE KEY Index_User_Name (user_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO affiliates (user_name, password_hash) VALUES( 'test', '9b93b6ff07bfb6463bd0b5a6e65c4125' );

PHP Script to Demonstrate Bug:
<?php

$dbConnection = mysqli_connect("localhost", "root", "password");
$userName = "test";
$passwordHash = md5("some junk");
$sql = sprintf("CALL LoginAffiliate('%s', '%s')", $userName, $passwordHash);
$recordSet = mysqli_query( $dbConnection, $sql );
if ( !$recordSet )
{
  echo "ERROR:" . strval(mysqli_errno($dbConnection));
}
else
{
  echo "SUCCESS";
}
die();

?>
 [2007-03-21 10:22 UTC] tony2001@php.net
And the LoginAffiliate procedure, please.
 [2007-03-29 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, 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".
 [2007-11-19 20:56 UTC] hholzgra@php.net
Can't reproduce with either current 5.2 CVS or 5.2.1 release code
(see test case below). I tested with MySQL 5.0.45 server and client
library code, which MySQL versions did you see these problems?


--TEST--
Bug #40870 Stored proc warning 1329 produces NULL result set
--SKIPIF--
<?php if (!extension_loaded("mysqli")) print "skip"; ?>
--FILE--
<?php
$mysqli = mysqli_init();
$mysqli->real_connect('localhost', 'root', '', 'test');
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$mysqli->query("DROP TABLE IF EXISTS p1") or die($mysqli->error);
$mysqli->query("DROP PROCEDURE IF EXISTS p1") or die($mysqli->error);
$mysqli->query("CREATE PROCEDURE p1() BEGIN DECLARE foo INTEGER DEFAULT NULL; SELECT i INTO foo FROM t1; SELECT 23;END") or die($mysqli->error);


if($mysqli->multi_query ("CALL p1();"))
{
  do
  {
    if($objResult = $mysqli->store_result())
    {
      while($row = $objResult->fetch_assoc())
      {
        var_dump($row);
      }
      $objResult->close();
      if($mysqli->more_results())
      {
        print "----- next result -----------\n";
      }
    }
    else
    {
      print "no results found";
    }
  }while ( $mysqli->next_result());
}
else
{
        print $mysqli->error;
}
$mysqli->query("DROP PROCEDURE p1") or die($mysqli->error);
$mysqli->close();
?>
--EXPECT--
array(1) {
  [23]=>
  string(2) "23"
}
----- next result -----------
no results found


 [2007-11-27 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, 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".
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 00:01:28 2024 UTC