mysql 5.5 tuning for 145MB osc2.2rc2

[motorcity]

We made the leap to php 5.3 and mysql 5.5 with our old osc 2.2rc2 only because we want to move on towards a bootstrap website and I thought it made sense to take it one step at a time. Almost everything works except some inventory reorder forms that we've become dependent upon over the years.

I thought it was lousy code work at first but later I discovered it's database tuning that is out of whack.

Here's what is wrong;

A html form that could sort by many columns, automatically set up a proposed order based on stock level, reorder quantity, and a set point, and give the user a checkbox to approve or not each item then POST the update to the products.table stopped working.

I found out (no errors being reported) that I could make it work by reducing the number of products queried.

I found out it's not php. It's mysql running out of resources.

Though the years I played around with tuning the old mysql 5.0.96 database and now I need to get the 5.5 version up to speed. It's all MyISAM in the osc tradition but mysql5.5 is trending innodb and it's default configuration seems to bear that out.

Tools I'm using;

tuning-primer.sh

mysqltuner.pl

queries_debugv1.5

kiss_error_handler

WebHostManager 11.48.4

Old notes on changes to 5.0.96 that I hope can help.

Questions:

1. Does anyone know what the hell I'm doing because I sure as .... well it's complicated.

2. If I do get this thing tuned up good enough for the MyISAM engine aren't I just going to have to switch everything over to innodb at some point anyway? Is this a fools errand?

3. I've already been cheating on the 48 hours run time to diagnose what changes (to my.cnf) I make next, any thoughts on how to get this done quicker?

4. Any moral support you can offer would be nice because everyone I talk to about all this seem to roll their eyes back and yawn.

[clustersolutions]

Funny...

 

"1. Does anyone know what the hell I'm doing because I sure as .... well it's complicated."

 

Until you had pinpoint the error message/log don' assume. You on a $5/mo hosting plan? That could be your answer.

 

Run top/iostat/netstat and check your stat...check your mysql log too...regarding my.cnf...change things one at a time to bound your problem. I seriously doubt it's mysql...you check you explain plan?

[motorcity]

It's been a long time since the $5 a month plan for me anyways. Running centos6 now, that's the other thing that got updated.

[clustersolutions]

I was just trying to be funny...

 

"I found out it's not php. It's mysql running out of resources."

 

Which resource? Well...is that a 145mb of db or products table? But that's hardly a lot...If you are doing bulk update to the products table you may be better of with innodb so you have better control on locks (I seriously doubt it's the issue here)...you need to catch your slow queries and run explain plan on them...mysql sometimes may be very slow but it always finish queries...

 

I hope you are doing crawl, walk, then run so this is not your production server...

[♥bruyndoncx]

probably just some indexes missing, if you can share the php file that has the sql statement and the database products table creation info from phpmyadmin we can probably pinpoint the issue

[Hotclutch]

Start by loading a test bootstrap store in a subfolder of your live store, then build all the functionality over onto it. Last step will be to migrate all your data. Will be less work than trying to upgrade an old store.

[MrPhil]

I agree with @@Hotclutch that you would be better off by making the leap to 2.3.4 (with or without bootstrap) and just migrating a copy of your data. You'll spend an incredible amount of time and effort trying to migrate ancient code like 2.2, if that's what you're trying to do. Sooner or later, even if you get things running (apparently) smoothly on PHP 5.3 and the new MySQL, you'll be face to face with migrating your tools and add-ons to 2.3.4. I think you'd be better off up front by jumping to the latest and greatest PHP (5.4 or 5.5 if you can get it) and osC 2.3.4, and spend your time migrating over necessary add-ons and such.

 

145MB of database should not be overwhelming to MySQL. I think around 300-400MB should be where it starts to really bog down, on a well-run server with adequate resources. That number I think was for MYISAM, and may be higher for INNODB. If you're trying to get by on the cheap, you may find it worth the additional money to go to a better server plan (cloud, VPS, even dedicated) to get better speed and reliability, especially if this store is a real livelihood and not just a hobby.

[motorcity]

