Jump to content

Archived

This topic is now archived and is closed to further replies.

DavidSWP

mysql_query hanging

Recommended Posts

Hi,

I would really appreciate some help with a problem I have using mysql_query in php 5, Mysql ver. 5

I'm executing a series of very simple sql SELECT queries in a loop using the PHP function mysql_query.  Individually the queries work fine, even the ones it crashes on, but in my script which executes maybe a thousand one after another it gets to a point where it just completely hangs.  Its hanging at different SELECT queries each time I run it which makes me think its a memory error.

Is there a simple solution to this problem that I am missing?  What steps should I take to track down the problem, there are no error messages at all, the function just fails to finish.

Thanks

DWP >:(

Share this post


Link to post
Share on other sites
You mean the script?

Ok there is a main script and a wrapper class around the mysql functions.  I have tracked the error down to the mysql_query call in the wrapper class.  This looks like a lot of code, but what what I am having trouble with is very trivial as I explained.  I'm certain its a random memory error since it happens at different points.  WHat I'm really looking for is some general advise on debuging the call to mysql_query in the absence of any error messages

Thanks again..


Main Script:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>

<?php
require_once ('../wp-blog-header.php');

$supid="puck";
$catprefix='111';
$xmlurl='http://dropship.puckator.co.uk/xml_feed.php?email=DELETED&password=DELETED&products&fields=pid|name|pev|f|wl|lfscd|sd|ld|dcc|dcev|sl|d|piu|descriptors|categories&since=' . $_GET['since'];
$xml = simplexml_load_file($xmlurl);

$filename = 'update.sql';
$fp = fopen($filename, "w");
fputs($fp,"-- new products or updated products\n");

// Create/Update product entries

foreach ($xml->product as $product )
{
    $sup_prod_id = $supid . "_" . $product->pid;
$count = $wpdb->get_var("SELECT count(*) FROM wp_postmeta WHERE meta_key='sup_prod_id' AND meta_value='$sup_prod_id'");

if($count == 0)
{
    $post_name = strtolower($product->name);
$post_name = preg_replace("/[^0-9a-z ]/i",'', $post_name);
$post_name = preg_replace("/ /", "-", $post_name);
$post_name = preg_replace("/--/", "-", $post_name);

    fputs($fp,"INSERT INTO wp_posts ( post_author, post_title, post_content, post_excerpt, post_name, post_date, post_modified ) VALUES ('1','$product->name','$product->ld','$product->sd','$post_name','$product->lfscd','$product->lfscd');\n");

        $postid = mysql_insert_id();

        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'sup_prod_id', '$sup_prod_id');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'price_ex_vat', '$product->pev');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'for_sale', '$product->f');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'warning_level', '$product->wl');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'last_for_sale_change', '$product->lfscd');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'del_charge_code', '$product->dcc');\n");
    fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'del_charge_ex_vat', '$product->dcev');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'stock_level', '$product->sl');\n");
        fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'dimensions', '$product->d');\n");
   
    $n=1;
    foreach ( $product->piu as $piu)
    {
            fputs($fp,"INSERT INTO wp_postmeta ( post_id, meta_key, meta_value) VALUES ('$postid', 'prod_img_$n', '$piu');\n");
        $n++;
}
     
    foreach ( $product->categories->cat_id as $cat_id )
{
    fputs($fp,"INSERT INTO wp_post2cat ( post_id, category_id ) VALUES ('$postid', '$catprefix$cat_id');\n");
    }
}
    else
{
    $postid = $wpdb->get_var("SELECT post_id FROM wp_postmeta WHERE meta_key='sup_prod_id' AND meta_value='$sup_prod_id'");
$datestr = date("Y-m-d H:i:s");
fputs($fp,"UPDATE wp_posts SET post_modified='$datestr' WHERE ID='$postid';\n");
    fputs($fp,"UPDATE wp_postmeta SET meta_value='$product->wl' WHERE post_id='$postid' AND meta_key='warning_level';\n");
    fputs($fp,"UPDATE wp_postmeta SET meta_value='$product->f' WHERE post_id='$postid' AND meta_key='for_sale';\n");
    fputs($fp,"UPDATE wp_postmeta SET meta_value='$product->s1' WHERE post_id='$postid' AND meta_key='stock_level';\n");    
}


}

fclose($fp);

?>
<p>Created or updated product entries</p>
</body>
</html>



****  WRAPPER CLASS 

