Bug #8657

execute() returns 0 on successful manip query

Details

Submitted	2006-09-07 17:55 UTC
From	auldridgej at gmail dot com
Assigned	quipo
Status	Closed
Package	MDB2_Driver_pgsql
PHP Version	4.4.3
OS	FreeBSD 6.1
Roadmaps	(Not assigned)

Comments

[2006-09-07 17:55 UTC] auldridgej at gmail dot com

Description:
------------
I am having a problem on a few machines where execute() returns 0 when successfully executing a prepared statement whose 3rd argument was passed as MDB2_PREPARE_MANIP. I can see the data changing in the database, but always get 0 as number of rows returned. However, when directly working with PostgreSQL via PHP, or using MDB2 exec(), I get the proper number of affected rows.

This is true on the following machine configurations:

I worked with IRC user lsmith on #pear who suggested I try to add this line:
$mdb2->setOption('emulate_prepared',true);
to my test code. This corrected the affectd rows from being misreported, though obviously doesn't answer the question as to why this setting is required in some places but not in others. lsmith asked me to open this bug report, so here it is. :)

Test script:
---------------
<?php
//GET the PEAR file for MDB2
require 'MDB2.php';
//SET UP ERROR HANDLING
function handle_pear_error ($error_obj)
{
die('<pre>PEAR-Error '.
$error_obj->getMessage().': '.$error_obj->getUserinfo().
'</pre>');
}
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');
//CONNECTION INFO
$dsn = array(
'phptype' => 'pgsql',
'username' => 'postgres',
'password' => 'MyPassword',
'hostspec' => 'localhost',
'database' => 'testdb',
);
//MAKE THE CONNECTION, INSTANCIATE THE OBJECT
$mdb2 =& MDB2::factory($dsn);

/* ADDED BY SUGGESTION OF LSMITH, COMMENTING OUT HERE BECAUSE THIS HIDES THE ACTUAL REPORTED PROBLEM

//SETTING OPTION TO FIX execute()
$mdb2->setOption('emulate_prepared',true);

//QUERY PREPARE/EXECUTE STUFF
$query = "INSERT INTO testtable (data1) VALUES(?)";
$stmt = $mdb2->prepare($query,array('text'),MDB2_PREPARE_MANIP);
$data = array('hello world');
$aRows = $stmt->execute($data);
//WHAT DID WE COME UP WITH?
if(!PEAR::isError($aRows)){
echo "".$aRows." rows affected\n";
}
//DID DB ROW COUNT INCREASE?
$result = $mdb2->query("SELECT * FROM testtable");
echo "".$result->numRows()." rows now in DB\n";

//Try exec()
$aRows = $mdb2->exec("INSERT INTO testtable (data1) VALUES('hello world')");
if(!PEAR::isError($aRows)){
echo "".$aRows." rows affected\n";
}
//DID DB ROW COUNT INCREASE?
$result = $mdb2->query("SELECT * FROM testtable");
echo "".$result->numRows()." rows now in DB\n";
?>

Expected result:
----------------
1 rows affected
21 rows now in DB
1 rows affected
22 rows now in DB

Actual result:
--------------
0 rows affected
21 rows now in DB
1 rows affected
22 rows now in DB

[2006-09-11 13:36 UTC] auldridgej at gmail dot com

I completely rebuilt the first box listed in the list of boxes having the problem. It is still having the problem. Its new specs are as follows:

[2006-11-22 19:27 UTC] auldridgej at gmail dot com

Hello Lorenzo,

I have modified the test script to add your debug suggestions and smooth out the output. It now looks like so:
-------------------------------------
<html>
<head>
<title>Testing PEAR MDB2 Bug #8657</title>
<style type="text/css">
b{color: #f00;}
</style>
</head>
<body>
<?php
/*
Uses following DB schema:

CREATE DATABASE testdb;
CREATE TABLE testtable
(
data1 text
)
WITH OIDS;

*/
//CONNECTION INFO
$dsn = array(
'phptype' => 'pgsql',
'username' => 'MyPostgresUser',
'password' => 'MyPostgresPassword',
'hostspec' => 'localhost',
'database' => 'testdb',
);

//GET the PEAR file for MDB2
require('MDB2.php');
//SET UP ERROR HANDLING
function handle_pear_error ($error_obj){
die('<h1>PEAR-Error</h1><pre>'.$error_obj->getMessage().' : '.$error_obj->getUserinfo()."</pre>\n");
}
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');