To be clear(er) we have pretty good hosting. A vps, good support, not cheap, not a problem. Size of mysql vps wide is over 300MB but the live db is less than 150. And yes this is our live website. The problems are confined to scripts ran in the admin but that admin would be basically the same whether we were bootstrapped 2.3.4 or still 2.2rc2 which we are.

Maybe I should point out that the script I'm having problems with POSTS an update just fine if limited to 462 products via

and p.products_id < 88566

the same script with no other changes doesn't POST anything if I increase the number of products to 463 via

and p.products_id < 88567

So somewhere the logic and thought that mysql will complete the job no matter what, just isn't happening in this case. The same exact script used to work with no problems with nearly 4000 products. It was used regularly under osc2.2RC2 php5.2.9 and mysql5.0.96 without errors or issues.

[motorcity]

<?php
require('includes/application_top.php');
define('MAX_RESULTS', '1000');
//set_time_limit(300);
ini_set('memory_limit', '-1');
//ini_set('post_max_size', '10M');
//ini_set('max_input_time', 300);
//ini_set('max_execution_time', 300);
 function get_products_reorder_to($products_quantity, $reorder_quantity, $shelf_pack,$products_reorder_to){
 $new_reorder_to = 0;
 if($products_quantity < $reorder_quantity && $shelf_pack > 0){
 	for($new_reorder_to = $shelf_pack;($new_reorder_to + $products_quantity) < $reorder_quantity;){
	$new_reorder_to += $shelf_pack;
	}
 }
 if($products_reorder_to > 0)
 $new_reorder_to = $products_reorder_to;
 return $new_reorder_to;
// var_dump($new_reorder_to);
 } //end function

if($_POST['reset']){
$reset_query = tep_db_query("select products_id, products_quantity, shelf_pack, reorder_quantity from " . TABLE_PRODUCTS . " where vendors2_id = 16 and products_quantity < reorder_quantity");
  while ($reset_select = tep_db_fetch_array($reset_query)) {
    $jd = get_products_reorder_to($reset_select['products_quantity'], $reset_select['reorder_quantity'], $reset_select['shelf_pack'], 0);
	$query = tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET products_reorder_to = '" . $jd . "' WHERE  vendors2_id = 2 and products_id = '" . $reset_select['products_id'] . "'");
	}

$query = tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET approved = '1' WHERE  vendors2_id = '2' and products_quantity < reorder_quantity ");
$query = tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET approved = '0', products_reorder_to = '0' WHERE  vendors2_id = '2' and products_quantity >= reorder_quantity ");
}//post reset

if($_POST['save'] || $_POST['autosave'] == '1'){
if($_POST['add2order'] != '')
$idchecked = join(",", $_POST['add2order']);
if($_POST['add2orderlist'] != '')
$idlist = join(",", $_POST['add2orderlist']);
$query = tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET approved = '1' WHERE vendors2_id = '2' and products_id  in (" . $idchecked . ")");
$query = tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET approved = '0' WHERE vendors2_id = '2' and products_id in (" . $idlist . ") and products_id NOT in (" . $idchecked . ")");
//var_dump($_POST['save']);
}//post save

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>

</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php');  ?>
<!-- header_eof //-->

<!-- body //-->
<form name="order_quantity" method="post">
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
    <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php //require(DIR_WS_INCLUDES . 'column_left.php'); 
$sorted = $HTTP_GET_VARS['sorted'];
$orderby = $HTTP_GET_VARS['orderby'];
if ($sorted !== "ASC" and $sorted !== "DESC") $sorted = "ASC"; 
?>

<!-- left_navigation_eof //-->
        </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
<?php

   $products_query_raw = "select p.products_id, p.products_model, p.products_quantity, pd.products_name, p.products_sku, p.reorder_quantity, p.products_reorder_to, p.shelf_pack, p.vendors_product_price, pr.manufacturers_name, p2c.categories_id from " . TABLE_PRODUCTS . " p inner join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left outer join " . TABLE_MANUFACTURERS . " pr on p.manufacturers_id = pr.manufacturers_id  where p.vendors2_id = 2 and p.products_id < 88567 and  p.approved=1 group by p.products_id   ";
 $products_query = tep_db_query($products_query_raw);
  while ($products = tep_db_fetch_array($products_query)) {
  $new_reorder_to = get_products_reorder_to($products['products_quantity'], $products['reorder_quantity'], $products['shelf_pack'], $products['products_reorder_to']);
			$subtotal = $new_reorder_to * $products['vendors_product_price'];
			$total += $subtotal;
//			var_dump($products);
  }

