Hi there,
we use a wrapper class to access a mysql db with php-4.0.6 running in
Apache/Linux and mysql-3.23.40.
We ran into the following problem:
Assume the following test code (sorry - rather long):
-----[ test code ]------
<?
/* defines DB_M_SERVER and so on... */
require($DOCUMENT_ROOT . "/../../htdocs_includes/www.fhh.de/config.mysql.php");
/* the database wrapper class */
class NWN_DB {
// Variablen
var $reader; // DB-Verbindung aus der gelesen wird
var $reader_rs; // Result-ID
var $reader_nr; // Num Rows
var $writer; // DB-Verbindung in die geschrieben wird
var $writer_rs; // Result-ID
var $writer_li; // Last ID
var $writer_ar; // Affected Rows
var $writer_transaction = 0; // Grade in einer Transaktion?
function NWN_DB () {
// Constructor
global $DB_M_server, $DB_M_user, $DB_M_pass, $DB_M_dbase;
global $DB_S_server, $DB_S_user, $DB_S_pass, $DB_S_dbase;
unset($this->reader,$this->writer);
( $this->writer = mysql_connect($DB_M_server, $DB_M_user, $DB_M_pass)
) || die ("Konnte keine Verbindung zum Master herstellen!");
mysql_select_db($DB_M_dbase, $this->writer) || die ("Konnte Master-DB
nicht auswaehlen!");
( $this->reader = mysql_connect($DB_S_server, $DB_S_user, $DB_S_pass)
) || die ("Konnte keine Verbindung zum Reader herstellen!");
mysql_select_db($DB_S_dbase, $this->reader) || die ("Konnte Reader-DB
nicht auswaehlen!");
$this->reader_rs = "";
$this->reader_nr = "";
$this->writer_rs = "";
$this->writer_li = "";
$this->writer_ar = "";
}
function close () {
// Verbindung kappen
mysql_close($this->writer);
mysql_close($this->reader);
unset($this->writer);
unset($this->reader);
}
function read ($SQL) {
// Von den Slaves lesen, Anzahl der Zeilen im Result Set festhalten
if ($SQL != "") {
// Wenn wir grade _nicht_ in einer Transaktion sind, vom
Reader lesen
if ($this->writer_transaction != 1) {
$this->reader_rs = mysql_query($SQL, $this->reader);
if (! $this->reader_rs) return FALSE;
$this->reader_nr = mysql_num_rows($this->reader_rs);
return TRUE;
} else {
// Innerhalb einer Transaktion vom Writer lesen, nur
der kann da die richtigen
// Daten haben. Das Result Set wird trotzdem in
$this->reader_rs gespeichert, damit
// die weiteren Zugriffsfunktionen dann auch innerhalb
der Transaktion funktionieren
$this->reader_rs = mysql_query($SQL, $this->writer);
if (! $this->reader_rs) return FALSE;
$this->reader_nr = mysql_num_rows($this->reader_rs);
return TRUE;
}
} else {
$this->reader_rs = "";
$this->reader_nr = "";
return FALSE;
}
}
function get () {
// Zeile aus dem aktuellen Result Set holen
return mysql_fetch_array($this->reader_rs);
}
} //class
function bla() {
$db2 = new NWN_DB;
print "reader_f: " . $db2->reader."<br>";
print "writer_f: " . $db2->writer."<br>";
$sql = "SELECT * FROM bookmarks";
$db2->read($sql);
echo "table bookmarks <br>";
while($tmp = $db2->get()) {
echo "db2 : $tmp[0] <br>";
}
$db2->close();
//if you omit this close(), $db3 will later get the same connection
//as db and db2
}
//lets go
$db = new NWN_DB;
print "reader1: " . $db->reader."<br>";
print "writer1: " . $db->writer."<br>";
$sql = "SELECT * FROM profile";
$db->read($sql);
bla(); //establishes the second db connection
/*
$db3 will get a new ressource id if bla() closes its connection
although the link is still in use by $db
--> conclusion: the reference count for the db link does not work somehow
probably this is related to the use of a class for the db connections
*/
$db3 = new NWN_DB;
$db3->read("SELECT * FROM mpr_attributes");
print "reader3: " . $db3->reader."<br>";
print "writer3: " . $db3->writer."<br>";
echo "table mpr_attributes <br>";
while($tmp = $db3->get()) {
echo "db3 : $tmp[0] <br>";
}
$db3->close();
//this still returns the correct results
//although the last $db->close() tells us
//there is no more mysql connection available
echo "table profile <br>";
while($tmp = $db->get()) {
echo "db : $tmp[0] <br>";
}
print "reader_a: " . $db->reader."<br>";
print "writer_a: " . $db->writer."<br>";
$db->close();
print "end object stuff <p><p>";
function bla2() {
global $DB_M_server, $DB_M_user, $DB_M_pass, $DB_M_dbase;
$conn2 = mysql_connect($DB_M_server, $DB_M_user, $DB_M_pass);
mysql_select_db($DB_M_dbase,$conn2);
$rs = mysql_query("SELECT * FROM mpr_attributes",$conn2);
echo "resource2: $conn2 <br>";
while ($tmp = mysql_fetch_row($rs)) {
print "conn2: $tmp[0] <br>";
}
mysql_close($conn2);
}
$conn = mysql_connect($DB_M_server, $DB_M_user, $DB_M_pass);
mysql_select_db($DB_M_dbase,$conn);
echo "resource1: $conn <br>";
$rs1 = mysql_query("SELECT * FROM profile",$conn);
bla2();
while ($tmp = mysql_fetch_row($rs1)) {
print "conn1: $tmp[0] <br>";
}
mysql_close($conn);
//now the same procedure without a function
print "<p><p>now without a function call<p><p>";
$conn3 = mysql_connect($DB_M_server, $DB_M_user, $DB_M_pass);
mysql_select_db($DB_M_dbase,$conn3);
$rs = mysql_query("SELECT * FROM mpr_attributes",$conn3);
echo "resource3: $conn3 <br>";
while ($tmp = mysql_fetch_row($rs)) {
print "conn3: $tmp[0] <br>";
}
$conn4 = mysql_connect($DB_M_server, $DB_M_user, $DB_M_pass);
mysql_select_db($DB_M_dbase,$conn4);
echo "resource4: $conn4 <br>";
$rs1 = mysql_query("SELECT * FROM profile",$conn4);
while ($tmp = mysql_fetch_row($rs1)) {
print "conn1: $tmp[0] <br>";
}
mysql_close($conn3);
mysql_close($conn4);
/*
here the close calls work as expected
so somehow this issue has something to do with resource handling in the
context of functions / classes
*/
?>
----[end of test code (finally)]----
the output is:
--------------
reader1: Resource id #1
writer1: Resource id #1
reader_f: Resource id #1
writer_f: Resource id #1
table bookmarks
db2 : 10
db2 : 11
db2 : 6
db2 : 2
db2 : 1
db2 : 9
db2 : 7
db2 : 8
reader3: Resource id #4
writer3: Resource id #4
table mpr_attributes
db3 : 1
db3 : 2
db3 : 3
db3 : 4
table profile
db : 16
db : 13
db : 11
db : 12
reader_a: Resource id #1
writer_a: Resource id #1
Warning: 1 is not a valid MySQL-Link resource in
/usr/local/httpd/htdocs/www.fhh.de/test.php on line 44
Warning: 1 is not a valid MySQL-Link resource in
/usr/local/httpd/htdocs/www.fhh.de/test.php on line 45
end object stuff
resource1: Resource id #6
resource2: Resource id #6
conn2: 1
conn2: 2
conn2: 3
conn2: 4
conn1: 16
conn1: 13
conn1: 11
conn1: 12
Warning: 6 is not a valid MySQL-Link resource in
/usr/local/httpd/htdocs/www.fhh.de/test.php on line 180
now without a function call
resource3: Resource id #9
conn3: 1
conn3: 2
conn3: 3
conn3: 4
resource4: Resource id #9
conn1: 16
conn1: 13
conn1: 11
conn1: 12
---------------------
So after a mysql_close inside a function/class was executed PHP seems to
assume that the db link with the used ressource id is closed.
Since it isn't closed (we do read from apparently closed connections
above), the final mysql_close reports "not a valid MySQL-Link resource".
So somehow PHP loses information about the number of connection that
are working on the same mysql link/ressource id when mysql_close is
called within a function/class.
There are two Bugs in the Bug database which might be related to this,
but they are marked "closed":
http://bugs.php.net/bug.php?id=11201
http://bugs.php.net/bug.php?id=8634
Can someone reproduce this behaviour?
TIA, Matthias, Eike, Carsten
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
