persistent connections and temporary tables?

justdiy · September 13, 2007

hello all!

I'm having a heck of time understanding the way a few things work, or at least, the proper way to do them.

To start off, I have a mysql 5 database that's loaded up with lots of stored procedures I've written for handling a firewall log table. When I started on my project, I didn't worry about things like multiuser ability, or pagination. Now that I've got a lot of the backend work done, I'm trying to improve the front end.

My stored procs return their results in the form of a temporary table (engine=memory); as I understand the mysql documentation, a temporary table stored in memory exists only while the database connection is open, and is discarded once the connection closes. So that started me on a quest to maintain my connection to the database persistently across multiple page loads. This is were I start pulling my hair out.

First I find that some group of developers in their infinite wisdom have decided to not support persistent connections in the mysqli (mysql improved) functions, to that end, there is no mysqli_pconnect function. I've tried mysql_pconnect, but the (old fashioned) mysql functions don't understand stored procedures, and I get an error back from mysql that the result cannot be represented (or something like that). So I started searching for alternatives. At first, I considered switching to postgres instead of mysql; pg offers sprocs and the php functions for it offer persistent connections. Rather than go the route of reconfiguring everything, I kept searching. I then came across the php odbc functions. They seemed pretty straight forward, and functionally similar to the mysqli functions. So, I installed an odbc connector on my server, and changed my code to use odbc instead of mysqli.

Now I have odbc working correctly, the same way mysqli did previously. So now, I want to try to maintain a persistent connection.

According to the php docs, I need to call odbc_pconnect first to setup the connection, and then when I want to re-use that connection, I can call it again with the same dsn, username and password.

So lets say I visit index.php, which asks for a username and password, and then opens a connection to the database via odbc. I store the username and password in a session variable, which is subsequently checked by an include in all my other pages. The include grabs the u/p from the session array, and fires off another odbc_pconnect, presumably to reopen the db connection.

So far so good; its all working.

Now I decide to test if my db connection is actually being maintained, or just recreated. I fire off a stored proc using odbc_exec($link_id,"CALL mydb.myproc()") , followed by a query to grab results from the temporary table using odbc_exec again. This works as expected. However, in my code, I also set a session variable to indicate the sp has been run, so when I refresh the page, the sp will not be called, just the query on the temp table. This is where it breaks down.

Warning: odbc_exec() [function.odbc-exec]: SQL error: [unixODBC][MySQL][ODBC 5.1 Driver][mysqld-5.0.22]Table 'ulogdb.summary_results' doesn't exist, SQL state S0002 in SQLExecDirect in /var/www/html/db/new_summary2.php on line 29

The table summary_results should still be there if my db connection had been preserved, however, it seems the db connection was closed somewhere along the way, and mysql destroyed the table.

On a side note, odbc_pconnect causes apache to segfault if it passes an incorrect username or password to the odbc connector.

Here's my scripts:

<?php session_start(); ?>
<html><head><title>Connections Summary</title>
<?php
include 'styleref.php';

// Report all PHP errors (bitwise 63 may be used in PHP 3)
error_reporting(E_ALL ^ E_NOTICE);

$limit = 100;

include("opendb.php");

include("getdefs.php");

// run SP to generate table of in/out traffic over the last n hours
$starttime = microtime(true);
if($_SESSION['summary'] != "yes" || $_GET['refresh'] == "yes"){
$result = odbc_exec($link_id, "CALL new_summary($my_hours,'$my_trafdir')");
if (!$result) {
	echo odbc_error($link_id) ." : ". odbc_errormsg($link_id);
	die;
}
$_SESSION['summary'] = "yes";
}
$endtime = microtime(true);
$sproctime = $endtime - $starttime;

$starttime = microtime(true);
$result = odbc_exec($link_id, "SELECT * FROM summary_results ORDER BY 'time' DESC");
if (!$result) {
	echo odbc_error($link_id) ." : ". odbc_errormsg($link_id);
	$_SESSION['summary'] = "no";
	die;
}
$endtime = microtime(true);
$querytime = $endtime - $starttime;

$totalrows = odbc_num_rows($result);
$fields_num = odbc_num_fields($result);

$starttime = microtime(true);
if($my_trafdir == "out"){$direction = "Outbound to";}else{$direction = "Inbound from";}
echo "<h1>Packets $direction Internet</h1>\n";
echo "<h3>click on anything for detailed accounting</h3>\n";
echo "<h3>data returned from the past ";
if($my_hours > 1){echo "$my_hours hours</h3>\n";}
else{echo "hour</h3>\n";}

// generate table header based on field names from query
include("printtable.php");

// fetch row as an array which accepts a name based index
// index is case sensitive!
while($row = odbc_fetch_array($result))
{
// this variable contains the rule action obtained from the database
// it must be defined to something, even it it's not being used
$ruleaction = $row['rule'];

// this include applies a CSS class to each TR element in the table
// based on the action of the rule - current accept, deny and reject
include("row_colorizer.php");

// these variables are used by inter-links to build the various report
// links (hrefs)
// these varible must be defined to something, even if not used
$srcip = $row['srcip'];
$destip = $row['destip'];
$tcpport = $row['tcp'];
$udpport = $row['udp'];
$timestamp = $row['time'];

// this include builds hrefs based on the info above
include("inter-links.php");
?>

<TD><?=$timedetail?></TD>
<TD><?=$srcdetail?></TD>
<TD><?=$row['proto']?></TD>
<TD><?=$destdetail?></TD>
<TD><?=$tcpdetail?></TD>
<TD><?=$udpdetail?></TD>
<TD><?=$ruledetail?></TD>
<TD><?=$row['cnt']?></TD>
</TR>
<?
}
echo "</TABLE>";