class wpdb {

var $show_errors = true;
var $num_queries = 0;
var $last_query;
var $col_info;
var $queries;

// Our tables
var $posts;
var $users;
var $categories;
var $post2cat;
var $comments;
var $links;
var $linkcategories;
var $options;
var $optiontypes;
var $optionvalues;
var $optiongroups;
var $optiongroup_options;
var $postmeta;

// ==================================================================
// DB Constructor - connects to the server and selects a database

function wpdb($dbuser, $dbpassword, $dbname, $dbhost) {
$this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword);
if (!$this->dbh) {
$this->bail("
<h1>Error establishing a database connection</h1>
<p>This either means that the username and password information in your <code>wp-config.php</code> file is incorrect or we can't contact the database server at <code>$dbhost</code>. This could mean your host's database server is down.</p>
<ul>
<li>Are you sure you have the correct username and password?</li>
<li>Are you sure that you have typed the correct hostname?</li>
<li>Are you sure that the database server is running?</li>
</ul>
<p>If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the <a href='http://wordpress.org/support/'>WordPress Support Forums</a>.</p>
");
}

$this->select($dbname);
}

// ==================================================================
// Select a DB (if another one needs to be selected)

function select($db) {
if (!@mysql_select_db($db, $this->dbh)) {
$this->bail("
<h1>Can&#8217;t select database</h1>
<p>We were able to connect to the database server (which means your username and password is okay) but not able to select the <code>$db</code> database.</p>
<ul>
<li>Are you sure it exists?</li>
<li>On some systems the name of your database is prefixed with your username, so it would be like username_wordpress. Could that be the problem?</li>
</ul>
<p>If you don't know how to setup a database you should <strong>contact your host</strong>. If all else fails you may find help at the <a href='http://wordpress.org/support/'>WordPress Support Forums</a>.</p>");
}
}

// ====================================================================
// Format a string correctly for safe insert under all PHP conditions

function escape($string) {
return addslashes( $string ); // Disable rest for now, causing problems
if( !$this->dbh || version_compare( phpversion(), '4.3.0' ) == '-1' )
return mysql_escape_string( $string );
else
return mysql_real_escape_string( $string, $this->dbh );
}

// ==================================================================
// Print SQL/DB error.

function print_error($str = '') {
global $EZSQL_ERROR;
if (!$str) $str = mysql_error();
$EZSQL_ERROR[] =
array ('query' => $this->last_query, 'error_str' => $str);

$str = htmlspecialchars($str, ENT_QUOTES);
$query = htmlspecialchars($this->last_query, ENT_QUOTES);
// Is error output turned on or not..
if ( $this->show_errors ) {
// If there is an error then take note of it
print "<div id='error'>
<p class='wpdberror'><strong>WordPress database error:</strong> [$str]<br />
<code>$query</code></p>
</div>";
} else {
return false;
}
}

// ==================================================================
// Turn error handling on or off..

function show_errors() {
$this->show_errors = true;
}

function hide_errors() {
$this->show_errors = false;
}

// ==================================================================
// Kill cached query results

function flush() {
$this->last_result = null;
$this->col_info = null;
$this->last_query = null;
}

// ==================================================================
// Basic Query - see docs for more detail

function query($query) {
    echo("<p> before flush $query</p>");
// initialise return
$return_val = 0;
$this->flush();


// Log how the function was called
$this->func_call = "\$db->query(\"$query\")";

// Keep track of the last query for debug..
$this->last_query = $query;



// Perform the query via std mysql_query function..
if (SAVEQUERIES)
$this->timer_start();
echo("<p> after flush $query</p>");
$this->result = @mysql_query($query, $this->dbh);

++$this->num_queries;

if (SAVEQUERIES)
$this->queries[] = array( $query, $this->timer_stop() );

// If there is an error then take note of it..
if ( mysql_error() ) {
$this->print_error();

return false;
}

if ( preg_match("/^\\s*(insert|delete|update|replace) /i",$query) ) {
$this->rows_affected = mysql_affected_rows();
// Take note of the insert_id
if ( preg_match("/^\\s*(insert|replace) /i",$query) ) {
$this->insert_id = mysql_insert_id($this->dbh);
}
// Return number of rows affected
$return_val = $this->rows_affected;
} else {
$i = 0;
while ($i < @mysql_num_fields($this->result)) {
$this->col_info[$i] = @mysql_fetch_field($this->result);
$i++;
}
$num_rows = 0;
while ( $row = @mysql_fetch_object($this->result) ) {
$this->last_result[$num_rows] = $row;
$num_rows++;
}

@mysql_free_result($this->result);

// Log number of rows the query returned
$this->num_rows = $num_rows;

// Return number of rows selected
$return_val = $this->num_rows;
}

       
return $return_val;
}

// ==================================================================
// Get one variable from the DB - see docs for more detail

function get_var($query=null, $x = 0, $y = 0) {
$this->func_call = "\$db->get_var(\"$query\",$x,$y)";
if ( $query )
$this->query($query);

// Extract var out of cached results based x,y vals
if ( $this->last_result[$y] ) {
$values = array_values(get_object_vars($this->last_result[$y]));
}

// If there is a value return it else return null
return (isset($values[$x]) && $values[$x]!=='') ? $values[$x] : null;
}

// ==================================================================
// Get one row from the DB - see docs for more detail

function get_row($query = null, $output = OBJECT, $y = 0) {
$this->func_call = "\$db->get_row(\"$query\",$output,$y)";
if ( $query )
$this->query($query);

if ( $output == OBJECT ) {
return $this->last_result[$y] ? $this->last_result[$y] : null;
} elseif ( $output == ARRAY_A ) {
return $this->last_result[$y] ? get_object_vars($this->last_result[$y]) : null;
} elseif ( $output == ARRAY_N ) {
return $this->last_result[$y] ? array_values(get_object_vars($this->last_result[$y])) : null;
} else {
$this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
}
}

// ==================================================================
// Function to get 1 column from the cached result set based in X index
// se docs for usage and info

function get_col($query = null , $x = 0) {
if ( $query )
$this->query($query);

// Extract the column values
for ( $i=0; $i < count($this->last_result); $i++ ) {
$new_array[$i] = $this->get_var(null, $x, $i);
}
return $new_array;
}

// ==================================================================
// Return the the query as a result set - see docs for more details

function get_results($query = null, $output = OBJECT) {
$this->func_call = "\$db->get_results(\"$query\", $output)";

if ( $query )
$this->query($query);

// Send back array of objects. Each row is an object
if ( $output == OBJECT ) {
return $this->last_result;
} elseif ( $output == ARRAY_A || $output == ARRAY_N ) {
if ( $this->last_result ) {
$i = 0;
foreach( $this->last_result as $row ) {
$new_array[$i] = (array) $row;
if ( $output == ARRAY_N ) {
$new_array[$i] = array_values($new_array[$i]);
}
$i++;
}
return $new_array;
} else {
return null;
}
}
}


// ==================================================================
// Function to get column meta data info pertaining to the last query
// see docs for more info and usage

function get_col_info($info_type = 'name', $col_offset = -1) {
if ( $this->col_info ) {
if ( $col_offset == -1 ) {
$i = 0;
foreach($this->col_info as $col ) {
$new_array[$i] = $col->{$info_type};
$i++;
}
return $new_array;
} else {
return $this->col_info[$col_offset]->{$info_type};
}
}
}

function timer_start() {
$mtime = microtime();
$mtime = explode(' ', $mtime);
$this->time_start = $mtime[1] + $mtime[0];
return true;
}

function timer_stop($precision = 3) {
$mtime = microtime();
$mtime = explode(' ', $mtime);
$time_end = $mtime[1] + $mtime[0];
$time_total = $time_end - $this->time_start;
return $time_total;
}

function bail($message) { // Just wraps errors in a nice header and footer
if ( !$this->show_errors )
return false;
header( 'Content-Type: text/html; charset=utf-8');
echo <<<HEAD
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>WordPress &rsaquo; Error</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style media="screen" type="text/css">
<!--
html {
background: #eee;
}
body {
background: #fff;
color: #000;
font-family: Georgia, "Times New Roman", Times, serif;
margin-left: 25%;
margin-right: 25%;
padding: .2em 2em;
}

h1 {
color: #006;
font-size: 18px;
font-weight: lighter;
}

h2 {
font-size: 16px;
}

p, li, dt {
line-height: 140%;
padding-bottom: 2px;
}

ul, ol {
padding: 5px 5px 5px 20px;
}
#logo {
margin-bottom: 2em;
}
-->
</style>
</head>
<body>
<h1 id="logo"><img alt="WordPress" src="http://static.wordpress.org/logo.png" /></h1>
HEAD;
echo $message;
echo "</body></html>";
die();
}
}

