Home » Database » DB » Bug #7194

[2006-03-24 10:04 UTC] kmh496 at kornet dot net

Description:
------------
bonjour,
i got no response on the PEAR mailing list, so i assume it's a bug.

why does my DB object always send INIT DB to mysql server? i am using
PHP 5.0.5 and
* @version Release: 1.7.5
* @link http://pear.php.net/package/DB
*/
the mysql_* functions from PHP do not init db like this!!

the problem caused by calls like the following -- very simple code,
really ---- may be caused by my PEAR/DB.php wrapper functions (see
below) . which are logically stupid, nevertheless, should i stop using
pear and go to mysql_query which does not do INIT DB in my server logs?
i need to run lots of queeries ( order of thousands at a shot) so ....
now, the evidence is my mysql query log (shown below the code, also),
please help me pinpoint the error................

if (!$db) $db=db_connect();^M
$this_userRS = db_query($db,$query);
$this_user= $this_userRS->fetchRow();
$top_bar_text = "these are old todo list entries by " .
$browse_user_name . " (". $this_user[0] .")";
$max_from_db_table = $this_user[0];

i use functions like this :

function db_connect() {
global $err,$errstring, $dsn,$mysql_host,$mysql_db,$mysql_user,
$mysql_password,$DEBUG_DICT_CONNECT,$options;
// Retries connection to server 5 times.
if ($DEBUG_DICT_CONNECT) echo "dsn = " . print_r($dsn) . "<br>";
for ($i = 0; !(@$db = DB::connect($dsn,$options)) && $i < 5; $i++)
{ }
if (DB::isError($db))
{
header("Location: http://" . $_SERVER['HTTP_HOST'] .
"/index.php?cfile=err&query=" . $err . "&errstring='" . $errstring."'");
exit;
}
else {
db_query($db,"set names utf8");
return $db;
}
} // end db_connect

and

function db_query(&$db,$query)
{
global $err,$errstring, $DEBUG_DICT, $tpf_admin_email;
$result = $db->query($query);
if (DB::isError($result))
{
exit;
}
else return $result;
}

function db_getAssoc(&$db,$query)
{
global $err,$errstring, $DEBUG_DICT, $_SERVER, $tpf_admin_email;
$result = $db->getAssoc($query,false,array(),
DB_FETCHMODE_ORDERED,true);
if (DB::isError($result))
{
exit;
}
else return $result;
}

and my server query log shows up stuff like

373 Init DB utf8_explicit
373 Query select
rss_id,rss_url,rss_name,rss_cache_time,rss_listnum,rss_listtype,from_lang from rss_info where rss_user = 'curious' and cid = '246' and from_lang='german'
373 Query select cid from rss_category where
cname= 'misc' and from_lang='french'
373 Init DB utf8_explicit
373 Query select
rss_id,rss_url,rss_name,rss_cache_time,rss_listnum,rss_listtype,from_lang from rss_info where rss_user = 'joe' and cid = '219' and from_lang='french'
373 Init DB utf8_explicit
373 Query select
rss_id,rss_url,rss_name,rss_cache_time,rss_listnum,rss_listtype,from_lang from rss_info where rss_user = 'curious' and cid = '219' and from_lang='french'

--
PEAR General Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Test script:
---------------
any query, with logging enabled in /etc/my.cnf

Expected result:
----------------
060322 20:31:35 4 Query select * from user_todo_english where wordid=36015
060322 20:31:37 4 Query select * from user_todo_english where wordid=36025
060322 20:31:43 4 Query select * from user_todo_english_english where wordid=36025
060322 20:31:56 4 Query select * from user_todo_english
060322 20:32:01 4 Query select * from user_todo_english_english
060322 20:32:27 4 Query select * from user_todo_english_english where wordid=36025

Actual result:
--------------
5 Query select rss_id,rss_url,rss_name,rss_cache_time,rss_listnum,rss_listtype,from_lang from rss_info where rss_user = 'joe' and cid = '27' and from_lang='english'
5 Init DB dict_explicit
5 Query select rss_id,rss_url,rss_name,rss_cache_time,rss_listnum,rss_listtype,from_lang from rss_info where rss_user = 'curious' and cid = '27' and from_lang='english'
5 Query select cid from rss_category where cname= 'blogs' and from_lang='korean'
5 Init DB dict_explicit
5 Query select rss_id,r

[2006-04-06 17:08 UTC] david at futuresbright dot com

Actually you can improve performance on this drastically by doing a "SELECT DATABASE" and only doing an Init DB if the currently selected database is different to the one you're meant to be querying.

I'm doing this in a DB abstraction layer I've written to imitate and replace Pear DB. For large numbers of queries I'm seeing anything up to 200% better performance than Pear DB.