//MAKE THE CONNECTION, INSTANCIATE THE OBJECT
$mdb2 =& MDB2::factory($dsn);
//BEEF UP DEBUGGING
$mdb2->setOption('debug',2);

//ADDED BY SUGGESTION OF LSMITH
//UNCOMMENT FOLLOWING LINE TO USE EMULATED PREPARES AND FIX THE RETURN ISSUE IN execute()--THIS ONLY FIXES A SYMPTOM, THOUGH, NOT THE PROBLEM
//$mdb2->setOption('emulate_prepared',true);

//COUNT NUMBER OF ROWS BEFORE DOING INSERT
$result = $mdb2->query('SELECT * FROM testtable');
echo ''.$result->numRows()." rows in DB before any MANIP queries\n";

//QUERY PREPARE/EXECUTE STUFF
$query = 'INSERT INTO testtable (data1) VALUES(?)';
$stmt = $mdb2->prepare($query,array('text'),MDB2_PREPARE_MANIP);
$data = array('hello world');
$aRows = $stmt->execute($data);
//WHAT DID WE COME UP WITH?
if(!PEAR::isError($aRows)){
echo 'Ran execute(), it returned '.$aRows." as number of rows affected\n";
}
//DID DB ROW COUNT INCREASE?
$result = $mdb2->query("SELECT * FROM testtable");
echo ''.$result->numRows()." rows in DB after running execute()\n";

//Try exec()
$aRows = $mdb2->exec("INSERT INTO testtable (data1) VALUES('hello world')");
if(!PEAR::isError($aRows)){
echo 'Ran exec(), it returned '.$aRows." as number of rows affected\n";
}
//DID DB ROW COUNT INCREASE?
$result = $mdb2->query("SELECT * FROM testtable");
echo ''.$result->numRows()." rows in DB after running exec()\n";

//DUMP DEBUG INFO
echo '<h1>MDB2 Debug Output:</h1><pre>';var_dump($mdb2->getDebugOutput());echo"</pre>\n";
?>
</body>
</html>
-------------------------------------

I also ran this on three different machines. Two of them continued failing, the other worked fine. One of the two failed machines is the same as one that failed before, with some software updates. The other that failed is totally new hardware built from the ground up. The failed machines start working fine as soon as I turn on emulated prepares. The one that passed without prepare emulation was on hardware that passed before but has been rebuilt from the ground up on a new OS and everything. Before I list the machine specs, here is what the failed output looks like:
-------------------------------------
<html>
<head>
<title>Testing PEAR MDB2 Bug #8657</title>
<style type="text/css">
b{color: #f00;}
</style>
</head>
<body>
12 rows in DB before any MANIP queries
Ran execute(), it returned 0 as number of rows affected

13 rows in DB after running execute()
Ran exec(), it returned 1 as number of rows affected
14 rows in DB after running exec()
<h1>MDB2 Debug Output:</h1><pre>string(554) "query(1): SELECT * FROM testtable
prepare(1): INSERT INTO testtable (data1) VALUES(?)
query(1): PREPARE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 (text) AS INSERT INTO testtable (data1) VALUES($1)
execute(1): PREPARE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 (text) AS INSERT INTO testtable (data1) VALUES($1)
query(1): EXECUTE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 ('hello world')
query(1): SELECT * FROM testtable
query(1): INSERT INTO testtable (data1) VALUES('hello world')
query(1): SELECT * FROM testtable
"
</pre>

</body>
</html>
-------------------------------------

The machine specs are as follows:

Note that while the two that failed are on PostgreSQL 7.4, it has passed on 7.4 before (see original bug report).

Thanks,
Jim

[2007-02-27 14:38 UTC] andrew dot hill at openads dot org

I have been doing some testing on this "bug", and can confirm that of the two versions of PostgreSQL I have installed (7.4 and 8.1), it only seems to be an issue with PostgreSQL 7.4.

This appears to be entirely separate from the MDB2_Driver_pgsql class; the PHP pg_affected_rows built in function simply does not return the affected rows when using a prepared statement via the SQL "PREPARE" statement in PostgreSQL 7.4.

HTH.

[2007-03-01 13:18 UTC] auldridgej at gmail dot com

Thanks all for the extra analysis and comments. Looking into the possible PHP problem.