$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
?>

Share this post


Link to post
Share on other sites
Some suggestions.  the first one seems most likely, particularly since your connecting to everything all at once: xml, mysql, mail ....

You could be execing your PHP script time.  If you have access to your ini file, you could give your scripts a longer execution time.

But there may be easier ways to get all of this done.  Maybe breaking the functionality into seperate pages, which redirect to each other...

Just some suggestions, HTH

Jeff

Share this post


Link to post
Share on other sites
Do you mean that its hitting a time limit and then chopping the script?  How do I alter this in the .ini file?

Why would there be a time limit?  It does take about 3 minutes before it crashes out but surely I should be able to process as much as I want? 

Share this post


Link to post
Share on other sites
My good your lucky the computer did not blow up.

you need to convert the code over diffrent pages as all your doing is open mysql close mysql all the time.

Share this post


Link to post
Share on other sites
I thought I just had a load of select queries in a loop.  I'm not opening and closing the database connection for each one.  Please can you explain what you mean?

Share this post


Link to post
Share on other sites
I can't help with specifics, but I can offer advice based on php/pgsql experiene.  If your script is hanging during a database call, then you can leave it running and inspect the database itself.  Get a list of running queries, and see if they are waiting on any locks, etc etc.  IS it possible there might be other scripts running that could case a deadlock?  I can't help you with specific commands to do that under Mysql though.