$endtime = microtime(true);
$disptime = $endtime - $starttime;
odbc_free_result($result);
?>
<P>
<h3><a class="hdr" href="new_summary2.php?trafdir=in&hours=<?=$my_hours?>">Inbound Summary</a> - <a class="hdr" href="new_summary2.php?trafdir=out&hours=<?=$my_hours?>">Outbound Summary</a></h3>
<h4>sproc: <?=round($sproctime,3)?> query: <?=round($querytime,3)?> display: <?=round($disptime,3)?></h4>
</body></html>

and opendb.php:

<?
$db_dsn = 'Default';


   $form = '
    <center>
       <form action="'.$_SERVER['PHP_SELF'].'" method="post">
       <input type="text" name="username">
       <input type="password" name="password"><br>
       <input type="submit" name="submit" value="submit this">
       </form></center>
       ';
   
if(isset($_SESSION['username']))	// is the username session variable set?
{								// the username variable is set, we're good
// force odbc connection to be a specific username and password for now
// thanks to the odbc_pconnect bug
$link_id = odbc_pconnect('Default','work','damnit');
   	if(!$link_id){die("db=gone");}
   	else{$hm = "connected";}
echo "<h4>(welcome back ".$_SESSION['username'].")</h4>\n";
}else{								// nope?
if(isset($_POST['submit']))		// has the user submitted the form?
	{							// yes, they have!
	$user = $_POST['username'];
	$pass = $_POST['password'];
	$user = str_replace(" ", "", $user); //remove spaces
    	$pass = str_replace(" ", "", $pass); //remove spaces
    	$user = str_replace("%20", "", $user); //remove escaped spaces
    	$pass = str_replace("%20", "", $pass); //remove escaped spaces
    	// add slashes to escape things like quotes and apostrophes
    	// because they can be used to hijack SQL statements!
    	$user = addslashes($user);
    	$pass = addslashes($pass);

	// trial by fire, see if we can link up with the db server
	// force odbc connection to be a specific username and password for now
	// thanks to the odbc_pconnect bug
	$link_id = odbc_pconnect('Default','work','damnit');
    	if(!$link_id){$hm = "nope";}
    	else{$hm = "connected";}
    	
	if($hm == "connected"){		// auth successful
		$_SESSION['username'] = $_POST['username'];
		echo "<h4>(welcome aboard ".$_SESSION['username'].")</h4>\n";
	}else{						// auth failed
		echo "<h4>(username / password not valid)</h4>\n";
		echo $form;
		die;
	}
}else{							// default page load, display form
		echo "<h4>(please log in)</h4>\n";
		echo $form;			
		die;
}
}


?>

justdiy · September 13, 2007

I didn't want the first post getting too long. Here is the sproc in case it is relevant somehow.

BEGIN
DECLARE my_query1 TEXT;
DECLARE my_query2 TEXT;
DECLARE my_netdir TEXT;

/* first part of SQL statement */
SET my_query1 = "
CREATE TEMPORARY TABLE summary_results
ENGINE = MEMORY

SELECT
max(ulog.`timestamp`) AS 'time',
inet_ntoa(ip_saddr) AS 'srcip',
proto.protocol AS 'proto',
inet_ntoa(ip_daddr) AS 'destip',
ulogdb.ulog.tcp_dport AS 'tcp',
ulogdb.ulog.udp_dport AS 'udp',
ulogdb.ulog.oob_prefix AS 'rule',
count(*) AS 'cnt'

FROM ulogdb.ulog
Left Join ulogdb.ip_protocols AS proto ON proto.ip_protocol = ulog.ip_protocol

WHERE ";

/* second part of SQL statement */
SET my_query2 = "
GROUP BY ulog.ip_saddr, ulog.tcp_dport, ulog.udp_dport, ulog.ip_daddr, ulog.oob_prefix

ORDER BY 'time' DESC";

/* setup traffic direction */
IF trafdir = 'out' THEN /* display outgoing traffic */
SET my_netdir = "ulogdb.ulog.oob_out LIKE 'eth0' ";
ELSE		/* display incoming traffic */
SET my_netdir = "ulogdb.ulog.oob_in LIKE 'eth0' ";
END IF;

SET @s = CONCAT(my_query1, 'ulogdb.ulog.`timestamp` >= SUBDATE(Now(), INTERVAL ', hourcount, ' HOUR) AND ', my_netdir, my_query2);

PREPARE stmt1 FROM @s;

DROP TEMPORARY TABLE IF EXISTS summary_results ;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

END

Sign In

persistent connections and temporary tables?

Recommended Posts

justdiy

Link to comment

Share on other sites

justdiy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information