?>

                <td class="pageHeading" align="left">A Products Company</td>

			<td class="main" align="left">Grand Total = $<?php echo $total;?></td>
            <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>        
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td>
				<td class="dataTableHeadingContent"><?php  if (!isset($orderby) or ($orderby == "sku" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=sku&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_PROD_ID . '</a>'; ?> </td>
                <td class="dataTableHeadingContent"><?php  if (!isset($orderby) or ($orderby == "model" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=model&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' . TABLE_HEADING_MODEL . '</a>';  ?></td>
                <td class="dataTableHeadingContent"><?php  if (!isset($orderby) or ($orderby == "name" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=name&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' . TABLE_HEADING_PRODUCTS . '</a>';  ?></td>
                <td class="dataTableHeadingContent" align="right"><?php  if (!isset($orderby) or ($orderby == "cost" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=cost&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_PRICE_LEFT . '</a>'; ?> </td>
                <td class="dataTableHeadingContent" align="right"><?php  if (!isset($orderby) or ($orderby == "shelf" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=shelf&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_SHELF . '</a>'; ?> </td>
                <td class="dataTableHeadingContent" align="right" bgcolor="FFCCCC"><?php  if (!isset($orderby) or ($orderby == "stock" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=stock&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_QTY_LEFT . '</a>'; ?> </td>
                <td class="dataTableHeadingContent" align="right" bgcolor="CCFF99"><?php  if (!isset($orderby) or ($orderby == "reorder" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=reorder&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_REORDER . '</a>'; ?> </td>
                <td class="dataTableHeadingContent" align="right"><?php  if (!isset($orderby) or ($orderby == "reorder2" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=reorder2&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">' .TABLE_HEADING_REORDER2 . '</a>'; ?> </td>
                <td class="dataTableHeadingContent" align="right"><?php if (!isset($orderby) or ($orderby == "total" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=total&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">'. TABLE_HEADING_LINE_T. '</a>'; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php if (!isset($orderby) or ($orderby == "approved" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link(FILENAME_STATS_INVENTORY4, 'orderby=approved&sorted='. $to_sort.($_GET['page']>0?'&page='.$_GET['page']:'')) . '" class="headerLink">'.TABLE_HEADING_CHECK_T. '</a>'; ?></td>
              </tr>
<?php
			
 if ($HTTP_GET_VARS['page'] > 1) $rows = $HTTP_GET_VARS['page'] * 20 - 20;
 if ($orderby == "name") {$db_orderby = "pd.products_name";}
 elseif ($orderby == "stock") {$db_orderby = "p.products_quantity";}
 elseif ($orderby == "reorder") {$db_orderby = "p.reorder_quantity";}
 elseif ($orderby == "reorder2") {$db_orderby = "p.products_reorder_to";}
 elseif ($orderby == "shelf") {$db_orderby = "p.shelf_pack";}
 elseif ($orderby == "cost") {$db_orderby = "p.vendors_product_price";}
 elseif ($orderby == "sku") {$db_orderby = "p.products_sku";}
 elseif ($orderby == "model") {$db_orderby = "p.products_model";}
  elseif ($orderby == "total") {$db_orderby = "total";}
   elseif ($orderby == "approved") {$db_orderby = "p.approved";$secsort=",total desc";}
 else {$db_orderby =  "p.approved";$secsort=",total desc";
 
  $sorted='DESC';}
 

  $products_query_raw = "select p.products_id, p.products_model, p.products_quantity, pd.products_name, p.products_sku, p.reorder_quantity, p.products_reorder_to, p.shelf_pack, p.vendors_product_price, pr.manufacturers_name,(p.vendors_product_price *  p.products_reorder_to) as total, p.approved, p2c.categories_id from " . TABLE_PRODUCTS . " p inner join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left outer join " . TABLE_MANUFACTURERS . " pr on p.manufacturers_id = pr.manufacturers_id  where p.vendors2_id = 2 and p.products_id < 88567 group by p.products_id   order by $db_orderby $sorted $secsort";
//test site db < 88473 breaks and 88415 works
  //following is for live
//pid > larger than 76899 works. we have 460 pids larger than 76893 breaks it we have 461 pids
//pid < less than 88566 works, thats 462 pids. 88567 breaks = 463 pids
  $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_RESULTS, $products_query_raw, $products_query_numrows);
  $products_query = tep_db_query($products_query_raw);
  while ($products = tep_db_fetch_array($products_query)) {
    $rows++;
//var_dump($products_query_raw);
    if (strlen($rows) < 2) {
      $rows = '0' . $rows;
    }
				$products_id = $products['products_id'];

			
			$new_reorder_to = get_products_reorder_to($products['products_quantity'], $products['reorder_quantity'], $products['shelf_pack'], $products['products_reorder_to']);
			$subtotal = $new_reorder_to * $products['vendors_product_price'];
			
				$products_approved = tep_db_query("select approved from " . TABLE_PRODUCTS . " where products_id = $products_id");
				$product_approved = tep_db_fetch_array($products_approved);
				$p_approved = $product_approved["approved"];
			if($p_approved==1)
			$pagetotal += $subtotal;
			$approvedbutton = tep_draw_checkbox_field('add2order[]',$products_id,(($p_approved==1)? 'true':'')) . ' ' . (tep_not_null(ENTRY_NEWSLETTER_TEXT) ? '<span class="inputRequirement">' . 'Approved'. '</span>': '');
			echo '<input type="hidden" name="add2orderlist[]" value="'.$products_id.'">';
?>

<tr class="dataTableRow"onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)">
            <td align="left" class="dataTableContent"><?php echo $rows; ?>.</td>
            <td align="right" class="dataTableContent"><?php echo $products['products_sku']; ?></td>
            <td align="left" class="dataTableContent"><?php echo $products['products_model']; ?></td>
            <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $products['categories_id'] . '&pID='.$products['products_id'].'&action=new_product') . '" TARGET="_blank"><b>' . $products['products_name'] . '</a></b>'; ?></td>
            <td align="right" class="dataTableContent"><?php echo $products['vendors_product_price']; ?></td>
            <td align="right" class="dataTableContent"><?php echo $products['shelf_pack']; ?></td>
            <td align="right" bgcolor="FFCCCC" class="dataTableContent"><?php echo $products['products_quantity']; ?></td>
            <td align="right" bgcolor="CCFF99" class="dataTableContent"><?php echo $products['reorder_quantity']; ?></td>
            <td align="right" width="50" class="dataTableContent"><?php echo tep_draw_input_field('reorder2['.$products_id.']', $new_reorder_to, 'style="width: 100%"'); ?></td>            
            <td align="right" class="dataTableContent"><?php echo  sprintf("%01.2f",$subtotal); ?></td>
            <td align="right" class="dataTableContent"><?php echo $approvedbutton; ?></td>
            </tr>
            
<?php
 //} 
 unset($cPath_array); unset($p_category_array); 
  }
?>
    	<tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent" colspan="4" align="center"><input type="submit" name="reset" value="Send/Reset Order" onClick="return confirm('Send/Reset all orders?');"></td>
				   <td class="dataTableHeadingContent" colspan="5" align="center"><input type="submit" name="save" value="Save Order" onClick="return confirm('Save orders?');"></td>
				  <td class="dataTableHeadingContent" colspan="2"><?php echo 'Sub Total : $'.$pagetotal; ?></td>
				</tr>
          </table></td>
          </tr>
<?php 
if($_POST['reset']){
echo '<input type="hidden" name="autosave" value="1">';
echo '<script language="javascript">
order_quantity.submit();  
</script>';
}
?>

		  </form>
          <tr>
            <td colspan="3"><table border="0" width="90%" cellspacing="0" cellpadding="2">
              <tr>
                <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, MAX_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
                <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, MAX_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page'], "orderby=" . $orderby . "&sorted=" . $sorted); ?> </td>
              </tr>
            </table></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
</body>
</html>

That's the complete script. The vendors2_id is hard coded for a bunch of different outfits we buy from, some are keyed by manufacturer_id as well.

[Jack_mcs]

Joe - Is that a file that is standard in oscommerce? Not the contents of it but the file name, like categories.php? If it is, then I suggest uploading a stock one and see if that works. That will eliminate a problem with the database. It probably isn't but in a case like this, sometimes the best approach is to remove as much extra stuff as possible until it works and then add them back in one at a time. If it isn't a standard file, I would try the database command that is failing directly in phpmyadmin. If it fails, remove something from it, like the vendor part.

[♥bruyndoncx]

in mysql try

 

check table products

optimize table products

 

these will update the index statistics and reorganize the physical layout of the table. This might just magically solve the issue.

 

Seocndly do you really need the group by products_id in the sql (do you have products in multiple categories ?)

 

Thirdly, I think you are missing some indexes on those new fields

 

in mysql use the explain function

https://dev.mysql.com/doc/refman/5.0/en/using-explain.html

 

(I'm not a fulltime developer, but I did tune some big databases in my professional career - granted it is almost 20 years ago that I acted as DBA, still basic principles are the same)

[MrPhil]

The first thing I'd check is whether there's something odd about that product where products_id=88567. Invalid or corrupted data (especially any text that's invalid UTF-8)? You might even try p.products_id<>88567 and p.products_id<88600 to see if it works with other products. You might consider putting in something to check if there's an error reported on the query, and print it out, to get specifics on what happened.

 

If it does appear that it's not a particular product's data, but the size of the query (i.e., using too much memory), you're going to need to look into how to reduce the size of the query. You've got quite a few table joins there -- maybe you could get rid of some or simplify it in some way. If the activities involving one product are independent of all the other products, maybe you could use a LIMIT clause in a loop to process, say, 250 records at a time (in a loop). It's not elegant, but sometimes you gotta do what you gotta do to get the job done.

[motorcity]

Thanks to all.

@@Jack I was able to enter parts of the queries directly into the db with success. Began hoping perhaps a manual reset of that kind might get it rolling again, but no. It still does nothing when asked to do the UPDATE through POST.

Here's a small sample of queries debug output;

POST:

Array
(
    [add2orderlist] => Array
        (
            [0] => 77091
            [1] => 76909
           [461] => 4238
        )

    [reorder2] => Array
        (
            [77091] => 15
            [76909] => 25
            [71069] => 12
           [4238] => 
        )

    [add2order] => Array
        (
            [0] => 71069
            [1] => 76891

@@bruyndoncx

I'm off to find some samples I remember seeing on explain.

@@MrPhil

Kinda ran the gamit (or damnit?) trying to find corrupt data and coming up empty. While we're at it, you mentioned  utf-8 and I believe the deal is when switching to osc2.3.4 bootstrap that is required, most everything about this database is still osc2.2RC2 and still latin1_swedish_ci. Only information_schema, and performance_schema are utf-8 at this point.

[clustersolutions]

You ran that query in mysql and it ran fine so the issue was not mysql. U should post your explain plan for that query? Also, may be your issue was not with that particular query. I don't really see that query being troublesome. The worst case it'd probably run some full table scan and use a lot of tmp spaces...

 

You still need to pin point the error from either the Mysql or PHP erroy log...focus on the PHP log and make sure that PHP error reporting is enabled. I had something similar happened to me recently with USU5 and turns out it was PHP memory limit...VPS, do you know how many sites are sharing your server? I found VPS with a bit of limitation as well...another thought was you should have a dedicated dev box and see if the issue still there...

[motorcity]

I spent a fair amount of time and came up with an index that worked to reduce the query time by more than half and it made no difference at all. Same deal add one too many products into the mix and it doesn't update. Take one away and it works.

Also my db tuning is ongoing and mysql is running much better than it was earlier. All of which leads me back to php must be the fault.

How on earth!

[MrPhil]

You must be getting an error message somewhere that tells you that PHP is either running out of memory or is over a time limit. Did you enable all error messages? Do you have error_log files or something similar being dropped somewhere on your site? It shouldn't just fall over dead and not say why. Are you able to increase the memory allocation and/or run time in php.ini or similar? Maybe bump up one of them and see if it gets a little further than before.

 

Since MySQL itself seems to be able to handle the show, my suspicion would be that MySQL is tying up PHP long enough to trip a time limit. How many results does MySQL return to PHP? Try adding LIMIT 1 to the query (and drop the products_id limit) and see if it runs through. If it does, you might be able to loop through and do chunks of 10 at a time or something. Even better would be to study the query carefully and see if you're doing unnecessary JOINs or can otherwise optimize the query.

[motorcity]

@@MrPhil When I first started all this I was running FWR's kiss_error_handler. I was getting some notices about undefined indexes and variables, but no warnings. I switched up later, a week ago yesterday, on 5/15/15 to Chemo's queries debug v1.5 (so I could debug the POST variables) but I haven't tried switching back to kiss.

I asked the server support today where to find php error logs and was told "php does not have its own specific error log but logs to site error logs and apache error logs". I did take a look at various apache logs today but found  nothing I thought could remotely be involved in this issue; chinese ip's attempting lord knows what being denied.

I probably haven't made all that clear that before we switched to php5.3 I had 16 different versions of this script where some key on manufacturer and  some on vendors2_id. There are some in both camps that still work like they used to and some from both that don't work after the change to php5.3. They're evenly divided; 8 work, 8 don't. All those that still work are smaller in terms of number of products and of course number of queries.

All the bigger ones fail so it's some sort of resource issue that doesn't get reported, or at least I'm not finding how it's getting reported.

I got my start programing eprom chips back when we called it 'machine language'. To me, it's always been the same; it's a machine, it can't do it right and then deviate from what it's programed to do, unless you are misinterpreting the facts it's working with.

I know I'm wrong. I just can't figure it out.

~Joe

Onedit one thing that bugs me is this

if($_POST['add2orderlist'] != '')
$idlist = join(",", $_POST['add2orderlist']);

The ",", in the join is the only place in my website that happens.

[MrPhil]

Do you check somewhere that $_POST['add2orderlist'] is set? It's common to write code assuming a variable or element exists, but it should be checked unless you've proven by that point the element exists. You should in that case see an error message, but if you've suppressed some warnings, you might not see it. Also note that osC normally uses $HTTP_POST_VARS, which should be a copy of $_POST, but if anything was added to $HTTP_POST_VARS and not $_POST that might be a problem.

 

Are you satisfied that the data exists (is set) and is valid (i.e., no invalid UTF-8 characters), as opposed to having something to do with the volume of data (memory size or run time)? Then there's the matter that MySQL can handle it, but PHP seems to choke on it, which could be either invalid data returned, or the size/time.

[greasemonkey]

@@motorcity It would seem you have a solid (much more so than I) understanding of MySQL and PHP.... So please don't take offense when I say - the effort you've put into fixing this, if put into a new 2.3.4 BS build, could have been half the effort required to have that new build up and running.

 

Just an (un requested) opinion ☺️☺️☺️☺️ I'll shut up now.

[motorcity]

@@greasemonkey Hey Scott. At this point I certainly don't consider myself very accomplished at php or mysql, even if I brilliantly or (more likely) luckily solved my current issue here. I truly appreciate any and all questions and comments.

That being said, these scripts were tested as well as I could running php 5.2.9 to be php 5.3 compliant. I can't say I tested all 16 but I found no problems at the time and of course half of them still do work now in 5.3

Also of note; these scripts along with most of the osc admin are the same thing I'll be running when I switch to osc2.3.4 bootstrap.

@ MrPhil I switched this am to the most brutal error reporting I know of;

admin/application_top

  error_reporting(E_ALL & E_NOTICE);
  ini_set('display_errors', 'On');

Again it was all notices of undefined indexes and variables ~ all over the place. So I took some time to try to minimize  some of that with some success. Thinking "save" and "reset" are where the functional problems really are let's fix those, so I tried this;

if(isset($_POST['reset'])){ $reset = $_POST['reset']; } 
if($_POST['reset']){ //added the above isset line, no changes below including this comment for forum entry only

if(isset($_POST['save'])){ $save = $_POST['save']; } 
if(isset($_POST['autosave'])){ $autosave = $_POST['autosave']; } 
if($_POST['save'] || $_POST['autosave'] == '1'){ //added the above 2 isset lines, no changes below including this comment for forum entry only

And guess what happened? In my product_id limited functioning file hitting save results in no undefined notice about save or autosave, but it complains about reset being undefined. Hitting reset results in complaints about save and autosave but no problem with reset.

Seeing that I decided to move on. Maybe there's a better method or code, but it isn't solving the issue or causing it as best I can tell.

I then began hacking out parts of the most expensive mysql queries, finding much that really doesn't do anything including multiple joins, and then I ripped out the pagination parts, none of that makes a difference.

Next I wanted to eliminate some other variables like memcached and mysql query_cache storing stuff that's screwing me up. So I tried turning memcached off and ran files that work and files that don't ~ Results; makes no difference at all. Researching, my best guess is the mysql query_cache is cleared of data when mysql restarts so I tried that as well and it made no difference.

 

So finally I'm looking at winmerge of the new 5.3 php.ini compares to the old 5.2.9 php.ini

Differences of note;

allow_call_time_pass_reference = On (in old 5.2.9 only, is depreciated in 5.3)

old 5.2.9 has

max_execution_time = 90
max_input_time = 60
memory_limit = 32M

new 5.3 has

max_execution_time = 300
max_input_time = 600
max_input_vars = 1000 ~~~~~~~~~~~~~~~~~have not yet read about max_input_vars
memory_limit = -1

magic_quotes_gpc = On on old php, new 5.3 is turned off

old 5.2.9

extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20060613"~~~~~~~~~~~has been updated
extension = "timezonedb.so"~~~~~~~~~~~don't know, not in 5.3
extension = "memcache.so"~~~~~~~~~~~~not in use, not in 5.3
zend_extension = "/usr/local/IonCube/ioncube_loader_lin_5.2.so"~~~~~~~~~~~~not in use, not in 5.3

enable_dl = On on old 5.2.9, off in 5.3

Following group of extensions are loaded in 5.3 only

extension = "pdo.so"
extension = "pdo_sqlite.so"
extension = "pdo_mysql.so"
extension = "sqlite.so"
extension = "memcache.so"

date.timezone = "America/Detroit" in 5.3 only.

Still pluggin away here.

Happy Memorial day

@@MrPhil I should mention I tried using the suggested query limit instead of my p_id limits and never found the proper syntax to get it working, it was confused with MAX RESULTS which led me to eliminate the whole thing.

[motorcity]

changed

max_input_vars = 1000

to

max_input_vars = 5000

That was it!

At least... I Think that was it.

OnEdit yep that was it. The biggest narly-ist and most important ones now work.

Time for a little settle down time. Thanks to all.

[MrPhil]

The code you gave makes no sense at all.

 

1) You have to take care of all cases -- where the $_POST['save'] element is set and where it's not, and set your $save accordingly. That is,

 

if (isset($_POST['save'])) {

  $save = true; // or whatever value it should have

} else {

  $save = false; // something distinct from the other case

}

// from here on, use only $save

 

2) You use only the $save variable after that point -- it's guaranteed to have a legitimate value (be defined). You don't use $_POST['save'] again in this function.

[motorcity]

That's what I get for googling for a code snippet.

The whole idea was to clear the php notice but as you point out, it makes no sense. Which is why it behaves in exactly the fashion it does; flip-flopping between one php notice or the other.

I removed that code and will carry on towards the goal of launching osc2.3.4BS with existing php notices in tow, with functionality taking first place, query/resource costs second, and php notices somewhat lower on my priority list.

Thanks for pointing that out though. And thanks for all your help.

~motorcity

[Havock]

Greetings motorcity,

 

Seeing your post after you solved your case, so could not help before. :)

 

Your problem had nothing to do with queries optimisation, but was only due to the huge number of input fields of your form. Increasing  php_value max_input_vars is the right solution (had to do this myself a few years ago with a huge form on my admin).

[♥bruyndoncx]

it did make me wonder if you could not rewrite the query so you only needed to pass along the checked fields displayed on screen and not needing the 'list' construction, or even using ajax and updating the database value whenever you change something