I assume that your "fputs($fp, ...)" commands would normally be database queries?

Share this post


Link to post
Share on other sites
There are some things you can do to speed up execution

Use value lists in INSERT queries, so instead of

[code]INSERT INTO tableA (a, b, c) VALUES (a1, b1, c1)
INSERT INTO tableA (a, b, c) VALUES (a2, b2, c2)
INSERT INTO tableA (a, b, c) VALUES (a3, b3, c3)[/code]

use

[code]INSERT INTO tableA  (a, b, c) VALUES
(a1, b1, c1),
(a2, b2, c2),
(a3, b3, c3)[/code]

Share this post


Link to post
Share on other sites
[quote author=btherl link=topic=107473.msg431497#msg431497 date=1157875695]
I can't help with specifics, but I can offer advice based on php/pgsql experiene.  If your script is hanging during a database call, then you can leave it running and inspect the database itself.  Get a list of running queries, and see if they are waiting on any locks, etc etc.  IS it possible there might be other scripts running that could case a deadlock?  I can't help you with specific commands to do that under Mysql though.

I assume that your "fputs($fp, ...)" commands would normally be database queries?
[/quote]

No, the fputs commands are building an import file.  They aren't normally queries.  All I have in essence is:

foreach thing (of which there are maybe 1000)
  MYSQL SELECT to see if thing already exists
  if thing already exists
      print a few strings to a file
  if thing doesn't already exist
      MSQL SELECT to get another single value
      print some other stuff to a file

I really don't know how I could split this functionality as the logic is extremely trivial.

There are no scripts running at the same time.  I don't know how to examine the database for running queries, have you any advise? 

Thanks for help so far!

Share this post


Link to post
Share on other sites
[quote author=DavidSWP link=topic=107473.msg431231#msg431231 date=1157820865]
Individually the queries work fine, even the ones it crashes on, but in my script which executes maybe a thousand one after another it gets to a point where it just completely hangs.  Its hanging at different SELECT queries each time I run it which makes me think its a memory error.
[/quote]

What do you mean by hang? jefkin's thought is one I'd consider.

Put this just before "require_once ('../wp-blog-header.php');". You may not be seeing all errors
[code]
error_reporting(E_ALL);
ini_set('display_errors', 'on');
[/code]
Run the script again and perhaps the "max_execution_time" error will be shown.

This kind of script would probably be better run from the [url=http://www.php.net/manual/en/features.commandline.php]command line[/url] where there are no execution time restrictions.

[quote=DavidSWP]
I don't know how to examine the database for running queries, have you any advise?
[/quote]

[code]
SHOW PROCESSLIST
[/code]

EDIT: You may be able to change the max_execution_time setting using [url=http://www.php.net/ini_set]ini_set()[/url] depending on the "safe_mode" setting. If you have full access to the server then you can of course change whatever you like in the php.ini.

Share this post


Link to post
Share on other sites
I've altered the 2 execution time values in the script to 999 and increased the max operating memory limit in php.ini but I still have the problem.

I will try your suggestions tommorow regarding displaying errors and processes.  Need some sleep now!
Yes it would make sense to run this script from the command line.  I didn't realise it operated differently.

Thanks again.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.