PHP, PEAR DB and MySQL StoredProc Problem

Hey everyone, I have a really obscure but impassable problem with a
reasonably simple piece of php/PEAR DB/MySQL code

When calling several stored procs the first call succeeds, but
subsequent calls fail no matter which SP is called first it always
works, and the rest fail... with:
[color=blue][color=green][color=darkred]
>>> [nativecode=2013 ** Lost connection to MySQL server during query][/color][/color][/color]


I have a MySQL 5.05 database, I have PHP 4.3 and 5.0 (this error occurs
using both on several test servers) and I have PEAR::DB 1.80.

The problem I'm having is, I can't seem to call two stored procedures in
a row. The first call works perfectly, but the second always receives
the error [nativecode=2013 ** Lost connection to MySQL server during query]

This happens during the line: 379 of "pear/db/mysqli.php"

Which is in the function "simpleQuery($query)".....
if (!@mysqli_select_db($this->connection, $this->_db)) {
return $this->mysqliRaiseError(DB_ERROR_NODBSELECTED);
}


To reproduce ( and I can't seem to ever NOT reproduce this one...)

create two tables (with data) and two stored procs as follows:

================================================== =================


CREATE TABLE `host_headers` (
`host` varchar(100) NOT NULL,
`site_id` int(11) NOT NULL,
PRIMARY KEY (`host`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `templates` (

`template_id` bigint(20) NOT NULL auto_increment,

`site_id` int(11) NOT NULL,

`template_name` varchar(28) NOT NULL,

`enabled` tinyint(1) NOT NULL default '1',

PRIMARY KEY (`template_id`),

KEY `template_id` (`template_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;



INSERT INTO `host_headers` (host, site_id)
VALUES ('localhost, 1);

INSERT INTO `templates` (site_id, template_name, enabled)
VALUES (1, 'Default', 1);



DELIMITER \\

CREATE PROCEDURE `sp_get_site_from_hostname`(IN pHostname varchar(255))
BEGIN


SELECT site_id FROM host_headers WHERE host like pHostname;


END\\

CREATE PROCEDURE `sp_get_template` (IN pSiteID int)
BEGIN
SELECT template_name FROM templates WHERE site_id = pSiteID;
END\\

DELIMITER ;


================================================== =================
Then run the following PHP script. (required PHP, MySQL, PEAR DB)
================================================== =================

<?php
include_once('DB.php'); //PEAR::DB

//REPLACE WITH YOUR SETTINGS BELOW
$dsn = "mysqli:username:password@localhost/database";

$options = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL
);
$db =& DB::connect($dsn, $options);


//FIRST STORED PROC CALL SUCCEEDS
$hostname = 'localhost';
$sql = "call sp_get_site_from_hostname('$hostname')";
$rs =& $db->query($sql);
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$siteid = $row['site_id'];
echo "Site ID: $siteid<br/>";
}
$rs->free();


//SECOND AND SUBSEQUENT STORED PROCS FAIL
//WITH THE ERROR [2013]
$sql = "call sp_get_template($siteid)";
$rs =& $db->query($sql); // <<< causes error //
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$template = $row['template_name'];
echo "Template: $template<br/>":
}
$rs->free();

$db->disconnect();
?>



================================================== ======
Any suggestions are greatly appreciated...
Regards,
James.

re: PHP, PEAR DB and MySQL StoredProc Problem

I'm just going over this PHP stuff. I see one connection
item being used to access two database files? If this is
correct, try using two variables:

$db1
$db2

and setting each up to make their own appropriate connection.

Hope this helps.

--
Jim Carlock
Post replies to the newsgroup, thanks.

"James" <jprice@netspace.net.au> wrote:
Hey everyone, I have a really obscure but impassable problem with a
reasonably simple piece of php/PEAR DB/MySQL code

When calling several stored procs the first call succeeds, but
subsequent calls fail no matter which SP is called first it always
works, and the rest fail... with:
[color=blue][color=green][color=darkred]
>>> [nativecode=2013 ** Lost connection to MySQL server during query][/color][/color][/color]

I have a MySQL 5.05 database, I have PHP 4.3 and 5.0 (this error occurs
using both on several test servers) and I have PEAR::DB 1.80.

The problem I'm having is, I can't seem to call two stored procedures in
a row. The first call works perfectly, but the second always receives
the error [nativecode=2013 ** Lost connection to MySQL server during query]

This happens during the line: 379 of "pear/db/mysqli.php"

Which is in the function "simpleQuery($query)".....
if (!@mysqli_select_db($this->connection, $this->_db)) {
return $this->mysqliRaiseError(DB_ERROR_NODBSELECTED);
}

To reproduce ( and I can't seem to ever NOT reproduce this one...)

create two tables (with data) and two stored procs as follows:

================================================== =================
CREATE TABLE `host_headers` (
`host` varchar(100) NOT NULL,
`site_id` int(11) NOT NULL,
PRIMARY KEY (`host`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `templates` (
`template_id` bigint(20) NOT NULL auto_increment,
`site_id` int(11) NOT NULL,
`template_name` varchar(28) NOT NULL,
`enabled` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`template_id`),
KEY `template_id` (`template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `host_headers` (host, site_id)
VALUES ('localhost, 1);

INSERT INTO `templates` (site_id, template_name, enabled)
VALUES (1, 'Default', 1);

DELIMITER \\

CREATE PROCEDURE `sp_get_site_from_hostname`(IN pHostname varchar(255))
BEGIN
SELECT site_id FROM host_headers WHERE host like pHostname;
END\\

CREATE PROCEDURE `sp_get_template` (IN pSiteID int)
BEGIN
SELECT template_name FROM templates WHERE site_id = pSiteID;
END\\

DELIMITER ;

================================================== =================
Then run the following PHP script. (required PHP, MySQL, PEAR DB)
================================================== =================

<?php
include_once('DB.php'); //PEAR::DB

//REPLACE WITH YOUR SETTINGS BELOW
$dsn = "mysqli:username:password@localhost/database";

$options = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL
);
$db =& DB::connect($dsn, $options);

//FIRST STORED PROC CALL SUCCEEDS
$hostname = 'localhost';
$sql = "call sp_get_site_from_hostname('$hostname')";
$rs =& $db->query($sql);
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$siteid = $row['site_id'];
echo "Site ID: $siteid<br/>";
}
$rs->free();

//SECOND AND SUBSEQUENT STORED PROCS FAIL
//WITH THE ERROR [2013]
$sql = "call sp_get_template($siteid)";
$rs =& $db->query($sql); // <<< causes error //
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$template = $row['template_name'];
echo "Template: $template<br/>":
}
$rs->free();

$db->disconnect();
?>

================================================== ======
Any suggestions are greatly appreciated...
Regards,
James.