DavidSWP Posted September 9, 2006 Share Posted September 9, 2006 Hi,I would really appreciate some help with a problem I have using mysql_query in php 5, Mysql ver. 5I'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.ThanksDWP >:( Quote Link to comment Share on other sites More sharing options...
tomfmason Posted September 9, 2006 Share Posted September 9, 2006 Please post what you have so far Quote Link to comment Share on other sites More sharing options...
DavidSWP Posted September 9, 2006 Author Share Posted September 9, 2006 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 messagesThanks 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><?phprequire_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 entriesforeach ($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’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 › 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);?> Quote Link to comment Share on other sites More sharing options...
jefkin Posted September 9, 2006 Share Posted September 9, 2006 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, HTHJeff Quote Link to comment Share on other sites More sharing options...
DavidSWP Posted September 9, 2006 Author Share Posted September 9, 2006 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? Quote Link to comment Share on other sites More sharing options...
redarrow Posted September 9, 2006 Share Posted September 9, 2006 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. Quote Link to comment Share on other sites More sharing options...
DavidSWP Posted September 9, 2006 Author Share Posted September 9, 2006 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? Quote Link to comment Share on other sites More sharing options...
btherl Posted September 10, 2006 Share Posted September 10, 2006 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 Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 There are some things you can do to speed up executionUse 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] Quote Link to comment Share on other sites More sharing options...
DavidSWP Posted September 10, 2006 Author Share Posted September 10, 2006 [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 fileI 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! Quote Link to comment Share on other sites More sharing options...
shoz Posted September 10, 2006 Share Posted September 10, 2006 [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. Quote Link to comment Share on other sites More sharing options...
DavidSWP Posted September 10, 2006 Author Share Posted September 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.